(mysql.info) unnamed-views

Info Catalog

(mysql.info) correlated-subqueries

(mysql.info) subqueries

(mysql.info) subquery-errors

 
 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.
 

Info Catalog

(mysql.info) correlated-subqueries

(mysql.info) subqueries

(mysql.info) subquery-errors