P05 · SQL & Querying
SQL JOINs, explained — and the LEFT that goes INNER
How SQL joins decide which rows survive — and the classic trap where a filter turns your LEFT JOIN back into an INNER one and drops rows.
Start with the basics
A join combines two tables by matching rows — usually on a shared key, like a customer id that appears in both an orders table and a customers table.
An inner join keeps only the rows that match on both sides. An outer join (LEFT is the common one) keeps every row from one side even when the other side has no match, filling the missing columns with nothing.
That choice — keep the unmatched rows or drop them — is the whole game. It decides which rows even exist in your result, and a report can look fine while quietly missing the rows it was built to show.
One that works
| customer | order_ids |
|---|---|
| Ada | 1, 2 |
| Bo | (none) |
SELECT c.name, COUNT(o.customer_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY c.name;| name | orders |
|---|---|
| Ada | 2 |
| Bo | 0 |
The LEFT JOIN keeps Bo even with no orders — the count reads 0, not missing.
A join matches rows by key — and an outer join keeps the rows that don't match, filling the gaps with nothing.
When to use it
- Reach for a join whenever the data you need lives in two tables — orders and the customers who placed them.
- Use an inner join when you only want matched rows; a LEFT join when you want to keep every row from one side.
- If you are looking things up in a loop in your app, that is usually a join.
That one word — keeps — is exactly what a stray filter throws away. Put a condition on the outer-joined table in the wrong place, and every unmatched row it was keeping disappears. Here is the day that bites.
See it happen
The LEFT JOIN that quietly turns INNER
You want every customer with their 2024 order count — including customers with none. A recent-date filter in the wrong place drops exactly those customers.
| id | name |
|---|---|
| 1 | Ada |
| 2 | Bo |
| customer_id | order_date |
|---|---|
| 1 | 2024-02-01 |
| 1 | 2023-12-15 |
date filter in WHERE — drops Bo
SELECT c.name, COUNT(o.customer_id) AS n
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= DATE '2024-01-01'
GROUP BY c.name;| name | n |
|---|---|
| Ada | 1 |
date filter in ON — keeps Bo
SELECT c.name, COUNT(o.customer_id) AS n
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.order_date >= DATE '2024-01-01'
GROUP BY c.name;| name | n |
|---|---|
| Ada | 1 |
| Bo | 0 |
The “all customers incl. zero orders” report quietly drops every zero-order customer — the rows it exists to show.
Walk the full example in the unit →The rest of the family
More ways a join drops the wrong rows
A missing value in a join key never equals anything, so those rows quietly drop out of an inner join.
A LEFT join with an is-missing test, NOT EXISTS, and NOT IN all find rows with no match, but NOT IN carries the NULL trap; NOT EXISTS is the safe habit.
A condition in ON filters the join itself; the same condition in WHERE filters the finished result — different meaning in an outer join.
To relate rows within one table (employee to manager) you join it to itself with two distinct aliases and a condition that is not self-matching.
A full outer join keeps the unmatched rows from both tables, which makes it the tool for reconciling two lists against each other.
Self-check
How to spot it in your own SQL
- A report meant to include every row (all customers, even with zero orders) is missing some.
- You added a filter on the joined table and the row count dropped.
- A join key can be missing on one or both sides.
- You are trying to list rows that exist in one table but not another.
What people miss
Joins are where most real reporting bugs live, because the wrong join quietly changes which rows exist at all — and the report still runs, still looks plausible, just missing the customers it was built to show.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
Why did my LEFT JOIN drop rows?
A filter on the right-hand table in the WHERE clause removes the kept unmatched rows (the ones with nothing on the right), which turns your LEFT JOIN back into an INNER one.
Move that condition into the join's ON clause so the unmatched rows survive.
from the unit →Why do rows with a null key vanish from my join?
A missing join key never matches anything — not even another missing key — so those rows silently drop out of an inner join.
Decide deliberately whether they should be included, and handle the missing key explicitly if so.
from the unit →What's the safe way to find rows with no match?
Use NOT EXISTS, or a LEFT join keeping only the rows whose right side is missing. Both stay correct even when the other table has missing values.
Avoid NOT IN against a column that can be blank — it carries the same NULL trap as basic filtering.
from the unit →Does my date filter go in ON or WHERE?
In an outer join they mean different things: a condition in ON filters what gets joined, while the same condition in WHERE filters the final result — and the WHERE version can drop your kept rows.
For a LEFT join where you want to keep every left row, put the right-table condition in ON.
from the unit →That’s the free taste