LEFT JOIN ... WHERE ... IS NULL
Concepts covered: Outer Joins, Null Handling, Filtering
A LEFT JOIN keeps every row from the table on its left and attaches a partner from the table on its right wherever the ON condition is true. Where a left row has 2 partners it appears twice, once per partner. Where a left row has no partner it still appears once, with every column from the right table set to NULL. That last rule is the whole anti-join: the join manufactures a row for Carol that no inner join would produce, and the NULLs on it are the mark that nothing matched.
What the join produces before the WHERE
| c.customer_id | c.name | o.order_id | o.customer_id |
|---|---|---|---|
| 1 | Alice | 101 | 1 |
| 2 | Bob | 102 | 2 |
| 1 | Alice | 103 | 1 |
| 3 | Dana | 104 | 3 |
| 5 | Carol | NULL | NULL |
The join turns 4 customers into 5 rows. Alice is doubled because 2 orders match her, Bob and Dana appear once, and Carol appears once with NULL in both order columns. The join has lost nobody, which is the property that makes the filter possible: every customer is present, and the ones with no orders are the ones whose right-side columns are empty. The WHERE now has something to test.
The query, clause by clause
-- Customers who have never placed an order, LEFT JOIN form
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;| customer_id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Dana |
| 5 | Carol |
| order_id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 2 |
| 103 | 1 |
| 104 | 3 |
Watch the order of evaluation. The engine builds the 5 joined rows first, then runs the WHERE over them, and o.customer_id IS NULL is true for exactly 1 of the 5: Carol's. Alice's 2 rows carry 1 in o.customer_id, Bob's carries 2, Dana's carries 3, and all 4 are dropped. The result is 1 row, (5, Carol), with the columns of customers only. The SELECT list reads from c, since any column read from o on a surviving row would be NULL by construction.
| Clause | What it does |
|---|---|
| SELECT c.customer_id, c.name | Reads output columns from the kept table only |
| FROM customers c | The kept table: every row here is a candidate |
| LEFT JOIN orders o | The excluding table, attached without dropping any customer |
| ON o.customer_id = c.customer_id | The key that makes a right row a partner |
| WHERE o.customer_id IS NULL | Keeps the candidates whose partner never arrived |
Every engine you will be interviewed on accepts this form unchanged. LEFT OUTER JOIN is the same keyword with OUTER written out, and the result is identical. The only thing that changes between dialects is the quoting of names, so the 6 lines you rehearse are the 6 lines you type.
Why LEFT and not INNER
An INNER JOIN on the same ON condition returns 4 rows: Alice twice, Bob, Dana. Carol is gone before the WHERE runs, because an inner join emits a row only for pairs that satisfy the condition, and no pair contains her. A WHERE o.customer_id IS NULL on that result tests 4 rows that all carry a real id and returns nothing. The LEFT keyword keeps the unmatched row alive long enough for the filter to see it, and that is the sentence to have ready when the interviewer asks why you wrote it.
About this lesson
This section is part of the Anti-Join: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.