Gotcha: PostgreSQL NULL in NOT IN queries

NOT IN query returns no results when the subquery or list contains NULL values.

NULL in NOT IN causes the entire condition to return UNKNOWN (effectively FALSE):

-- This returns NO RESULTS if any excluded_id is NULL!
SELECT * FROM users
WHERE id NOT IN (SELECT excluded_id FROM exclusions);
-- If excluded_id has any NULL, zero rows returned!

-- Fix 1: Filter NULLs
SELECT * FROM users
WHERE id NOT IN (
  SELECT excluded_id FROM exclusions WHERE excluded_id IS NOT NULL
);

-- Fix 2: Use NOT EXISTS (preferred - also faster)
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM exclusions e WHERE e.excluded_id = u.id
);

-- Fix 3: Use LEFT JOIN
SELECT u.* FROM users u
LEFT JOIN exclusions e ON e.excluded_id = u.id
WHERE e.excluded_id IS NULL;

Why it happens:

• 5 NOT IN (1, 2, NULL) = 5!=1 AND 5!=2 AND 5!=NULL
• 5 != NULL = UNKNOWN
• TRUE AND TRUE AND UNKNOWN = UNKNOWN
• Row is excluded from results

SQL uses three-valued logic (TRUE, FALSE, UNKNOWN). Any comparison with NULL yields UNKNOWN, which propagates through AND operations in NOT IN.

SQL queries filtering by exclusion lists that may contain NULLs