{"id":5964,"date":"2017-07-04T08:07:31","date_gmt":"2017-07-04T15:07:31","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5964"},"modified":"2024-01-04T07:04:10","modified_gmt":"2024-01-04T14:04:10","slug":"mysql-derived-table","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-derived-table\/","title":{"rendered":"MySQL Derived Tables"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL derived tables and how to use them to simplify complex queries.<\/p>\n\n\n\n

Introduction to MySQL derived tables<\/h2>\n\n\n\n

A derived table is a virtual table returned from a SELECT<\/a><\/code> statement. A derived table is similar to a temporary table<\/a>, but using a derived table in the SELECT<\/code> statement is much simpler than a temporary table because it does not require creating a temporary table.<\/p>\n\n\n\n

The term derived table and subquery<\/a> is often used interchangeably. When a stand-alone subquery is used in the FROM<\/code> clause of a SELECT<\/code> statement, it is also called a derived table.<\/p>\n\n\n\n

The following illustrates a query that uses a derived table:<\/p>\n\n\n\n

\"MySQL<\/figure>\n\n\n\n

Note that a stand-alone subquery is a subquery that can execute independently of the outer query.<\/p>\n\n\n\n

Unlike a subquery, a derived table must have an alias<\/a> so that you can reference its name later in the query. If a derived table does not have an alias, MySQL will issue the following error:<\/p>\n\n\n

Every derived table must have its own alias.<\/code><\/span><\/pre>\n\n\n
The following illustrates the syntax of a query that uses a derived table:<\/p>\n\n\n
SELECT<\/span> \n    select_list\nFROM<\/span>\n    (SELECT<\/span> \n        select_list\n    FROM<\/span>\n        table_1) derived_table_name\nWHERE<\/span> \n    derived_table_name.c1 > 0<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Basic MySQL Derived Table example<\/h2>\n\n\n\n
The following query gets the top five products by sales revenue in 2003 from the orders<\/code> and orderdetails<\/code> tables in the sample database<\/a>:<\/p>\n\n\n\n
\"Orders<\/figure>\n\n\n
SELECT<\/span> \n    productCode, \n    ROUND<\/span>(SUM<\/span>(quantityOrdered * priceEach)) sales\nFROM<\/span>\n    orderdetails\n        INNER<\/span> JOIN<\/span>\n    orders USING<\/span> (orderNumber)\nWHERE<\/span>\n    YEAR<\/span>(shippedDate) = 2003<\/span>\nGROUP<\/span> BY<\/span> productCode\nORDER<\/span> BY<\/span> sales DESC<\/span>\nLIMIT<\/span> 5<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
You can use the result of this query as a derived table and join it with the products<\/code> table as follows:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n
SELECT<\/span> \n    productName, sales\nFROM<\/span>\n    (SELECT<\/span> \n        productCode, \n        ROUND<\/span>(SUM<\/span>(quantityOrdered * priceEach)) sales\n    FROM<\/span>\n        orderdetails\n    INNER<\/span> JOIN<\/span> orders USING<\/span> (orderNumber)\n    WHERE<\/span>\n        YEAR<\/span>(shippedDate) = 2003<\/span>\n    GROUP<\/span> BY<\/span> productCode\n    ORDER<\/span> BY<\/span> sales DESC<\/span>\n    LIMIT<\/span> 5<\/span>) top5products2003\nINNER<\/span> JOIN<\/span>\n    products USING<\/span> (productCode);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following shows the output of the query above:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this example:<\/p>\n\n\n\n
    \n
  1. First, the subquery is executed to create a result set or derived table.<\/li>\n\n\n\n
  2. Then, the outer query is executed that joins the top5product2003<\/code> derived table with the products<\/code> table using the productCode<\/code> column.<\/li>\n<\/ol>\n\n\n\n
    A more complex MySQL Derived Table example<\/h2>\n\n\n\n
    Suppose you have to classify the customers who bought products in 2003 into 3 groups: platinum<\/code>, gold<\/code>, and silver<\/code>. And you need to know the number of customers in each group with the following conditions:<\/p>\n\n\n\n