MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
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:
SELECT ... FROM (subquery
) [AS]tbl_name
...
The JSON_TABLE()
function
generates a table and provides another way to create a derived
table:
SELECT * FROM JSON_TABLE(arg_list
) [AS]tbl_name
...
The [AS]
clause is mandatory because every table in a
tbl_name
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:
SELECT ... FROM (subquery
) [AS]tbl_name
(col_list
) ...
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:
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
Here is how to use a subquery in the FROM
clause, using the example table:
INSERT INTO t1 VALUES (1,'1',1.0); INSERT INTO t1 VALUES (2,'2',2.0); SELECT sb1,sb2,sb3 FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb WHERE sb1 > 1;
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:
SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;
However, this query provides the desired information:
SELECT AVG(sum_column1) FROM (SELECT SUM(column1) AS sum_column1 FROM t1 GROUP BY column1) AS t1;
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:
mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt;
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
To provide column names explicitly, follow the derived table name with a parenthesized list of column names:
mysql> SELECT * FROM (SELECT 1, 2, 3, 4) AS dt (a, b, c, d);
+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
A derived table can return a scalar, column, row, or table.
Derived tables are subject to these restrictions:
A derived table cannot contain references to other tables of
the same SELECT
(use a
LATERAL
derived table for that; see
Section 13.2.11.9, “Lateral Derived Tables”).
Prior to MySQL 8.0.14, a derived table cannot contain outer
references. This is a MySQL restriction that is lifted in
MySQL 8.0.14, not a restriction of the SQL standard. For
example, the derived table dt
in the
following query contains a reference t1.b
to the table t1
in the outer query:
SELECT * FROM t1 WHERE t1.d > (SELECT AVG(dt.a) FROM (SELECT SUM(t2.a) AS a FROM t2 WHERE t2.b = t1.b GROUP BY t2.c) dt WHERE dt.a > 10);
The query is valid in MySQL 8.0.14 and higher. Before
8.0.14, it produces an error: Unknown column 't1.b'
in 'where clause'
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
modifies 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:
CREATE DATABASE d1; USE d1; CREATE TABLE t1 (c1 INT); CREATE TABLE t2 (c1 INT); CREATE FUNCTION f1(p1 INT) RETURNS INT BEGIN INSERT INTO t2 VALUES (p1); RETURN p1; END;
Referencing the function directly in an
EXPLAIN
SELECT
has no effect on t2
, as
shown here:
mysql>SELECT * FROM t2;
Empty set (0.02 sec) mysql>EXPLAIN SELECT f1(5)\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: No tables used 1 row in set (0.01 sec) mysql>SELECT * FROM t2;
Empty set (0.01 sec)
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
:
mysql>EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2\G
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: No tables used 1 row in set, 1 warning (0.00 sec) mysql>SHOW WARNINGS;
+-------+------+------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------+ | Note | 1249 | Select 2 was reduced during optimization | +-------+------+------------------------------------------+ 1 row in set (0.00 sec) mysql>SELECT * FROM t2;
Empty set (0.00 sec)
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:
mysql>EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2\G
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> partitions: NULL type: system possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: PRIMARY table: a1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL *************************** 3. row *************************** id: 2 select_type: DERIVED table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: No tables used 3 rows in set (0.00 sec) mysql>SELECT * FROM t2;
+------+ | c1 | +------+ | 5 | +------+ 1 row in set (0.00 sec)
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
:
EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));