All 22 modules are open from the start — nothing here is
locked, and nothing costs anything. Sign in so your progress, titles and
credentials stay with you, on every device you use.
Free forever, with your Google account. No password, no payment.
The last module ended on a warning: an inner join deletes any row with no
partner, and never mentions it. This module is the answer — and the two
mistakes that undo it. Both produce a number that looks entirely reasonable,
and neither raises an error.
Ready?
1
LEFT JOIN Keeps the Left Table Whole
An inner join returns pairs. If a row on one side has no partner on the
other, there is no pair, so there is no row — and the result never
mentions the absence.
LEFT JOIN keeps every row of the left table
regardless. Where it found a partner, the result looks exactly
like an inner join. Where it did not, the row still comes through, with
NULL filled in for every column of the right-hand table.
SELECT u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id;
"Left" is positional and nothing more: it is the table named first, in
FROM. The ON condition is unchanged. The only
edit is the word.
users LEFT JOIN orders — rows 15–20 of 26, ordered by user_id
user_id
name
order_id
amount
6
Mei Lin
21
29.00
7
Omar Haddad
NULL
NULL
8
Sofia Rossi
11
147.00
8
Sofia Rossi
12
38.00
9
Tom Becker
13
12.00
9
Tom Becker
22
9.00
26 rows in total, not 24. Omar Haddad — the shaded row — never ordered,
and is present, named, and honestly empty rather than absent. Jonas
Berg is the other one, at row 24. An inner join returns 24 rows and
neither of them.
Those NULLs were not stored anywhere. Nothing in orders is
NULL; the join manufactured them, because it had a user to report and
nothing to report about them. That distinction matters in a moment.
LEFT OUTER JOIN is the same thing
The full name is LEFT OUTER JOIN. The OUTER
is optional, carries no meaning, and is almost never typed — in
the same way that plain JOIN already means
INNER JOIN. Four spellings, two joins.
Quick check
A table of 500 customers is LEFT JOINed to orders. 300 customers have ordered, placing 1,200 orders between them. How many rows come back?
2
The Anti-Join
Keeping the unmatched rows is useful. Isolating them is what
gets asked for in the job.
Which customers have never bought anything. Which products have never
sold. Which accounts have no owner, which invoices have no payment,
which employees filed no timesheet. Every one of those is a question
about absence, and none of them can be answered by an
inner join, because the answer is exactly the set of rows an inner join
removes.
Once a LEFT JOIN has kept the unmatched rows, they are
identifiable: they are the ones the join padded with NULL.
SELECT u.user_id, u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
WHERE o.order_id IS NULL;
That is the anti-join. Join everything, then keep only
the rows where the join found nothing. Three lines, and it is worth
knowing by shape rather than deriving each time.
Test the primary key, not any column
Use the right table's primary key in the
IS NULL test. A key is NOT NULL in its own
table, so a NULL in that column can only be padding from the
join. Test a nullable column instead — say
subscriptions.cancelled_on — and you cannot tell "no
matching row" apart from "a matching row whose value is genuinely
empty", which are different answers to different questions.
And it has to be IS NULL. = NULL is never
true, not even against another NULL — the three-valued logic from
module 1-05 applies here unchanged. A WHERE o.order_id = NULL
returns zero rows, cheerfully, forever.
Quick check
You LEFT JOIN users to subscriptions and write WHERE s.cancelled_on IS NULL to find users with no subscription. What do you actually get?
3
The Two Ways a LEFT JOIN Lies to You
Both of these run. Both return a number a reasonable person would
believe. Neither is correct, and between them they account for most
wrong outer-join queries ever shipped.
Trap one: COUNT(*) counts the padding
A user with no orders still has a row after a LEFT JOIN —
the padded one. COUNT(*) counts rows, and that is a row. So
the user who bought nothing is reported as having placed
one order.
COUNT(*) -- 1 for a user who has never ordered
COUNT(o.order_id) -- 0 which is the truth
COUNT(column) skips NULLs — the same rule as module 2-01,
arriving somewhere it suddenly matters a great deal. Counting a column
from the right-hand table is what makes an empty group score
zero.
The same applies to the other aggregates, differently:
SUM(o.amount) over nothing but padding is
NULL, not 0. Consistent — there were no values
to add, and SQL will not invent one — but a report full of blank totals
is usually not what was wanted, and anything computed from that blank
goes NULL too. COALESCE(SUM(o.amount), 0) is the fix.
Trap two: a WHERE on the right table undoes the join
This one is subtler, and it catches people who already know the first.
WHERE runs after the join has built its rows,
padding included. A padded row has o.status = NULL. NULL is
not 'paid' — and, crucially, it is not "not paid" either;
it is unknown, and WHERE keeps only what is definitely
true. So every padded row is discarded, and the
LEFT JOIN has become an inner join with nothing to
indicate it.
Silently inner
The filter in WHERE
LEFT JOIN orders o ON o.user_id = u.user_id WHERE o.status = 'paid'
21 rows. Every user with no paid order is gone, including the two who never ordered at all.
Still outer
The filter in ON
LEFT JOIN orders o ON o.user_id = u.user_id AND o.status = 'paid'
24 rows. Users with no paid order stay, padded — which is what a per-user report needs.
The rule underneath is short. In ON, a condition
decides what counts as a match. In WHERE, it filters the
finished join. For a condition on the left table the
two are equivalent. For the right table they are different
questions, and only one of them is usually the one you were asked.
Quick check
"Every product and its 2024 revenue." You LEFT JOIN products to orders and write WHERE o.ordered_at >= DATE '2024-01-01'. What is wrong with the result?
4
Which Join, and the Two You Will Rarely Write
The choice is not a matter of taste, and it reduces to one question:
should a row with no match still appear in the answer?
JOIN
No — drop it
"Revenue by country" is a question about orders. A user who never ordered contributes nothing to revenue and belongs nowhere in the result.
LEFT
Yes — keep it, at zero
"Orders per user" is a list of users. Leaving out the ones on zero hides precisely the fact the report exists to surface.
There is a reliable tell in how the question is phrased. If it opens
with the thing — "every user", "all products", "each category"
— that noun is the left table and the join is a LEFT one. If it opens
with the event — "every order", "all signups" — an inner join
is right, because an event always has the thing it happened to.
RIGHT JOIN
Keeps every row of the second table. It is a
LEFT JOIN with the tables written the other way round and
nothing else, and it is genuinely uncommon in production SQL: a reader
working down a long FROM clause has to hold the direction
in their head, so most teams swap the tables and write LEFT. Worth
recognising when you meet it; rarely worth writing.
FULL OUTER JOIN
Keeps unmatched rows from both sides, padding whichever
one is missing. This is the reconciliation join — two lists that ought
to agree, and you need to see what each has that the other does not,
in one pass.
SELECT COALESCE(a.id, b.id) AS id, a.total, b.total
FROM ledger a
FULL OUTER JOIN payments b ON b.id = a.id
WHERE a.id IS NULL OR b.id IS NULL; -- only the disagreements
Postgres has supported FULL OUTER JOIN for decades. Several
other databases still do not, or added it only recently — which is why
you will find old code faking it with two LEFT JOINs and a
UNION. On this engine you can simply write it.
Quick check
Finance asks for "a list of all 40 cost centres and what each spent last quarter". Eleven cost centres spent nothing. Which join, and why?
0 of 9 completed
Loading the tables…
01
To do
The last module ended on an uncomfortable fact: an inner join is also a
filter. users JOIN orders returned 24 rows — one per order
— and the two users who never bought anything vanished without a word.
LEFT JOIN is the fix. It keeps every row of the
left table whether or not it found a partner, and where there
was no partner it fills the right-hand columns with NULL.
FROM users u LEFT JOIN orders o ON o.user_id = u.user_id
Nothing else changes — same ON, same columns. "Left" means
the table named first, in FROM.
Your task: return u.name,
o.order_id and o.amount for every user, keeping
users who never ordered. Order by u.user_id, then
o.order_id.
query.sql
PostgreSQLCtrl↵ to run
Hint
The ON is the same condition an inner join would use: ON o.user_id = u.user_id. Only the join word changes.
Output
02
To do
Here is the reason LEFT JOIN earns its place. Once the
unmatched rows survive the join, they are identifiable: they
are exactly the rows where the right-hand side came back
NULL.
FROM users u LEFT JOIN orders o ON o.user_id = u.user_id WHERE o.order_id IS NULL
That is the anti-join, and it is the standard way to
ask "which of these has none of those". Test the right table's
primary key, not any column you happen to have handy —
a key is NOT NULL in the table, so a NULL there can only
have come from the padding.
Your task: return user_id and
name for every user who has never placed an order, ordered
by user_id.
query.sql
PostgreSQLCtrl↵ to run
Hint
WHERE o.order_id IS NULL — and it has to be IS NULL, because = NULL is never true. That was module 1-05.
Output
03
To do
The pattern is not about users. Any "which of these has none of those"
question is the same three lines, with different tables dropped in.
A catalogue is the obvious case: which products has nobody ever
bought? An inner join cannot answer it, because the answer is
precisely the rows an inner join deletes.
Note which table goes first. You are keeping every product, so
products is the left table and orders is the
one that may come back empty.
Your task: return product_id,
name and category for every product that has
never been ordered, ordered by product_id.
query.sql
PostgreSQLCtrl↵ to run
Hint
ON o.product_id = p.product_id, then WHERE o.order_id IS NULL. Products is on the left because products is what you want to keep.
Output
04
To do
This one is worth slowing down for, because it produces a wrong number
that looks entirely reasonable and raises no error.
After a LEFT JOIN, a user with no orders still has
one row — the padded one. So
COUNT(*) counts it and reports that they placed
1 order. They placed none.
COUNT(column) is the fix: it skips NULLs, which was true
back in module 2-01 and is exactly what is needed here. Counting
o.order_id — a column from the right table —
gives 0 for the padded rows.
COUNT(*) -- 1 for a user who bought nothing COUNT(o.order_id) -- 0, which is the truth
Your task: return u.name and the true
number of orders as orders, for every user, ordered by
orders descending then name.
query.sql
PostgreSQLCtrl↵ to run
Hint
COUNT(o.order_id) AS orders. Counting a right-hand column is what makes the padded rows score zero.
Output
05
To do
The second silent trap, and the one that catches people who already
know the first.
Put a condition on the right table in
WHERE, and it is applied after the join has
padded the unmatched rows. A padded row has
o.status = NULL, which is not 'paid' — and it
is not "not paid" either. It is unknown, so WHERE drops
it. The LEFT JOIN is now an inner join, and nothing
says so.
LEFT JOIN orders o ON o.user_id = u.user_id WHERE o.status = 'paid' -- 21 rows, orphans gone
LEFT JOIN orders o ON o.user_id = u.user_id AND o.status = 'paid' -- 24 rows, orphans kept
In ON, the condition decides what counts as a
match. In WHERE, it filters what the join already
built. For the left table, the two are the same; for the right table
they are completely different questions.
Your task: return u.name and
o.order_id for every user, matching only their
paid orders, and keeping users who have no paid order
at all. Order by u.user_id, then o.order_id.
query.sql
PostgreSQLCtrl↵ to run
Hint
Add the condition to the ON with AND: ON o.user_id = u.user_id AND o.status = 'paid'. Move it to WHERE and you lose the users you were trying to keep.
Output
06
To do
One more NULL to expect. Ask for SUM(o.amount) for a user
whose only row is the padded one, and the answer is not
0 — it is NULL.
That is consistent rather than perverse: there were no values to add,
and SQL will not invent one. COUNT is the exception that
returns 0, because "how many" of nothing genuinely is zero.
In a report a NULL total is usually wrong — a blank cell where the
reader expects 0.00, and any arithmetic downstream of it
goes NULL too. COALESCE, from module 1-05, is the fix.
COALESCE(SUM(o.amount), 0) AS spent
Your task: return u.name and total
spent for every user, with 0 rather than NULL
for users who never ordered. Order by spent descending,
then name.
query.sql
PostgreSQLCtrl↵ to run
Hint
Wrap the aggregate, not the column: COALESCE(SUM(o.amount), 0). COALESCE(o.amount, 0) inside the SUM works here too, but the outer one is the habit worth having.
Output
07
To do
A coverage question — "how much of the catalogue is actually selling?"
— needs every category present even if nothing in it ever sold. Same
reasoning as before: group the table you want complete, and let the
other side be NULL.
This is where LEFT JOIN and GROUP BY combine
into the shape most real reports have: one row per thing you
care about, with counts that may legitimately be zero, rather
than one row per thing that happened.
Your task: return each product category,
the number of products in it as products, and the number
of orders placed for those products as sold. Order by
category.
query.sql
PostgreSQLCtrl↵ to run
Hint
COUNT(DISTINCT p.product_id) AS products — distinct, because the join repeats a product once per order it has. COUNT(o.order_id) AS sold.
Output
08
To do
Two more that complete the set, and one honest piece of advice about
them.
RIGHT JOIN keeps every row of the second table.
It is LEFT JOIN with the tables written the other way
round, and it is genuinely rare in the wild — a reader following a long
FROM has to hold the direction in their head, so most
teams swap the tables and write LEFT instead.
FULL OUTER JOIN keeps both sides, padding
whichever one is missing. It is the join for reconciling two lists that
should agree and do not. Postgres has had it for as long as anyone can
remember; several other databases still do not.
FROM orders o RIGHT JOIN products p ON p.product_id = o.product_id -- every product survives
Your task: using a RIGHT JOIN, return
p.name and o.order_id for every product,
including the one nobody has bought. Order by p.product_id,
then o.order_id.
query.sql
PostgreSQLCtrl↵ to run
Hint
ON p.product_id = o.product_id. Products is written second, so RIGHT is what keeps it whole — 25 rows.
Output
09
To do
The decision is not stylistic, and it is easier than it looks. It comes
down to one question: should a row with no match still
appear?
JOIN
No — drop it
"Revenue by country" wants orders. A user who never ordered contributes nothing and belongs nowhere in the answer.
LEFT
Yes — keep it, at zero
"Orders per user" is a list of users. Leaving out the ones on zero hides exactly the fact the report exists to show.
The tell is in the noun. If the question starts "every user…", "all
products…", "each category…", the thing after it is the left table and
the join is a LEFT one. If it starts with the event — every order,
every signup — an inner join is right.
Your task: a churn report. Return u.name,
u.country and s.cancelled_on for
every user, including those with no subscription at
all. Order by u.user_id.
query.sql
PostgreSQLCtrl↵ to run
Hint
"Every user" is the tell — users is the left table and the join has to be a LEFT JOIN. Two users have no subscription row at all.
Output
The coverage report
To do
Every question in this module was the same question in different clothes:
what is missing, and does the report admit it? This is that
report, for the catalogue.
Your task: one row for every product,
whether or not it has ever sold, with:
name — the product name
category — its category
orders — how many paid orders it has, 0 if none
revenue — the total amount of those paid orders, 0 rather than NULL if none
Order by revenue descending, then name.
Two traps are deliberately in the way. Restricting to paid orders in the
WHERE clause will quietly delete the products that have never
sold — the rows this report exists to show. And an unsold product's
revenue is NULL until you say otherwise.
query.sql
PostgreSQLCtrl↵ to run
Hint
The paid condition belongs in the ON: LEFT JOIN orders o ON o.product_id = p.product_id AND o.status = 'paid'. Then COUNT(o.order_id) and COALESCE(SUM(o.amount), 0), grouped by p.product_id, p.name, p.category.