Marketing wanted a list.
Every customer
who had never placed an order.
The query was short
and read like English.
Select from customers
where id is not in
the customer ids from orders.
It had worked for two years.
On Monday
it returned zero rows.
Not fewer.
Zero.
Nobody had deleted a customer.
Nobody had touched the query.
What changed
was one row in orders.
The week before,
the shop started taking guest checkouts,
and a guest order
has no customer id.
The column holds a null.
That was enough.
Here is the mechanism.
NOT IN is shorthand
for a chain of comparisons.
The id is not one,
and it is not two,
and it is not null.
The last comparison
cannot be answered.
Is seven different from a value
nobody knows?
SQL says unknown.
Not true.
Not false.
Unknown.
True and unknown
is unknown.
And a WHERE clause
keeps a row only when the answer is true.
So every row gets unknown,
and every row is dropped.
One null on the inside
erases the whole result on the outside.
No error.
No warning.
An empty list
looks exactly like
a business where every customer has ordered.
This is standard SQL.
Postgres does it.
MySQL does it.
Oracle and SQL Server do it.
It is not a bug in any of them.
It is three valued logic,
working as designed,
inside a query
that was written in two valued English.
So stop writing NOT IN
against a column that can be null.
Write NOT EXISTS instead.
Where there is no order
whose customer id
equals this customer's id.
A null never equals anything,
so it never matches,
and it never poisons the rest.
If you keep NOT IN,
filter the nulls out of the subquery
and say why in the query.
Then go looking for
the columns that were never null
until last week.
Add one null to the test data
for each of them.
The query was correct
for every database
that had no nulls in it.
That database does not exist for long.
– Serguey Asael Shinder
Top comments (0)