A subquery that depends on the outer row
A correlated subquery references a column from the outer query, so it cannot run independently. Conceptually it re evaluates once per outer row, using that row's values as inputs. This makes it powerful but potentially expensive.
Common patterns
- EXISTS checks whether at least one matching row exists. It stops at the first match, so it is often cheaper than counting.
- NOT EXISTS finds outer rows with no match, the standard anti join pattern.
- A scalar correlated subquery in SELECT returns one value per outer row, such as the latest order date for each customer.
EXISTS versus IN with nulls
EXISTS and IN often feel interchangeable, but they differ around nulls. NOT IN against a list that contains a null can surprise you by returning no rows, because the comparison becomes unknown. NOT EXISTS avoids that trap and is the safer anti join choice.
Performance note
Although the mental model is row by row, modern optimizers frequently rewrite correlated subqueries into joins or semi joins. Still, ensuring the inner table has an index on the correlated column keeps these queries fast.
Key idea
A correlated subquery references the outer row and conceptually runs per row; EXISTS and NOT EXISTS are the efficient, null safe patterns, and indexing the correlated column keeps them fast.