EXISTS vs IN Performance
When EXISTS short-circuits and outperforms IN, a frequent senior-screening question.
What EXISTS Actually Tests
EXISTS takes a subquery and returns true the moment that subquery produces at least one row. It does not care about the values returned — only whether any row exists.
- It is a boolean test, used in
WHERE. - It is almost always correlated: the inner query references the outer row.
This single-question screen appears in nearly every mid-to-senior SQL interview.
A Basic EXISTS Query
Find customers who have placed at least one order. The inner query is correlated by o.customer_id = c.id; EXISTS returns true as soon as one matching order is found.
Note SELECT 1 — the projected value is irrelevant, so most engineers write 1 or *. Interviewers accept either; the optimizer ignores the select list inside EXISTS.
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
);