(mysql.info.gz) Correlated subqueries

(mysql.info.gz) EXISTS and NOT EXISTS subqueries

 
 13.1.8.7 Correlated Subqueries
 ..............................
 
 A _correlated subquery_ is a subquery that contains a reference to a
 table that also appears in the outer query.  For example:
 
      SELECT * FROM t1 WHERE column1 = ANY
             (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);
 
 Notice that the subquery contains a reference to a column of `t1', even
 though the subquery's `FROM' clause doesn't mention a table `t1'. So,
 MySQL looks outside the subquery, and finds `t1' in the outer query.
 
 Suppose that table `t1' contains a row where `column1 = 5' and `column2
 = 6'; meanwhile, table `t2' contains a row where `column1 = 5' and
 `column2 = 7'. The simple expression `... WHERE column1 = ANY (SELECT
 column1 FROM t2)' would be `TRUE', but in this example, the `WHERE'
 clause within the subquery is `FALSE' (because `(5,6)' is not equal to
 `(5,7)'), so the subquery as a whole is `FALSE'.
 
 *Scoping rule:* MySQL evaluates from inside to outside.  For example:
 
      SELECT column1 FROM t1 AS x
        WHERE x.column1 = (SELECT column1 FROM t2 AS x
          WHERE x.column1 = (SELECT column1 FROM t3
            WHERE x.column2 = t3.column1));
 
 In this statement, `x.column2' must be a column in table `t2' because
 `SELECT column1 FROM t2 AS x ...' renames `t2'. It is not a column in
 table `t1' because `SELECT column1 FROM t1 ...' is an outer query that
 is _farther out_.
 
 For subqueries in `HAVING' or `ORDER BY' clauses, MySQL also looks for
 column names in the outer select list.
 
 For certain cases, a correlated subquery is optimized. For example:
 
      VAL IN (SELECT KEY_VAL FROM TBL_NAME WHERE CORRELATED_CONDITION)
 
 Otherwise, they are inefficient and likely to be slow. Rewriting the
 query as a join might improve performance.

Info Catalog

(mysql.info.gz) EXISTS and NOT EXISTS subqueries

(mysql.info.gz) Subqueries

(mysql.info.gz) Unnamed views

automatically generated byinfo2html