NOT IN unexpectedly returns no rows

Illustrative lab: choose identifiers not present in an exclusion list that contains a null. This is a learning case, not a reported customer incident.

Observable symptoms

An exclusion query returns no identifiers even though some are not explicitly listed.

Likely cause

A null in the right-hand set introduces UNKNOWN comparisons. WHERE keeps true conditions, not unknown conditions.

Diagnosis steps

1. Inspect the exclusion set for nulls.
2. Define whether a null left-hand identifier should be eligible.
3. Test an anti-join using NOT EXISTS against the same small fixture.

Example

WITH candidates AS (SELECT 1 id FROM dual UNION ALL SELECT 2 FROM dual),
excluded AS (SELECT 1 id FROM dual UNION ALL SELECT NULL FROM dual)
SELECT c.id FROM candidates c
WHERE NOT EXISTS (SELECT 1 FROM excluded e WHERE e.id = c.id);

Expected result

ID
2

Code walkthrough

• The candidates CTE contains ids 1 and 2.
• The excluded CTE deliberately contains id 1 and a null.
• The correlated subquery finds a matching row only for id 1. NOT EXISTS therefore keeps id 2.

Solution and boundaries

Use an anti-existence predicate when that matches the requirement. If null candidate identifiers must be excluded, add c.id IS NOT NULL explicitly. Do not treat null as a magic identifier value.

Prevention and monitoring

Validate nullable key contracts and include null-bearing fixtures in regression tests.

Interview question

How do NOT EXISTS and NOT IN differ when nulls are present?

Official reference · Related technical guide