184.108.40.206 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
(5,6) is not equal to
(5,7)), so the expression as a whole is
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
SELECT column1 FROM t2 AS x ... renames
t2. It is not a column in table
SELECT column1 FROM t1 ... is an outer query that is farther out.
For subqueries in
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.