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

  1. customers LEFT JOIN orders keeps all 6 customers; Omar and the second Tom get NULLs for every order column.
  2. WHERE o.order_id IS NULL keeps only those NULL rows: the customers with no orders.
  3. order_id is the orders primary key, so it is NULL only when there was no match — never in a real order.
  4. NOT EXISTS version: for each customer, look for any order with their id; keep the customer if there is none.
  5. Avoid NOT IN for this when the inner list can hold a NULL — it returns nothing (Unit 4 shows why).

Key points

Common mistakes

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.