Anti-join

You meet it when#

"Which customers never ordered?" "Which orders have no payment?" "Which dimension keys are orphaned?" Any question whose answer is absence.

The trap#

select * from orders o
where o.customer_id not in (select customer_id from customers);

If customers.customer_id contains one null, this returns zero rows. Always. Silently.

The reason is three-valued logic: not in (1, 2, null) evaluates to NOT (x=1 OR x=2 OR x=null). That inner x = null is unknown, so the whole expression is unknown rather than true, and unknown rows are not returned.

It is not a rare edge case. Nullable columns in subqueries are the normal state of a warehouse.

The pattern#

select o.*
from orders o
where not exists (
    select 1 from customers c where c.customer_id = o.customer_id
);

not exists handles nulls correctly, short-circuits on the first match, and optimises well everywhere.

The other correct form#

A left join filtered to the misses. Equivalent, and useful when you want columns from the right side of the matching rows in the same query:

select o.*
from orders o
left join customers c on c.customer_id = o.customer_id
where c.customer_id is null;

This is the one legitimate use of WHERE <right table> IS NULL after a left join. Everywhere else it is the accidental inner join bug.

Related NULL rules worth memorising#

select null = null;              -- null, not true
select null <> 'a';              -- null, not true
select 1 in (2, null);           -- null → row not returned
where old.city is distinct from new.city   -- true when exactly one side is null

is distinct from is the one people do not know and need most: it is <> with sane null handling, and it is what change detection in a slowly changing dimension requires.