SQL, data modeling, data quality, EDA, visualization · insight-0042

Use NOT EXISTS instead of NOT IN when the subquery column can contain NULL

Situation
A query finds customers who never booked with WHERE customer_id NOT IN (SELECT customer_id FROM bookings), and returns zero rows.
Action
Write anti-joins as NOT EXISTS with a correlated condition, or LEFT JOIN and filter on the right key IS NULL. Add a NOT NULL constraint or filter in the subquery when you keep NOT IN.
Reason
Under three-valued logic, "x differs from every value" cannot be true if one value is unknown.
Boundary
Churn lists, "never purchased" segments, orphan checks and exclusion lists.

Symptom or risk

One NULL in the subquery makes NOT IN evaluate to NULL for every non-matching row, so the result is empty or wrong without an error.

Subqueries on columns that are declared NOT NULL behave as expected with NOT IN.

NOT EXISTS is more verbose; query planners generally handle it well.

Testing on clean development data with no NULLs, so the bug appears only in production.

If no right-hand value equals the left value and at least one right-hand row is NULL, NOT IN returns NULL, not true.
Subquery Expressions (PostgreSQL 18 documentation, section 9.24)