Why can `id NOT IN (SELECT manager_id FROM employees)` return no rows at all?
answer
- the subquery contains a NULL
- NOT IN expands to a universal claim
- comparing to NULL is not FALSE
- WHERE keeps only TRUE, discards UNKNOWN
- NOT EXISTS is two-valued, so immune
basics
~20 sIf the subquery yields even one NULL, every NOT IN comparison ends in UNKNOWN rather than TRUE, and WHERE keeps only TRUE rows. The whole filter therefore matches nothing. NOT EXISTS avoids this because it is two-valued.
solid answer
~40 s`x NOT IN (<subquery>)` is defined as `NOT (x = ANY (<subquery>))`, i.e. `x <> ALL (<subquery>)`: it is TRUE only if `x <> v` is TRUE for **every** value the subquery returns. Comparing anything to NULL gives UNKNOWN, not TRUE, so a single NULL among the manager ids means no row can ever satisfy "different from all of them" — the predicate is UNKNOWN for every candidate, `WHERE` discards UNKNOWN, and the query returns an empty result. It fails silently: no error, just zero rows. The NULL-safe form is `NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.id)`, because the inner NULL row simply fails to match and EXISTS still answers a definite no. Filtering the NULLs out inside the subquery, or declaring the column `NOT NULL`, also removes the hazard.
code
sql · 11 lines-- Broken: returns zero rows whenever any manager_id is NULL
SELECT e.id
FROM employees e
WHERE e.id NOT IN (SELECT manager_id FROM employees);
-- Fixed: NULL-safe anti-filter
SELECT e.id
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM employees m WHERE m.manager_id = e.id
);go deeper
Recognise the symptom: a NOT IN filter that returns nothing usually means the subquery contains a NULL. Knowing to reach for NOT EXISTS, and being able to say NULLs are the cause, is enough here.
Expand NOT IN to <> ALL and walk the truth values out loud for a matching and a non-matching candidate. Explain why the query returns empty rather than erroring, and why plain IN does not break the same way.
Show the diagnosis path — check the subquery column for NULLs first — and argue for a durable fix such as a NOT NULL constraint or a standing NOT EXISTS convention rather than a one-off IS NOT NULL patch.
Frame it as a silent-wrong-answer class rather than a bug: it produces no error, no log line, and a plausible empty result. Own the guardrails — nullability in the schema, review conventions, and fixtures that include a NULL row.
## The query that looks obviously right ```sql -- "employees who manage nobody" — returns ZERO rows if any manager_id is NULL SELECT e.id FROM employees e WHERE e.id NOT IN (SELECT manager_id FROM employees); ``` Every top-level employee has `manager_id` NULL, so the subquery almost certainly contains NULLs, and the query returns nothing. No error is raised. This is the single most reliable SQL trap in interviews because the query reads correctly in English. ## What NOT IN actually means The standard defines `x IN (<subquery>)` as `x = ANY (<subquery>)`, and `x NOT IN (<subquery>)` as `NOT (x IN (<subquery>))`, which is equivalent to `x <> ALL (<subquery>)`. So NOT IN is a **universal** claim: *for every value v the subquery returns, x <> v must be TRUE*. The truth rules for the quantified form are: - `x <> ALL (...)` is TRUE when every comparison is TRUE. - It is FALSE as soon as one comparison is FALSE (a match was found). - Otherwise — no FALSE, but at least one UNKNOWN — it is UNKNOWN. ## Three-valued logic, in one paragraph NULL means "value not known". Comparing an unknown value to anything therefore cannot yield a definite answer, so `5 = NULL`, `5 <> NULL` and even `NULL = NULL` all evaluate to UNKNOWN. `WHERE`, `ON` and `HAVING` keep a row only when the predicate is TRUE; UNKNOWN behaves like FALSE for row-keeping purposes but is not the same value, which is why `NOT UNKNOWN` is still UNKNOWN rather than TRUE. Negating your way out of the trap is impossible. ## Walking the trap Suppose the subquery returns `1`, `2`, `NULL`, and the candidate is `3`: - `3 <> 1` → TRUE - `3 <> 2` → TRUE - `3 <> NULL` → UNKNOWN No comparison is FALSE, but one is UNKNOWN, so the ALL predicate is UNKNOWN. The row is dropped. Now try `1`: - `1 <> 1` → FALSE → the whole ALL predicate is FALSE immediately. So matching rows are correctly rejected and non-matching rows are *also* rejected. Nothing can survive. That is why the result set is empty rather than merely wrong. ## The flip side: plain IN still works `x IN (1, 2, NULL)` is an existential claim, so a real match still wins: `1 IN (1, 2, NULL)` is TRUE, because one comparison is TRUE and TRUE dominates UNKNOWN in `OR`-style logic. Only the non-matching case degrades: `3 IN (1, 2, NULL)` is UNKNOWN rather than FALSE. Since `WHERE` treats UNKNOWN and FALSE the same way for filtering, `IN` with NULLs usually behaves as people expect — which is exactly why the NOT IN case ambushes them. ## Why NOT EXISTS is immune ```sql SELECT e.id FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM employees m WHERE m.manager_id = e.id ); ``` Inside the subquery, a row whose `manager_id` is NULL makes `m.manager_id = e.id` evaluate to UNKNOWN, so that row is not kept — it simply is not a match. The subquery then either produced rows (EXISTS TRUE) or did not (EXISTS FALSE). There is no third outcome, so `NOT EXISTS` is always a definite TRUE or FALSE. The NULL is absorbed one level down, where it merely means "this row does not match", instead of poisoning the whole predicate. ## The fixes, strongest first 1. **Declare the column `NOT NULL`** if the data model allows it. This removes the hazard at the source and makes `NOT IN` unambiguous forever. 2. **Write `NOT EXISTS`** with the correlated comparison. This is the default habit worth building; it is correct whether or not the column is nullable. 3. **Filter inside the subquery**: `NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL)`. Correct, but it is a patch that a later edit can drop, and it hides the reason in a place readers skim. Note that wrapping the *outer* column, e.g. `COALESCE(e.id, -1) NOT IN (...)`, fixes nothing: the NULLs that break the predicate are on the subquery side. ## How this gets asked Interviewers show the query and ask for the output, then ask for the fix, then ask why `IN` did not break the same way. Answer with the `<> ALL` expansion, the UNKNOWN step, and the two-valuedness of EXISTS. Saying only "use NOT EXISTS, it's better" without the mechanism reads as memorised.
- Why does plain IN with a NULL in the subquery usually still behave as expected?`IN` is existential: `x = ANY (...)` is TRUE as soon as one comparison is TRUE, and TRUE outranks UNKNOWN. So `1 IN (1, 2, NULL)` is TRUE. Only the non-matching case degrades — `3 IN (1, 2, NULL)` is UNKNOWN instead of FALSE — and since WHERE discards UNKNOWN and FALSE alike, the visible result matches intuition. NOT IN has no such escape.
- Does adding COALESCE around the outer column fix a NOT IN over a nullable subquery?No. The UNKNOWN comparisons come from NULLs on the subquery side, not from the outer value, so coalescing the outer column changes nothing. Fix it where the NULLs live: rewrite as NOT EXISTS, add `WHERE col IS NOT NULL` inside the subquery, or make the column NOT NULL in the schema.
- If the subquery column is declared NOT NULL, is NOT IN then equivalent to NOT EXISTS?Semantically yes for a single-column comparison: with no NULLs possible, every comparison is TRUE or FALSE, so `<> ALL` is total and matches the anti-filter meaning of NOT EXISTS. The risk is durability — a later migration that drops the NOT NULL constraint silently re-arms the trap, which is why many teams standardise on NOT EXISTS regardless.
saying these in an interview costs you the question
- Says NOT IN just ignores NULLs in the subquery
- Claims the query errors instead of returning zero rows
- Fixes it by coalescing the outer column instead of the subquery
- Thinks NOT (UNKNOWN) evaluates to TRUE
- Says IN and NOT IN are symmetric with respect to NULL