Correlated subqueries, EXISTS, and the NOT IN trap
A correlated subquery refers to a column of the outer row, so it is worked out once for each outer row. EXISTS asks "does the subquery return any row at all?", which makes it the safe way to test for a match.
Why it matters
"Each customer's latest order", "employees paid above their own department's average", "customers with at least one delivered order" — the comparison changes for every row. And the obvious way to find "no match", NOT IN, has a trap that returns nothing at all when NULLs are around.
The idea
In WHERE o.order_date = (SELECT MAX(o2.order_date) FROM orders o2 WHERE o2.customer_id = o.customer_id), the inner query uses o.customer_id from the outer row, so it is re-run (in effect) for each order.
EXISTS (subquery) is true if the subquery finds at least one row; what it selects doesn't matter, so people write SELECT 1. NOT EXISTS is true when it finds none.
The NOT IN trap: x NOT IN (1, 2, NULL) means x <> 1 AND x <> 2 AND x <> NULL, and the last part is always unknown — so the whole test is never true.
A correlated subquery is a teacher checking each pupil's mark against that pupil's own previous best, not one class-wide number. EXISTS is calling out "is anyone here from Leeds?" — one hand up is enough, and nobody has to say their name.
A teacher really does check each pupil one at a time. A database usually doesn't: the optimiser rewrites most correlated subqueries into a join that does all the rows at once. Think of it as row by row; don't assume it is slow.
Syntax
WHERE EXISTS (
SELECT 1 FROM other o
WHERE o.key = outer_table.key
)How it works
- Outer row: order 101 of customer 1. Inner query: the latest order_date for customer 1 is 2024-03-10. 101's date is 2024-01-06, so it fails.
- Order 109 of customer 1 has date 2024-03-10, which equals the latest, so it is kept.
- This repeats for every order, so each customer's latest order is kept.
- EXISTS: for customer Omar, the inner query finds no delivered orders, so EXISTS is false and Omar is dropped.
- NOT IN with a list containing NULL is never true, so
dept_id NOT IN (SELECT dept_id FROM employees)returns no rows — because Gus's dept_id is NULL.
Key points
- A correlated subquery uses outer-row columns; logically it runs once per outer row.
- EXISTS is true when the subquery returns at least one row;
SELECT 1inside is a convention. - NOT EXISTS is the NULL-safe "no match" test. Prefer it to NOT IN.
x NOT IN (… NULL …)is never true.x IN (… NULL …)still works for the values that match.- Correlated subqueries are clear for "compare with my own group"; window functions (Unit 5) often do the same faster.
Common mistakes
- Using NOT IN against a column that can be NULL.
- Forgetting the correlation (
WHERE o2.customer_id = o.customer_id), which compares every row with the overall maximum instead. - Thinking EXISTS cares what the subquery selects. It only checks whether a row exists.
Not true: EXISTS gives a different answer depending on which columns the subquery selects.
In fact: EXISTS only checks whether at least one row comes back. What the row contains makes no difference, which is why SELECT 1 is the usual choice.
In the interview
A correlated subquery references the outer query's row, so logically it is evaluated per row — useful for comparisons against a row's own group, like each customer's latest order. EXISTS tests whether a subquery returns any row. NOT EXISTS is my default anti-join, because NOT IN returns nothing if the subquery yields a single NULL: x not in a list containing NULL can never be true.
Part of Unit 4: Subqueries, CTEs and set operations in the free SQL course.