Derived Table

A derived table in MySQL is a virtual table that returned from the SELECT…FROM statement. In other words, it is an expression, which generates a table under the scope of the FROM clause in the SELECT statement. This concept is similar to the temporary table. However, the derived table is simpler than a temporary table because there is no need to use all steps that temporary tables need for its creation.

Most developers used the term derived table and subquery interchangeably. Thus, we can say that when we use a subquery in the SELECT…FROM clause, it is called a derived table.

Syntax

The following are the syntax to use the derived table in MySQL:

SELECT ... FROM (subquery) [AS] table_name (column_list) WHERE condition;  

It is to note that the stand-alone subquery is a subquery, which can be executed independently of the statement that contains this query.

We should also consider that a derived table must contain the alias table name that allows us to reference its name later in the statement. If we do not provide the alias name to a derived table, MySQL will issue the below error message:

Every derived table must have its own alias.

It is also mandatory that each column of the derived table should have unique names. Let us understand it with the help of the following illustration.

First, we will create a table using the below statement:

mysql> CREATE TABLE test1 (s1 INT, s2 CHAR(5), s3 FLOAT);  

Next, fill some record into this table using the below statement:

mysql> INSERT INTO test1 VALUES (1, 'A', 10.0), (2, 'B', 20.0), (3, 'C', 30.0);   

Execute the SELECT statement to verify the output:

Now, execute the below statement to understand how we can use a subquery in the FROM clause:

SELECT sb1, sb2, sb3  

  FROM (SELECT s1 AS sb1, s2 AS sb2, s3*5 AS sb3 FROM test1) AS test1_set  

  WHERE sb1 < 4;  

It will return the output as follows:

Let us another example where we will get the average of the float value column. Execute the below statement:

SELECT AVG(sum_float)  

  FROM (SELECT SUM(s3) AS sum_float FROM test1 GROUP BY s1) AS totals;  

In the above statement, the derived table calculates the total sum of the float column and then returns the average from the outer query. See the below output:

Derived tables in MySQL have the following restrictions:

• MySQL does not use a correlated subquery as a derived table.
• MySQL does not allow a derived table to use references to other tables of the same SELECT statement.
• We cannot use outer references in the derived table.