13.2.8.8. Subqueries in the FROM clause
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. You can find this syntax described
elsewhere in this manual, where the term used is
“derived tables.”
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.
Subqueries in the FROM clause are executed
even for the EXPLAIN statement (that is,
derived temporary tables are built). This occurs because upper
level queries need information about all tables during
optimization phase.
