DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(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