13.2.9.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 expression 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.

Aggregate functions in correlated subqueries may contain outer
references, provided the function contains nothing but outer
references, and provided the function is not contained in
another function or expression.

Note that according to bug #9090 ( http://bugs.mysql.com/bug.php?id=9090 ) it appears that ALL subqueries will be executed as correlated subqueries until version 5.2 or 6.0, when the optimizer may gain the ability to correctly execute uncorrelated subqueries.

Ryan Findley is good to point out the bug report, but as far as I can tell it is not true that MySQL rewrites ALL subqueries as correlated subqueries. Rather, the problem that the bug report identifies is that the MySQL optimizer does that to subqueries that test an IN statement. This is listed as one of the current limitations of MySQL's subquery support here (third item down, "Subquery optimization for IN..."):