+---+---+---+---+ | 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 

