(mysql.info) correlated-subqueries

(mysql.info) exists-and-not-exists-subqueries

 
 13.2.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 does not 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.
 
 Correlated subqueries cannot refer to the results of aggregate
 functions from the outer query.

Info Catalog

(mysql.info) exists-and-not-exists-subqueries

(mysql.info) subqueries

(mysql.info) unnamed-views

automatically generated byinfo2html