(mysql.info.gz) Unnamed views
Info Catalog
(mysql.info.gz) Correlated subqueries
(mysql.info.gz) Subqueries
(mysql.info.gz) Subquery errors
13.1.8.8 Subqueries in the `FROM' clause
........................................
Subqueries are legal in a `SELECT' statement's `FROM' clause. The
syntax that you'll actually see 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 illustration, assume that you have this table:
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
Here's 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's another example: Suppose that you want to know the average of a
set of sums for a grouped table. This won't work:
SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;
But this query will provide 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. At the moment, subqueries in the `FROM' clause cannot be
correlated subqueries.
Subqueries in the `FROM' clause will be executed even for the `EXPLAIN'
statement (that is, derived temporary tables will be built). This
occurs because upper level queries need information about all tables
during optimization phase.
Info Catalog
(mysql.info.gz) Correlated subqueries
(mysql.info.gz) Subqueries
(mysql.info.gz) Subquery errors
automatically generated byinfo2html