SQLIntermediate
EXISTS vs IN vs NOT IN
“When would you use each, and which is dangerous?”
What this tests
- Subquery semantics
- NULL safety
- Correlated vs uncorrelated cost
Answers by level
Read the beginner answer first and notice what is missing.
For positive tests, EXISTS and IN are usually interchangeable and the planner rewrites both into a semi-join. EXISTS stops at the first match and is NULL-safe.
NOT IN with a subquery that can return NULL is the trap: one NULL makes the whole predicate NULL and the query returns nothing. Use NOT EXISTS for anti-joins.
Green flags · Red flags
Strong green flag · Reaches for NOT EXISTS by default for anti-joins.
Green flags
- Flags NOT IN + nullable subquery
- Knows EXISTS short-circuits
- Checks the plan for correlation
Red flags
- Uses NOT IN on a nullable column
- Believes IN is always slower than EXISTS or vice versa without measuring
Follow-up questions
F1
How does a correlated EXISTS show up in EXPLAIN?
Scenario
A "products never ordered" query using NOT IN returns nothing. Explain and fix.