SQL NOT IN With NULL: Diagnose an Unexpected Empty Result

The practice task

Find fictional membership IDs absent from an exclusion list. The exclusion list contains one known ID and one missing value. Compare raw NOT IN with a version that removes unknown comparison values.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE members(id INTEGER);
CREATE TABLE exclusions(id INTEGER);
INSERT INTO members VALUES (1),(2),(3);
INSERT INTO exclusions VALUES (2),(NULL);

SELECT 'raw NOT IN' AS method, COUNT(*) AS remaining FROM members WHERE id NOT IN (SELECT id FROM exclusions)
UNION ALL
SELECT 'known exclusions', COUNT(*) FROM members WHERE id NOT IN (SELECT id FROM exclusions WHERE id IS NOT NULL)
ORDER BY method;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The raw comparison yields zero retained rows. ID 2 is explicitly excluded, while the unknown exclusion prevents the other IDs from establishing that they differ from every comparison value. Removing the unknown comparison row retains IDs 1 and 3.

Filtering the exclusion list is a policy decision. It means an unknown exclusion ID does not block a known member. If a missing value represents an incomplete control list that should stop processing, silently removing it would hide the problem instead of resolving it.

A correlated NOT EXISTS query is another way to express absence of a matching known key. It does not make unknown outer IDs meaningful by itself. Decide whether a missing member ID belongs in your result and test that case separately.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Inspect NULL values on both sides of an exclusion.
• Do not replace unknown IDs with a magic number that could be real.
• Record whether unknown exclusions should be ignored, flagged or stop the report.