AND is true only if every part is true; one unknown part makes the whole unknown (unless another part is false, which makes it false). WHERE keeps only rows where the condition is true, so it discards unknown. For Chen, every row: discarded. For Asha, 1 <> 1 is false, so false — discarded as before. Nobody survives.

IN with a NULL in the list is mostly harmless: a match makes the OR true no matter what else is there. The non-matching rows are unknown rather than false, which you only notice once something negates them — and NOT IN is the negation.

NULL on the outer side

The trap has a mirror image. If the outer column is NULL:

-- products.category_id is NULL for unclassified products
SELECT name FROM products
WHERE category_id NOT IN (SELECT id FROM discontinued_categories);

For an unclassified product the test is NULL NOT IN (…), unknown whenever the list is non-empty — so the product is never returned, even though it is certainly not in a discontinued category. NOT EXISTS (… WHERE d.id = p.category_id) returns it, because no row can equal NULL and so no matching row exists. Neither behaviour is wrong in itself; they answer subtly different questions, and you should choose deliberately.

Three reliable anti joins

NOT EXISTS — the default choice:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
ORDER BY c.name;

It asks a yes-or-no question per customer — is there a matching order? — and a NULL customer_id simply never matches. No unknowns leak out.

NOT IN with the NULLs filtered out:

SELECT c.name
FROM customers c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders o WHERE o.customer_id IS NOT NULL)
ORDER BY c.name;

Correct, but it relies on everyone remembering the filter every time. Declaring the column NOT NULL in the schema removes the risk at the source — when the data model allows it.

LEFT JOIN … IS NULL:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.name;

Correct as long as the tested column is never NULL in a real match (the primary key is ideal).

All three agree on the outer-side NULL case too, for the same reason — they test for a matching row rather than compare against a list. MySQL 8.0.17+ plans NOT EXISTS and NOT IN as antijoins; PostgreSQL often plans NOT IN worse precisely because it must honour the NULL semantics. That is the second reason to default to NOT EXISTS.

The empty subquery

x NOT IN (empty) is true for every x, including NULL — an AND of zero comparisons is true. So "products not in any discontinued category" correctly returns every product when nothing is discontinued. The trouble starts only when the list is non-empty and contains a NULL (or the outer value is NULL).

A classic: classifying tree nodes

A table stores a tree as (id, p_id), with p_id NULL for the root. Label each node Root, Inner (has a parent and children) or Leaf (has a parent, no children). The tempting solution:

SELECT id,
       CASE
         WHEN p_id IS NULL THEN 'Root'
         WHEN id IN (SELECT p_id FROM tree) THEN 'Inner'
         ELSE 'Leaf'
       END AS type
FROM tree
ORDER BY id;

This one is correct, and it is instructive to see why: IN with the root's NULL p_id in the list still finds the matches, and for leaves the unknown result just falls through to ELSE. Now flip the test, as many people do:

WHEN id NOT IN (SELECT p_id FROM tree) THEN 'Leaf'
ELSE 'Inner'

The root's NULL p_id is in the list, so NOT IN is never true, and every leaf is labelled Inner. Same data, same intent, one negation — wrong answer. Writing the child test as EXISTS (SELECT 1 FROM tree c WHERE c.p_id = t.id) makes both versions correct.

Rules to remember

• NOT IN (subquery) is correct only if the subquery's column cannot be NULL and the outer value cannot be NULL.
• NOT EXISTS is correct regardless; use it by default for "has no match".
• <> ALL (subquery) is the same as NOT IN and has the same trap.
• The same three-valued logic explains why WHERE col <> 'x' skips rows where col is NULL.

Pitfalls

• NOT IN on a nullable column — the query empties the day the first NULL arrives.
• Forgetting the outer side: a NULL outer value is never returned by NOT IN.
• Assuming NOT (x IN …) behaves differently — it is the same expression.
• Negating a correct IN test into an incorrect NOT IN test while refactoring.
• Testing with data that has no NULLs and concluding the query is right.

Key takeaways

• A comparison with NULL is unknown; WHERE keeps only true.
• x NOT IN (…, NULL, …) is never true, so one NULL empties the result.
• NOT EXISTS (or LEFT JOIN … IS NULL) is the safe anti join; NOT IN needs WHERE col IS NOT NULL inside.
• IN survives NULLs for matching; its negation does not.
• NOT IN of an empty subquery is true for every row.