Movatterモバイル変換

This section discusses general characteristics of derived tables. For information about lateral derived tables preceded by theLATERAL keyword, seeSection 15.2.15.9, “Lateral Derived Tables”.

A derived table is an expression that generates a table within the scope of a queryFROM clause. For example, a subquery in aSELECT statementFROM clause is a derived table:

SELECT ... FROM (subquery) [AS]tbl_name ...

TheJSON_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]tbl_name clause is mandatory because every table in aFROM 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 theFROM 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:

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

It is possible under certain circumstances that usingEXPLAIN 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 tablest1 andt2 in databased1, and a stored functionf1 that modifiest2, 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 anEXPLAIN SELECT has no effect ont2, 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: NULLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: NULL     filtered: NULL        Extra: No tables used1 row in set (0.01 sec)mysql> SELECT * FROM t2;Empty set (0.01 sec)

This is because theSELECT statement did not reference any tables, as can be seen in thetable andExtra columns of the output. This is also true of the following nestedSELECT:

mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2\G*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: NULL         type: NULLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: NULL     filtered: NULL        Extra: No tables used1 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 outerSELECT references any tables, the optimizer executes the statement in the subquery as well, with the result thatt2 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: systempossible_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: ALLpossible_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: NULLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: NULL     filtered: NULL        Extra: No tables used3 rows in set (0.00 sec)mysql> SELECT * FROM t2;+------+| c1   |+------+|    5 |+------+1 row in set (0.00 sec)

The derived table optimization can also be employed with many correlated (scalar) subqueries (MySQL 8.0.24 and later). For more information and examples, seeSection 15.2.15.7, “Correlated Subqueries”.