Subqueries are legal in a SELECT statement's FROM clause. The actual syntax is:

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

The [AS] name clause is mandatory, because every table in a FROM clause must have a name. Any columns in the subquery select list must have unique names.

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: 2, '2', 4.0.

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.

Subqueries in the FROM clause can return a scalar, column, row, or table. Subqueries in the FROM clause cannot be correlated subqueries, unless used within the ON clause of a JOIN operation.

Before MySQL 5.6.3, subqueries in the FROM clause are executed even for the EXPLAIN statement (that is, derived temporary tables are materialized). This occurs because upper-level queries need information about all tables during the optimization phase, and the table represented by a subquery in the FROM clause is unavailable unless the subquery is executed. As of MySQL 5.6.3, the optimizer determines information about derived tables in a different way and materialization of them does not occur for EXPLAIN. See Section 8.13.16.3, "Optimizing Subqueries in the FROM Clause (Derived Tables)".

It is possible under certain circumstances to modify table data using EXPLAIN SELECT. 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, created as shown here:

mysql> CREATE DATABASE d1;Query OK, 1 row affected (0.00 sec)mysql> USE d1;Database changedmysql> CREATE
        TABLE t1 (c1 INT);Query OK, 0 rows affected (0.15 sec)mysql> CREATE
        TABLE t2 (c1 INT);Query OK, 0 rows affected (0.08 sec)

Now we create a stored function f1 which modifies t2:

mysql> DELIMITER //mysql> CREATE FUNCTION f1(p1 INT) RETURNS INTmysql>   BEGINmysql>     INSERT INTO t2
        VALUES (p1);mysql>     RETURN p1;mysql>   END //Query OK, 0 rows affected (0.01 sec)mysql> DELIMITER ;

Referencing the function directly in an EXPLAIN SELECT does not have any effect on t2, as shown here:

mysql> SELECT * FROM t2;Empty set (0.00 sec)mysql> EXPLAIN SELECT f1(5);+----+-------------+-------+------+---------------+------+---------+------+------+----------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+1 row in set (0.00 sec)mysql> SELECT * FROM t2;Empty set (0.00 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;+----+-------------+-------+------+---------------+------+---------+------+------+----------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+|  1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | 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:

mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5))
        AS a2;+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra               |+----+-------------+------------+--------+---------------+------+---------+------+------+---------------------+|  1 | PRIMARY     | a1         | system | NULL          | NULL | NULL    | NULL |    0 | const row not found ||  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |                     ||  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | 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())));