An Essential Guide to MySQL Derived Table?

Summary: in this tutorial, you will learn about the MySQL derived table and how to it to simplify complex queries.

Introduction to MySQL derived table

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 steps of creating the temporary table.

The term derived table and subquery is often used interchangeably. When a stand-alone subquery is used in the FROM clause of a SELECT statement, we call it a derived table.

The following illustrates a query that uses a derived table:

Note that a stand-alone subquery is a subquery which can execute independently of the statement containing it.

Unlike a subquery, a derived table must have a 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 the following error:

1	Every derived table must have its own alias.

The following illustrates an SQL statement that uses a derived table:

1 2 3 4 5 6 7 8

SELECT     column_list FROM     (SELECT         column_list     FROM         table_1) derived_table_name; WHERE derived_table_name.c1 > 0;

A simple MySQL derived table example

The following query gets the top 5 products by sales revenue in 2003 from the orders and orderdetails tables in the sample database:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT     productCode,     ROUND(SUM(quantityOrdered * priceEach)) sales FROM     orderdetails         INNER JOIN     orders USING (orderNumber) WHERE     YEAR(shippedDate) = 2003 GROUP BY productCode ORDER BY sales DESC LIMIT 5;

You can use the result of this query as a derived table and join it with the products table as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SELECT     productName, sales FROM     (SELECT         productCode,         ROUND(SUM(quantityOrdered * priceEach)) sales     FROM         orderdetails     INNER JOIN orders USING (orderNumber)     WHERE         YEAR(shippedDate) = 2003     GROUP BY productCode     ORDER BY sales DESC     LIMIT 5) top5products2003 INNER JOIN     products USING (productCode);

The following shows the output of the query above:

In this example:

1. First, the subquery executed to create a result set or derived table.
2. Then, the outer query executed that joined the top5product2003 derived table with the productstable using the productCode column.

A more complex MySQL derived table example

Suppose you have to classify the customers in the year of 2003 into 3 groups: platinum, gold, and silver. In addition, you need to know the number of customers in each group with the following conditions:

1. Platinum customers who have orders with the volume greater than 100K
2. Gold customers who have orders with the volume between 10K and 100K
3. Silver customers who have orders with the volume less than 10K

To construct this query, first, you need to put each customer into the respective group using CASEexpression and GROUP BY clause as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT     customerNumber,     ROUND(SUM(quantityOrdered * priceEach)) sales,     (CASE         WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'         WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'         WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'     END) customerGroup FROM     orderdetails         INNER JOIN     orders USING (orderNumber) WHERE     YEAR(shippedDate) = 2003 GROUP BY customerNumber;

The following is the output of the query:

Then, you can use this query as the derived table and perform grouping as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SELECT     customerGroup,     COUNT(cg.customerGroup) AS groupCount FROM     (SELECT         customerNumber,             ROUND(SUM(quantityOrdered * priceEach)) sales,             (CASE                 WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'                 WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'                 WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'             END) customerGroup     FROM         orderdetails     INNER JOIN orders USING (orderNumber)     WHERE         YEAR(shippedDate) = 2003     GROUP BY customerNumber) cg GROUP BY cg.customerGroup;

The query returns the customer groups and the number of customers in each.

In this tutorial, you have learned how to use the MySQL derived tables which are subqueries in the FROM clause to simplify complex queries.