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

  1. 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.
  2. Order 109 of customer 1 has date 2024-03-10, which equals the latest, so it is kept.
  3. This repeats for every order, so each customer's latest order is kept.
  4. EXISTS: for customer Omar, the inner query finds no delivered orders, so EXISTS is false and Omar is dropped.
  5. 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

Common mistakes

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.