Part 4 of 5 · 2 min read
IN versus EXISTS
Two ways to ask "does a matching row exist," with different behavior around NULL.
IN versus EXISTS
Think of two ways to check a guest list. One way reads the whole list and checks if a name appears anywhere on it. The other just asks the door attendant whether anyone at all matches the description.
In plain words: IN checks whether a value appears in a list or subquery result. EXISTS checks only whether a correlated subquery returns any row at all, without caring what the row's values are. EXISTS often performs better for a correlated existence check, and behaves more predictably when the subquery's result could contain NULL.
A worked example. Finding customers who have placed at least one order, written both ways.
The key lines.
SELECT full_name FROM customers c
WHERE c.customer_id IN (SELECT customer_id FROM orders);
SELECT full_name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
IN, EXISTS
IN checks whether a value matches any value in a list or subquery result. EXISTS checks only whether a correlated subquery returns at least one row.
Example: both queries above return the same customers, but EXISTS stops as soon as it finds one matching order, while IN conceptually builds the whole list first.
For the curious The NULL trap with NOT IN
NOT IN (subquery) behaves incorrectly if the subquery's result contains even one NULL: the whole condition becomes unknown for every row, returning nothing. NOT EXISTS does not have this problem, which is why it is the safer default for a "has no matching row" check.
Quick notes
- A scalar subquery returns one value; a nested subquery returns a set, usually checked with IN.
- A correlated subquery references the outer row, conceptually running once per outer row.
- EXISTS checks only for the presence of a matching row; IN checks value membership in a list.
- EXISTS is generally the safer and often faster choice for existence checks, especially negated ones.
- NOT IN against a subquery containing NULL can silently return no rows at all; NOT EXISTS avoids this.