Part 3 of 5 · 1 min read
Correlated subqueries
A subquery that references the outer query's current row, re-run once per row.
Think of checking each customer one at a time. For each one, ask whether that specific customer has placed more than one order, instead of computing one shared number for everyone at once.
In plain words: a correlated subquery references a column from the outer query, meaning it cannot run independently. Conceptually, it runs once per row of the outer query, using that row's values each time. This makes a correlated subquery more expressive than an uncorrelated one, and often slower if written carelessly.
A worked example. Finding every customer who has placed more than one order, using a correlated subquery that counts orders for each specific customer row.
The key lines.
SELECT c.full_name
FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) > 1;
Correlated subquery
A correlated subquery references a column from its enclosing query, so its result depends on the outer row currently being evaluated, rather than being computed once overall.
Example: o.customer_id = c.customer_id inside the subquery above ties it to whichever customer row the outer query is currently checking.