A query using NOT IN with a subquery suddenly returns zero rows even though you expect matches. What's likely going on, and how would you fix it?
technical-conceptual · Mid level · software-engineering
What the interviewer is really asking
Assesses whether the candidate understands SQL's three-valued logic — that a NULL in the NOT IN subquery makes every comparison evaluate to UNKNOWN, so no row passes the WHERE filter — and knows the standard fixes (NOT EXISTS, a LEFT JOIN anti-join, or filtering NULLs), rather than treating it as a mysterious bug.
What to say
- Name the root cause: SQL uses three-valued logic, so if the subquery returns even one NULL, NOT IN expands to a chain of x <> val AND ... AND x <> NULL, and the NULL comparison is UNKNOWN — which is never TRUE, so the WHERE clause filters out every row.
- Show you can diagnose it: check whether the subquery column is nullable and actually contains NULLs, since the failure only appears when NULL is present in the result set.
- Give the fix you'd reach for and why — NOT EXISTS (correlated, only ever returns TRUE/FALSE so NULLs don't poison it) or a LEFT JOIN ... WHERE right.key IS NULL anti-join, with adding IS NOT NULL to the subquery as a quick patch.
What to avoid
- Calling it a database bug or blaming the optimizer instead of recognizing it's defined three-valued-logic behavior.
- Saying you'd just switch NOT IN to IN and invert the app logic, which doesn't address the NULL semantics and often changes the result.
- Claiming NOT IN and NOT EXISTS are always interchangeable — they behave identically only when no NULLs are involved, which is exactly the case that breaks here.
Example answers
Strong: The subquery almost certainly returns a NULL. With NOT IN, the row has to be unequal to every value in the list, and any comparison to NULL is UNKNOWN, so the whole predicate is never TRUE and every row gets dropped. I'd rewrite it as NOT EXISTS with a correlated subquery, since that only yields TRUE or FALSE and ignores the NULL trap — or add IS NOT NULL to the inner query if I want the smallest change.
Weak: NOT IN can be slow on big tables, so it's probably timing out or the optimizer is choosing a bad plan. I'd add an index on the column and maybe rewrite it as a join to speed it up.