Difficulty
Last Reviewed
Notes
Reference Link
Reviewed
Status
Done
Tags
NOTE: introduce you to the derived table concept and show you how to use it to simplify complex queries.
Introduction to MySQL derived tables
A derived table is a virtual table returned from a SELECT statement. A derived table is similar to a temporary table, but using a derived table in the SELECT statement is much simpler than a temporary table because it does not require creating a temporary table.
The term derived table and subquery is often used interchangeably. When a stand-alone subquery is used in the FROMclause of a SELECT statement, it is also called a derived table.
The following illustrates a query that uses a derived table:
Unlike a subquery, a derived table must have an alias so that you can reference its name later in the query. If a derived table does not have an alias, MySQL will issue error. Every derived table must have its own alias.
Basic MySQL Derived Table example
The following query gets the top five products by sales revenue in 2003 from the orders and orderdetails tables in the sample database:
‣
Example 1: Simple Derived Table
‣
The use of CASE in Sql
‣