13.2.11.8 Derived Tables

This section discusses general characteristics of derived tables. For information about lateral derived tables preceded by the LATERAL keyword, see Section 13.2.11.9, “Lateral Derived Tables”.

A derived table is an expression that generates a table within the scope of a query FROM clause. For example, a subquery in a SELECT statement FROM clause is a derived table:

The JSON_TABLE() function generates a table and provides another way to create a derived table:

The [AS] tbl_name clause is mandatory because every table in a FROM clause must have a name. Any columns in the derived table must have unique names. Alternatively, tbl_name may be followed by a parenthesized list of names for the derived table columns:

The number of column names must be the same as the number of table columns.

For the sake of illustration, assume that you have this table:

Here is how to use a subquery in the FROM clause, using the example table:

Result:

+------+------+------+
| sb1  | sb2  | sb3  |
+------+------+------+
|    2 | 2    |    4 |
+------+------+------+

Here is another example: Suppose that you want to know the average of a set of sums for a grouped table. This does not work:

However, this query provides the desired information:

Notice that the column name used within the subquery (sum_column1) is recognized in the outer query.

The column names for a derived table come from its select list:

To provide column names explicitly, follow the derived table name with a parenthesized list of column names:

A derived table can return a scalar, column, row, or table.

Derived tables are subject to these restrictions:

The optimizer determines information about derived tables in such a way that EXPLAIN does not need to materialize them. See Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.

It is possible under certain circumstances that using EXPLAIN SELECT will modify table data. This can occur if the outer query accesses any tables and an inner query invokes a stored function that changes one or more rows of a table. Suppose that there are two tables t1 and t2 in database d1, and a stored function f1 that modifies t2, created as shown here:

Referencing the function directly in an EXPLAIN SELECT has no effect on t2, as shown here:

This is because the SELECT statement did not reference any tables, as can be seen in the table and Extra columns of the output. This is also true of the following nested SELECT:

However, if the outer SELECT references any tables, the optimizer executes the statement in the subquery as well, with the result that t2 is modified:

This also means that an EXPLAIN SELECT statement such as the one shown here may take a long time to execute because the BENCHMARK() function is executed once for each row in t1: