Why doesn't column != 'cancelled' just mean everything except cancelled?

It means everything except cancelled among rows where the column has a value at all. A row where that column is NULL isn't 'not cancelled' from SQL's perspective — comparing anything to NULL, including with !=, produces NULL (neither true nor false), so the row gets excluded from the result rather than included.

Is NOT IN always dangerous, or only sometimes?

Only when the list or subquery it's checking against can contain a NULL. NOT IN (1, 2, 3) is fine. NOT IN (SELECT excluded_id FROM some_table) is risky if excluded_id can be NULL for even one row — that single NULL silently makes the entire NOT IN return no rows at all, not just skip the NULL entry.

How do I know if this is happening to me right now?

If a NOT IN subquery-based filter returns suspiciously few rows (sometimes zero), check whether the column being checked against can contain NULL. A quick COUNT(*) WHERE that column IS NULL on the relevant table tells you immediately.

What's the actual fix?

For a straightforward column comparison, add an explicit IS NULL check: WHERE status != 'cancelled' OR status IS NULL (if NULL should count as included). For NOT IN, prefer NOT EXISTS or filter NULLs out of the subquery explicitly, since NOT EXISTS doesn't have this failure mode.

Does this affect COUNT and other aggregates too?

Yes, in a related way — aggregates like COUNT(column) skip NULL values in that column entirely, while COUNT(*) counts every row regardless. Mixing the two without meaning to is a common source of a count that's technically correct but not measuring what you assumed.

SQL NULL Filter Missing Rows | NOT IN Bug

You asked for "orders that aren't cancelled" or "customers without a plan assigned." The count came back lower than you expected — sometimes suspiciously low, sometimes zero — and every row you spot-checked by eye clearly should have matched.

The likely cause isn't a wrong filter value. It's NULL, and how it interacts with comparison operators in ways that don't match how most people intuitively read the query.

NULL isn't "empty" — it's "unknown," and that changes the logic

In everyday language, "not cancelled" should include both explicitly non-cancelled rows and rows where the status was never set at all. SQL doesn't see it that way. NULL represents unknown, not empty or false, and comparing anything to an unknown value — with =, !=, <, >, doesn't matter which — produces another unknown, not true or false.

A WHERE clause only keeps rows where the condition evaluates to true. A row where status != 'cancelled' evaluates to unknown (because status is NULL) gets filtered out right alongside the actual cancelled rows — even though intuitively, a NULL status is about as "not cancelled" as you can get.

The `NOT IN` version is worse, because it can silently kill the whole query

This gets more dangerous with NOT IN against a subquery:

SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM churned_customers)

If even one row in churned_customers.customer_id is NULL, this doesn't just skip that one row — the entire NOT IN clause can return zero rows for the whole query, because the comparison logic treats "is this id in a list that includes an unknown value" as itself unknown for every candidate row. This is one of the more counterintuitive traps in SQL: a single NULL somewhere in a subquery silently breaks the exclusion for everything, and there's no error to flag it.

Why the AI writes it this way without noticing

!= and NOT IN read as natural, correct English translations of "not cancelled" and "not in that list" — and they compile and run without any error. Nothing about the query looks wrong, and for a table where the relevant column happens to never be NULL, it behaves exactly as expected. The bug only appears once NULL enters that specific column, which the AI has no way to know about unless it actually checked (the same discipline that fixes wrong column names — inspect before assuming).

How to catch it

If a filter that should clearly match "most rows minus a few" comes back near-empty or lower than expected, check whether the relevant column allows NULL and whether any rows currently have it:

SELECT COUNT(*) FROM orders WHERE status IS NULL;

If that's non-zero and your filter was status != 'cancelled', you've found it.

The fix

For a direct column comparison, decide explicitly whether NULL should count as a match, and say so:

WHERE status != 'cancelled' OR status IS NULL

For NOT IN against a subquery, NOT EXISTS is the safer default — it doesn't have the same all-or-nothing failure mode:

SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM churned_customers ch WHERE ch.customer_id = c.id
)

The prompt that heads this off

Before filtering with != or NOT IN, check whether the column involved
allows NULL values, and tell me if it does. If NULL rows should be
included in the result, add an explicit "OR column IS NULL". For
NOT IN against a subquery, use NOT EXISTS instead, since a NULL in
the subquery can silently return zero rows with NOT IN.

The short version

!= and NOT IN don't mean "everything else" the moment NULL is involved — they mean "everything else that isn't unknown," which quietly drops exactly the rows a plain-English reading would expect to keep. It's not a wrong query in the sense of a typo; it's SQL's three-valued logic doing something most people never learned to expect.

Read-only MCP gateway: mcpserver.design.

Related reading: