KITFORMA · EDITORIAL GUIDE
Why does NOT IN stop returning rows when the subquery contains NULL?
KitForma editorial guide. A NULL in the candidate set can make the NOT IN predicate unknown rather than true. This is a three-valued-logic issue, not necessarily missing data.
Step-by-step guidance
A NULL in the candidate set can make the NOT IN predicate unknown rather than true. This is a three-valued-logic issue, not necessarily missing data.
For an anti-join, use a correlated
NOT EXISTS with the intended equality condition. Alternatively exclude nulls only if the data contract truly allows that transformation. Test an empty subquery, one match, one nonmatch and a NULL. Decide separately how a NULL on the outer row should behave. Rewriting the query without specifying that case can exchange one subtle bug for another.
Minimal example:
-- Portable example; verified with SQLite.
WITH candidates(id) AS (VALUES (1), (2)),
excluded(id) AS (VALUES (1), (NULL))
SELECT c.id FROM candidates c
WHERE NOT EXISTS (SELECT 1 FROM excluded e WHERE e.id = c.id);
-- Expected: 2Sources and verification
Sources checked:
Scope: This editorial guide is based on the cited sources and tool behavior. A forum question or a query observed for our site does not establish market search volume, low competition, guaranteed rankings or inadequate answers elsewhere.
This starter guide was prepared by KitForma with AI assistance. It is not presented as a real member question or an independent user review. Check the sources and the result with your own file; report corrections in the discussion.