'Which customers have never ordered?' is one of the most valuable questions in analytics — those are the people your re-engagement campaign targets. An INNER JOIN literally cannot answer it: it drops exactly the rows you want. LEFT JOIN keeps them, and by the end of this lesson you'll know how to find what's missing, not only what matched.
What you'll learn
What
A LEFT JOIN (also called LEFT OUTER JOIN) returns every row from the left table. Where a right-table match exists, the columns are filled in as in an INNER JOIN; where no match exists, the right table's columns are filled with NULL. RIGHT JOIN is the mirror image: every row from the right table survives.
Why
Real datasets are full of legitimate non-matches: customers without orders, products never sold, employees with no manager, events with no signup. Outer joins let you keep those rows and even search for them — the anti-join pattern (LEFT JOIN ... WHERE right key IS NULL) is the standard tool for finding missing data.
Where it's used
Churn and retention analysis, data-quality audits (which orders reference deleted products?), funnel reports where later stages may be empty, and any dashboard that must show 'zero' rather than omit a row entirely.
Where this runs in production
Retention teams LEFT JOIN the subscribers table to viewing events for the week. Subscribers with NULL on the events side watched nothing — the exact cohort that receives 'here's what's new' nudges before they churn.
Marketplace analytics LEFT JOINs the product catalog to order lines to surface listings with no sales in 90 days. INNER JOIN would hide these items; the NULL-filled rows are what drives delisting and discount decisions.
Pipeline reports LEFT JOIN leads to opportunities on lead_id. NULL opportunity columns mark leads that stalled — sales ops segments them by source to find which campaigns generate contacts that never turn into deals.
A wedding planner prints the invitation list (left table) and matches it against RSVPs received (right table). The final seating report lists every invited guest: those who replied get their meal choice filled in; those who never replied still appear, with the meal column blank. Nobody invited is dropped from the report just because they went quiet. The blank cell is the join's NULL. Limitation of the analogy: an uninvited person who somehow RSVP'd (a right row with no left match) is dropped by a LEFT JOIN — keeping those too is what FULL OUTER JOIN does.
LEFT JOIN preserves all rows of the table written before the JOIN keyword. RIGHT JOIN preserves all rows of the table written after it. Both are 'outer' joins — the word OUTER is optional (LEFT JOIN and LEFT OUTER JOIN are identical) — because they include rows outside the matched set.
Filtering a right-table column in WHERE destroys the LEFT JOIN's purpose. In WHERE o.status = 'shipped', unmatched customers have o.status = NULL, NULL = 'shipped' is not true, and those rows are eliminated — your LEFT JOIN now behaves like an INNER JOIN. To filter the right side while keeping unmatched left rows, move the condition into ON: LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'shipped'. This ON-versus-WHERE distinction is the single most common outer-join bug in production SQL.
-- Anti-join: customers with NO orders SELECT c.customer_id, c.name FROM customers AS c LEFT JOIN orders AS o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL;
The anti-join pattern: LEFT JOIN, then keep only the rows where the right side failed to match. We test o.order_id — orders' primary key — because a primary key is never NULL in a genuine matched row, so IS NULL cleanly means 'no order existed'. This is the idiomatic way to find missing counterparts, and interviewers ask for it constantly.
A LEFT JOIN returns at least one row per left-table row — never fewer. Matched left rows appear once per match (one-to-many still fans out, exactly as with INNER JOIN); unmatched left rows appear exactly once, NULL-filled. So: result rows = matched pairs + unmatched left rows.
The same two tables, three join types. Click each node to see exactly which rows appear and where the NULLs come from.
Select a type to see its full definition, operations, and data science usage.
The bookshop from last lesson: 5 customers, 5 orders. Emma has never ordered. Marketing wants the full customer list with order ids where they exist.
Identical shape to an INNER JOIN — only the keyword changes. Row-count reasoning: Alice matches 3 orders (3 rows), Ben 1, Chloe 1; that's 5 matched pairs. Emma matches nothing, so LEFT JOIN adds her back once with order_id = NULL. Dev? He isn't in this smaller dataset. Total: 5 + 1 = 6 rows — never fewer rows than the left table has.
name | order_id -------+---------- Alice | 101 Alice | 104 Alice | 105 Ben | 102 Chloe | 103 Emma | NULL (6 rows)
Your task
products has product_id, name, category. order_items has item_id, order_id, product_id, quantity. Write a query listing EVERY product with its total units sold as units_sold — products never sold must show 0. Sort by units_sold ascending so the dead stock rises to the top.
name | units_sold -----------------+------------ Desk Lamp | 0 Yoga Mat | 0 USB-C Cable | 3 Coffee Grinder | 5 Noise-Cancelling Headphones | 8 (5 rows)
Write your solution in the editor on the right, then hit Run.
In a LEFT JOIN from customers to orders, what appears in the order columns for a customer with no orders?
Which query is equivalent to `orders RIGHT JOIN customers ON ...`?
customers has 8 rows; 6 of them have orders (14 orders total among them). How many rows does `customers LEFT JOIN orders ON customer_id` return?
Why, and what is the fix?
What is wrong?
Using customers (customer_id, name) and orders (order_id, customer_id, order_date), write an anti-join returning the name of every customer who has NO orders at all. Return names only, sorted alphabetically.
When would you choose a LEFT JOIN over an INNER JOIN? Give a concrete example.
I choose a LEFT JOIN whenever the left table defines the population I must report on in full, and the right table only enriches it. A concrete example: a retention dashboard listing every customer with their order count. With an INNER JOIN, customers who never ordered vanish — and they are the most important rows for retention work. With a LEFT JOIN they survive as NULL-filled rows, and COUNT(o.order_id) turns those NULLs into honest zeros. The decision rule I use is to ask 'should a non-match appear in the output?': if yes, outer join; if a non-match is meaningless or invalid for the question, inner join. I also flag that the choice is a business decision that changes totals, so it belongs in the analysis write-up, not buried in the SQL.
Explain why putting a right-table filter in WHERE can break a LEFT JOIN, and where the filter should go instead.
The LEFT JOIN runs first and fills right-table columns with NULL for unmatched left rows. WHERE then evaluates its condition on every result row — and any ordinary comparison against NULL, like o.status = 'shipped', evaluates to unknown, which WHERE treats as failure. So exactly the NULL-filled rows the outer join worked to preserve get discarded, and the query silently behaves like an INNER JOIN. The fix is to move right-table filters into the ON clause: LEFT JOIN orders o ON c.id = o.customer_id AND o.status = 'shipped'. That redefines what counts as a match, so customers with no shipped orders remain in the result as NULL rows. Filters on the left table, by contrast, belong in WHERE, since left rows are never NULL-filled by their own join. One legitimate exception exists: when the WHERE condition is IS NULL on a right-side key, discarding matches is the point — that is the anti-join pattern.
How would you find rows in table A that have no corresponding row in table B, and what NULL subtlety must you watch for?
The standard pattern is an anti-join: LEFT JOIN A to B on the key, then WHERE B.key IS NULL keeps only the A rows that found no partner. The subtlety is choosing which B column to test. It must be a column that can never be NULL in a genuine B row — ideally B's primary key or the join key itself. If you test a nullable column like B.shipped_date, a real matched row that happens to have a NULL shipped date would masquerade as a non-match, corrupting the result. A second subtlety is writing IS NULL rather than = NULL, since = NULL is never true under SQL's three-valued logic. Alternatives to the anti-join include NOT EXISTS with a correlated subquery — which many engines optimise identically and which sidesteps the column-choice trap — and NOT IN, which I avoid because a single NULL in the subquery's result makes NOT IN return no rows at all.
Common Mistakes to Avoid
1) Filtering a right-table column in WHERE after a LEFT JOIN — NULL-filled rows fail the comparison and the join silently degrades to INNER; put right-side filters in ON. 2) Writing = NULL instead of IS NULL in the anti-join — = NULL is never true, so the filter matches nothing. 3) Using COUNT(*) instead of COUNT(right_column) after an outer join — orderless customers get counted as 1 instead of 0. 4) Testing a nullable right-table column for the anti-join — a matched row with a legitimately NULL value looks like a non-match; always test the right table's key. 5) Assuming LEFT JOIN cannot change row counts — matched left rows still fan out one-to-many; only unmatched rows are guaranteed to appear exactly once.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: what does the NULL in a LEFT JOIN result actually mean?' • 'Quiz me: give me table sizes and match counts, and I'll predict LEFT JOIN row counts.' • 'Show me the ON vs WHERE bug with a tiny dataset and walk through why the rows vanish.' • 'Explain the anti-join pattern with a fresh analogy, then ask me to write one.' • 'Interview mode: ask me when I'd use LEFT JOIN vs NOT EXISTS and grade my answer.'
Glossary
LEFT JOIN (LEFT OUTER JOIN) — returns all left-table rows; unmatched ones get NULL in right-table columns. RIGHT JOIN — the mirror: all right-table rows survive. Outer join — any join that preserves unmatched rows from at least one side. NULL fill — the structural NULLs an outer join places in columns of the missing side. Unmatched row — a row whose key finds no partner under the ON condition. Anti-join — LEFT JOIN + WHERE right_key IS NULL: rows with no counterpart. Semi-join — returning left rows that DO have a match, without duplicating them (often via EXISTS). Preserved side — the table whose rows are guaranteed to appear (left for LEFT JOIN). Three-valued logic — SQL's true/false/unknown system; comparisons with NULL yield unknown. COALESCE — returns its first non-NULL argument; used to turn join NULLs into displayable defaults like 0.
Recommended Resources
• Docs: PostgreSQL manual, 'Joined Tables' under SELECT — the precise semantics of LEFT/RIGHT/FULL and of conditions in ON. • Read: 'The three-valued logic of SQL' (any solid treatment of NULL comparisons) — it explains every 'my WHERE ate my rows' bug you will ever hit. • Practice: take the customers/orders tables from this lesson, write the ON-filter and WHERE-filter versions of the same query, and diff the outputs until the difference feels obvious. • Next in DSM: FULL OUTER JOIN — keeping the unmatched rows from BOTH sides at once, the tool for reconciling two systems that should agree but don't.
Recap
✓ LEFT JOIN keeps every left-table row: matched rows pair up as usual, unmatched rows appear once with NULL in all right-table columns. ✓ RIGHT JOIN mirrors LEFT JOIN; swap the table order and you never need it — and some engines (old SQLite) historically lacked it. ✓ Result rows = matched pairs + unmatched left rows; one-to-many matches still fan out. ✓ Right-table filters belong in ON, not WHERE — a WHERE filter on the right side silently turns your outer join into an inner one. ✓ The anti-join (LEFT JOIN + WHERE right_key IS NULL) finds rows with no counterpart; always test a non-nullable key column. ✓ COUNT(right_column) — not COUNT(*) — gives honest zeros for unmatched rows; COALESCE turns NULL sums into 0. Next up: FULL OUTER JOIN. LEFT JOIN chose a side to preserve — next you'll keep the unmatched rows from both tables simultaneously, the pattern behind every 'reconcile system A against system B' audit.
Run your code to see the output here.