Correlated Subqueries
A subquery that references a column of the outer row is evaluated per row, unless the MySQL 8.4 optimizer rewrites it away.
SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.col = t1.col)A correlated subquery references a column from a table in the enclosing query. Its own FROM clause does not mention that table, so MySQL has to look outward to resolve the name. Conceptually the subquery is re-evaluated once for every candidate row of the outer query, which is what makes it expressive and what makes it expensive.
SELECT * FROM t1
WHERE column1 = ANY (SELECT column1 FROM t2
WHERE t2.column2 = t1.column2);Name resolution works inside out
An unqualified or aliased name is resolved in the innermost query block that can provide it, and only then outward. If an inner query reuses an alias from the outer query, the inner one wins. This is also the source of the nastiest bug in this lesson: misspell an inner column and, if the outer table happens to have a column by that name, MySQL resolves it outward instead of raising ERROR 1054. Qualifying every column with its table alias prevents it.
What the optimizer can do about the cost
Per-row evaluation is the semantics, not necessarily the plan. Correlated IN, = ANY and EXISTS predicates at the top level of WHERE or ON are eligible for semijoin transformation, and since MySQL 8.0.17 NOT IN and NOT EXISTS for antijoin. A correlated scalar subquery has fewer options: the subquery_to_derived flag in optimizer_switch can turn one into a derived table with grouping and an outer join, but it defaults to off in MySQL 8.4. Check EXPLAIN rather than assuming.
Example
-- Each customer with the date of their most recent paid order.
SELECT
c.id,
c.name,
(SELECT o.ordered_at
FROM orders AS o
WHERE o.customer_id = c.id -- outer reference: this is the correlation
AND o.status = 'paid'
ORDER BY o.ordered_at DESC, o.id DESC
LIMIT 1) AS last_paid_at
FROM customers AS c
-- A second correlated subquery, this time as an existence test, so customers
-- with no paid order are dropped rather than shown with a NULL date.
WHERE EXISTS (SELECT 1 FROM orders AS o2
WHERE o2.customer_id = c.id AND o2.status = 'paid');When to use it
- Attach the latest related row's value to each parent row in a report.
- Compare a row to an aggregate of its own group, such as lines above their order's average.
- Filter parents by a condition on their children with EXISTS.
- Rank or limit per group where a plain GROUP BY cannot carry the other columns.
More examples
Correlated subquery versus a LATERAL derived table
A plain derived table in FROM cannot reference other tables of the same SELECT; LATERAL is what lifts that restriction. Reach for it as soon as you need more than one column from the same 'top row per group' lookup, because the two-subquery version states the lookup twice and can drift out of sync when someone edits only one of them.
-- Two correlated scalar subqueries: the same lookup is expressed twice.
SELECT s.name,
(SELECT a.amount FROM all_sales AS a WHERE a.salesperson_id = s.id
ORDER BY a.amount DESC LIMIT 1) AS amount,
(SELECT a.customer_name FROM all_sales AS a WHERE a.salesperson_id = s.id
ORDER BY a.amount DESC LIMIT 1) AS customer_name
FROM salesperson AS s;
-- LATERAL (MySQL 8.0.14 and later): one correlated derived table, many columns.
SELECT s.name, max_sale.amount, max_sale.customer_name
FROM salesperson AS s,
LATERAL (SELECT a.amount, a.customer_name
FROM all_sales AS a
WHERE a.salesperson_id = s.id -- allowed only because of LATERAL
ORDER BY a.amount DESC LIMIT 1) AS max_sale;The alias-shadowing trap
The broken query runs without error and returns plausible-looking rows, which is why this bug survives review. MySQL resolves names in the innermost block that can supply them, so reusing an alias silently changes meaning. Give inner queries their own alias names, always, and never leave a column in a correlated subquery unqualified.
-- Intended: order lines whose price is above the average for that product.
-- Broken: the inner alias 'oi' shadows the outer one, so oi.product_id inside
-- the subquery refers to the INNER table, and the correlation disappears.
SELECT oi.order_id, oi.product_id, oi.unit_price
FROM order_items AS oi
WHERE oi.unit_price > (SELECT AVG(oi.unit_price)
FROM order_items AS oi
WHERE oi.product_id = oi.product_id); -- always true
-- Correct: distinct aliases, every column qualified.
SELECT oi.order_id, oi.product_id, oi.unit_price
FROM order_items AS oi
WHERE oi.unit_price > (SELECT AVG(inner_oi.unit_price)
FROM order_items AS inner_oi
WHERE inner_oi.product_id = oi.product_id);When a window function replaces the correlation
Whenever the correlated subquery aggregates the same table the outer query is scanning, a window function over a partition usually expresses it more cheaply. The derived table is required because a window function cannot appear in WHERE: it is evaluated after WHERE, so the filter has to happen one level out. Compare the two with EXPLAIN on your own data before committing to either.
-- Correlated: the inner aggregate is re-evaluated per outer row.
SELECT oi.order_id, oi.product_id, oi.unit_price
FROM order_items AS oi
WHERE oi.unit_price > (SELECT AVG(x.unit_price)
FROM order_items AS x
WHERE x.order_id = oi.order_id);
-- Window function: one pass, the average computed per partition.
SELECT order_id, product_id, unit_price
FROM (
SELECT oi.order_id, oi.product_id, oi.unit_price,
AVG(oi.unit_price) OVER (PARTITION BY oi.order_id) AS order_avg
FROM order_items AS oi
) AS priced
WHERE unit_price > order_avg; -- window functions are not allowed in WHERE
Discussion