Finding what is missing: anti-joins
An anti-join returns rows from one table that have no match in another — written as LEFT JOIN … WHERE right.key IS NULL, or as WHERE NOT EXISTS (…).
Why it matters
"Customers who never ordered", "products that never sold", "employees with no manager on file" — finding what is absent is one of the most common interview questions and real tasks. You can't find it with WHERE alone, because the missing rows aren't there to filter.
The idea
Do a LEFT JOIN, so every left row is kept and unmatched ones get NULL on the right. Then keep only the unmatched ones: WHERE o.order_id IS NULL. Test a column that can never be NULL in a real match — the right table's primary key is perfect.
NOT EXISTS says the same thing more directly: "keep the customer if no order exists for them". Its opposite, the semi-join, keeps rows that do have a match, each only once: WHERE EXISTS (…) or WHERE id IN (…).
Checking a guest list against the sign-in sheet. Hold the list (left table) next to the sheet (right table); the names with an empty space next to them on the sheet are the people who didn't come.
With a list you just look for blanks. In SQL you must test the right column for the blank. If you test a column that might legitimately be NULL (like amount), you will wrongly report customers whose real order just has no amount.
Syntax
SELECT a.*
FROM table_a a
LEFT JOIN table_b b ON b.key = a.key
WHERE b.key IS NULL;How it works
- customers LEFT JOIN orders keeps all 6 customers; Omar and the second Tom get NULLs for every order column.
WHERE o.order_id IS NULLkeeps only those NULL rows: the customers with no orders.- order_id is the orders primary key, so it is NULL only when there was no match — never in a real order.
- NOT EXISTS version: for each customer, look for any order with their id; keep the customer if there is none.
- Avoid NOT IN for this when the inner list can hold a NULL — it returns nothing (Unit 4 shows why).
Key points
- Anti-join = rows with no match:
LEFT JOIN … WHERE right.pk IS NULL, orNOT EXISTS. - Semi-join = rows with at least one match, each once:
EXISTSorIN. - In the IS NULL test, use a column that is never NULL in a real match — the right table's primary key or join key.
- NOT EXISTS is the safest anti-join: it handles NULLs correctly, and optimisers run it efficiently.
- Don't use
NOT IN (subquery)if the subquery can return NULL.
Common mistakes
- Testing a nullable column (
WHERE o.amount IS NULL), which also returns Lena because order 106 has no amount. - Using INNER JOIN and wondering why the missing rows never appear.
- Using
NOT IN (SELECT dept_id FROM employees): Gus's NULL dept_id makes it return no rows at all.
Not true: NOT IN (subquery) is safe even when the subquery returns a NULL.
In fact: A single NULL in the list makes NOT IN unknown for every row, so nothing comes back. Use NOT EXISTS, which is safe with NULLs.
In the interview
To find rows with no match I use an anti-join: either a LEFT JOIN with WHERE the right table's key IS NULL, or NOT EXISTS with a correlated subquery. I prefer NOT EXISTS, because it states the intent directly and is NULL-safe. I avoid NOT IN against a subquery, since a single NULL in that list makes the whole condition unknown and returns nothing.
Part of Unit 3: Joins: combining tables in the free SQL course.