Anti-joins — finding what's missing

Sometimes the answer you want is the absence of something. “Users who never ordered.” “Products that never sold.” “Accounts that didn’t log in last week.” That is an anti-join — the opposite of an inner join’s “only matches” rule: rows in A that have no counterpart in B.

In our little schema, Asha and Carlos have orders but Bo does not, so “users who never ordered” should return exactly Bo. SQL gives three ways to ask it — and one of them has a famous trap.

Idiom 1 — LEFT JOIN + IS NULL

Keep every user with a left join, then keep only the ones whose right side came back NULL — those are precisely the users with no order:

SELECT u.id, u.name, u.country
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE  o.id IS NULL;

Idiom 2 — NOT EXISTS (the one to default to)

NOT EXISTS (subquery) is true when the subquery finds nothing. It reads as the English question almost word for word, and it is NULL-safe:

SELECT u.id, u.name, u.country
FROM   users u
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

The subquery only checks “is there at least one?” — it never has to fetch the matching rows, so it can stop at the first one. Same answer, clean intent.

Idiom 3 — NOT IN, and its trap

NOT IN is the most literal translation, and it works fine as long as the inner list has no NULLs: WHERE id NOT IN (SELECT user_id FROM orders) also returns Bo. But let a single NULL into that inner list and it breaks completely — returning zero rows, even though plenty of users obviously are not in the orders list.

Practice