NOT IN vs NOT EXISTS in SQL: why a nullable column silently breaks NOT IN

A single NULL in a subquery makes NOT IN return zero rows, even when matching rows obviously exist. The query does not error and does not warn. It quietly returns an empty set, and the bug survives code review because the SQL reads correctly. NOT EXISTS does not have this behavior, which is why it is the safer default on any nullable column.

This has become a more common bug, not a rarer one, because AI coding assistants generate NOT IN subqueries by default and cannot see the schema detail that makes them fail. This post covers why the two operators diverge, what SQL three-valued logic does with a NULL comparison, why AI-generated SQL walks into it, and the three ways to fix it in PostgreSQL, SQL Server, Oracle and MySQL.

How NOT IN evaluates

NOT IN excludes rows by comparing a target column against a literal list or the rows a subquery returns:

SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM sales_history);

The operator implements negation by explicit comparison. For each row in the outer table, the SQL engine compares customer_id against every value the subquery produced, and keeps the row only when none of those comparisons is true.

That is the mechanism, and it is also the trap.

Why a nullable column silently breaks it

SQL uses three-valued logic: every comparison returns TRUE, FALSE, or UNKNOWN. Any comparison against NULL returns UNKNOWN, because NULL means no value recorded rather than a value to compare.

NOT IN expands to a chain of inequality tests joined by AND. So id NOT IN (1, 2, NULL) becomes id <> 1 AND id <> 2 AND id <> NULL. That last test returns UNKNOWN for every row. TRUE AND UNKNOWN is UNKNOWN, and a WHERE clause keeps a row only when its predicate is TRUE. So every row is discarded and the result set is empty, regardless of how many legitimate matches the table holds.

One NULL anywhere in the subquery is enough. This is why the failure is hard to spot: the query works correctly for months, someone inserts a row with a NULL in the joined column, and it starts returning nothing. Nothing in the application layer distinguishes no matching rows from the operator giving up.

Why AI coding assistants keep writing this bug

Ask Claude, Copilot, Cursor or any LLM-backed assistant to find customers with no sales history and you will usually get the NOT IN form. That is not a flaw in the model so much as an accurate reflection of its training data: NOT IN is the more common phrasing across tutorials, Stack Overflow answers and existing codebases, so it is the likeliest completion.

The problem is what the assistant cannot see. Nullability is a property of the schema, not of the query. Unless the DDL is in context, the model has no way to know whether sales_history.customer_id is declared NOT NULL. It generates SQL that is syntactically perfect, idiomatic, and correct for a non-nullable column, and silently wrong for a nullable one.

Three things make this worse than an ordinary generated-code defect:

• It passes review for the same reason a human-written version would. The reviewer reads the intent, the intent is right, and nothing on screen indicates a NULL is possible.
• Automated review tools miss it too. This is a data-dependent semantic bug, not a syntactic one. A linter cannot flag NOT IN (SELECT …) as wrong without resolving the referenced column nullability, and most review tooling does not carry schema context.
• It fails silently and late. Tests written against seed data with no NULLs pass. The query breaks in production the first time a real NULL lands, often months after the code shipped, and it breaks by returning nothing rather than by raising.

At SWARECO this is the version of the bug we now see most often in production code review: not a developer reaching for the wrong operator, but a generated query nobody had reason to question. The volume of SQL reaching main has gone up; the schema awareness behind it has not.

What to put in your agent rules

The fix is cheap and it is a project-level instruction, not a per-prompt one. In your CLAUDE.md, .cursorrules, or equivalent:

SQL: never use NOT IN with a subquery. Use NOT EXISTS. NOT IN returns zero rows if the subquery yields any NULL. Reserve NOT IN for hand-written literal lists.

Giving the assistant your schema DDL also helps, but it is the weaker fix, because it depends on the right table being in context on the right turn. A blanket rule does not.

How NOT EXISTS avoids it

NOT EXISTS asks a different question. It does not compare values, it tests whether a correlated subquery returns any rows at all:

SELECT c.* FROM customers c WHERE NOT EXISTS (SELECT 1 FROM sales_history s WHERE s.customer_id = c.customer_id);

The SELECT 1 is conventional and arbitrary; the projection inside an EXISTS subquery is never evaluated, only the presence or absence of rows. A row whose join key is NULL simply fails to match, so it contributes nothing rather than poisoning the whole predicate.

NULL affects one row's participation instead of the entire result set. That is the whole difference, and it is why NOT EXISTS is the null-safe form.

The three fixes

In order of how permanently they solve the problem:

1. Rewrite to NOT EXISTS. Correct regardless of what the data does later. This is the only fix that survives someone inserting a NULL next year.
2. Add a NOT NULL constraint on the subquery column. Makes the failure structurally impossible, but only where the column genuinely should never be null.
3. Filter explicitly with WHERE col IS NOT NULL inside the subquery. Correct, but it patches one query. The next NOT IN written against the same column has the same bug.

SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM sales_history WHERE customer_id IS NOT NULL);

COALESCE is sometimes suggested as a fourth option. It works, but it requires a sentinel value that can never appear in the data, and a sentinel that turns out to be reachable reintroduces the bug in a harder-to-find form.

Performance, and why it is the smaller reason

NOT EXISTS generally produces a better execution plan. Optimizers can implement it as an anti join, a hash anti join or nested loops, and stop scanning a correlated subquery as soon as one matching row is found. NOT IN against a nullable column cannot be optimized the same way, precisely because the engine has to account for the NULL semantics above, and often falls back to a full scan even when a usable index exists.

On modern PostgreSQL and SQL Server the gap narrows when the column is declared NOT NULL, since the optimizer can then prove the two forms equivalent and rewrite one into the other. Check with EXPLAIN ANALYZE rather than assuming.

Performance is the usual reason people cite for preferring NOT EXISTS. It is the less important one. A slow query announces itself. A query that silently returns nothing does not.

What to check in your own codebase

Grep for NOT IN (SELECT. For each hit, check whether the selected column is nullable. Every one that is, is a latent empty-result bug waiting on a single NULL insert. On a codebase with significant AI-assisted contribution, expect more hits than you would have guessed.

The rule worth adopting, for humans and assistants alike: use NOT EXISTS by default for subqueries, and reserve NOT IN for literal lists you wrote by hand, where you can see there is no NULL and no future insert can add one.

Frequently asked questions

Are NOT IN and NOT EXISTS interchangeable?

Only when the subquery column is guaranteed non-null. Otherwise they return different results for the same data: NOT IN returns zero rows, NOT EXISTS returns the correct set.

Why does ChatGPT or Copilot generate NOT IN instead of NOT EXISTS?

Because NOT IN is more frequent in the training corpus and reads more naturally for the stated intent. The model is not reasoning about nullability, it is completing the likeliest phrasing. Without the schema in context it has no signal that the column can be NULL.

Does this affect IN as well as NOT IN?

No. IN keeps a row when any comparison is TRUE, and an UNKNOWN from a NULL comparison does not prevent a different value from matching. Only the negated form breaks, because it requires every comparison to be TRUE.

Does it behave the same in Postgres, SQL Server, Oracle and MySQL?

Yes. Three-valued logic is ANSI SQL, not a vendor quirk. Oracle offers LNNVL() to handle unknown conditions explicitly, but the underlying NULL semantics are identical across all four.

What about LEFT JOIN and IS NULL?

The anti join pattern LEFT JOIN with WHERE right.key IS NULL is also null-safe and performs comparably. It is harder to read than NOT EXISTS and easier to break during refactoring, since removing the IS NULL silently changes the query meaning.