Premium

Correlated Subqueries

Basic SQL
≈ 1 min read

A correlated subquery depends on the outer row

An ordinary independent subquery can be understood separately from the outer query. A correlated subquery refers to columns from the current row of the outer query, so its meaning changes with that row. 

SELECT u.id, u.name
FROM users AS u
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.user_id = u.id
      AND o.status = 'PAID'
);

The reference u.id comes from the outer query. For each user, the inner query checks that particular user's paid orders.

Read correlation as a parameter to the inner query

A useful mental model is that the current outer row passes a value into the inner query. Then a condition such as o.user_id = u.id becomes a clear connection between the two levels.

Correlated subqueries require especially careful reading of aliases. Always identify which alias belongs to the outer query and which belongs to the inner query. Otherwise, it is easy to lose track of the dependency.
Back Next