An inner join keeps rows that match on both sides.
A LEFT JOIN keeps every row from the left table whether or not the
right side has anything, filling the missing columns with NULL.
The pizza schema is built around a case where that matters.
customer_order.user_id is nullable on purpose: it is NULL exactly when the
order came from a guest checkout, with no account behind it. Ten of the eighteen demo orders are
guest orders, so any query that joins orders to accounts has to decide what to do about them.
What it does
SELECT o.id, o.customer_name, u.email AS account_email, o.guest_email
FROM customer_order o
LEFT JOIN app_user u ON u.id = o.user_id
ORDER BY o.id
LIMIT 5;+----+---------------+---------------------+-----------------------+
| id | customer_name | account_email | guest_email |
+----+---------------+---------------------+-----------------------+
| 1 | Demo Customer | customer@pizza.test | NULL |
| 2 | Alex Rivera | NULL | guest1@example.com |
| 3 | Demo Customer | customer@pizza.test | NULL |
| 4 | Sam Chen | NULL | guest2@example.com |
| 5 | Jordan Blake | NULL | abandoned@example.com |
+----+---------------+---------------------+-----------------------+Orders 2, 4 and 5 have no account. Every column that came from app_user is NULL for
them — not because the account has a NULL email, but because there is no matching row at
all. That is where outer-join NULLs come from, and it is worth keeping the two causes
straight when you read a result.
The counts
This is the difference in one number. Inner join:
SELECT COUNT(*) AS inner_rows FROM customer_order o JOIN app_user u ON u.id = o.user_id;+------------+
| inner_rows |
+------------+
| 8 |
+------------+Left join:
SELECT COUNT(*) AS left_rows FROM customer_order o LEFT JOIN app_user u ON u.id = o.user_id;+-----------+
| left_rows |
+-----------+
| 18 |
+-----------+18 is the number of orders. That is the useful property: a left join cannot lose rows from the table you started with. If a report is meant to have one row per order, a left join guarantees it and an inner join quietly does not.
Finding the rows with nothing
Because unmatched rows get NULL on the right, testing the right side for NULL isolates exactly those — the anti-join:
SELECT o.id, o.customer_name, o.guest_email
FROM customer_order o
LEFT JOIN app_user u ON u.id = o.user_id
WHERE u.id IS NULL
ORDER BY o.id
LIMIT 4;+----+---------------+-----------------------+
| id | customer_name | guest_email |
+----+---------------+-----------------------+
| 2 | Alex Rivera | guest1@example.com |
| 4 | Sam Chen | guest2@example.com |
| 5 | Jordan Blake | abandoned@example.com |
| 7 | Priya Nair | guest3@example.com |
+----+---------------+-----------------------+Test a NOT NULL column of the right table — the primary key is the safe choice. Testing a nullable column cannot distinguish "no matching row" from "matched a row whose value is NULL".
Counting, with the zeros kept
The other everyday use is counting children without dropping the childless parents:
SELECT i.id, i.product_name, COUNT(t.id) AS toppings
FROM order_item i
LEFT JOIN order_item_topping t ON t.order_item_id = i.id
GROUP BY i.id, i.product_name
ORDER BY toppings DESC, i.id
LIMIT 5;+----+---------------------+----------+
| id | product_name | toppings |
+----+---------------------+----------+
| 10 | Veggie Lovers Pizza | 2 |
| 11 | Pepperoni Pizza | 2 |
| 19 | Pepperoni Pizza | 2 |
| 26 | Supreme Pizza | 2 |
| 4 | Meat Lovers Pizza | 1 |
+----+---------------------+----------+COUNT(t.id), not COUNT(*). An item with no toppings still produces one
row — with NULLs on the right — so COUNT(*) would count that row and report 1 instead
of 0. COUNT(column) ignores NULLs, which is exactly what you want here. See
NULL and IS NULL.
The mistake everyone makes once
Put a condition on the right-hand table in WHERE, and the left join silently becomes
an inner join:
-- looks like a LEFT JOIN, behaves like an INNER JOIN
SELECT COUNT(*) AS rows_returned
FROM customer_order o
LEFT JOIN app_user u ON u.id = o.user_id
WHERE u.role = 'CUSTOMER';+---------------+
| rows_returned |
+---------------+
| 8 |
+---------------+Eight, not eighteen. Every guest order has u.role = NULL, and
NULL = 'CUSTOMER' is unknown, so WHERE drops it. The left join did its
job and then WHERE undid it.
Move the condition into ON and the unmatched rows survive:
SELECT COUNT(*) AS rows_returned
FROM customer_order o
LEFT JOIN app_user u ON u.id = o.user_id AND u.role = 'CUSTOMER';+---------------+
| rows_returned |
+---------------+
| 18 |
+---------------+The rule is worth memorising, because nothing warns you: a condition in ON
decides what counts as a match; a condition in WHERE filters the rows that came
out. For the left table it makes no difference. For the right table it is the difference
between a left join and an inner one.
The one deliberate exception is the anti-join above, where WHERE ... IS NULL in the
WHERE clause is precisely the point.
What to remember
LEFT JOINkeeps every left row; unmatched right columns come back NULL.- It cannot lose rows from the left table — which is why report queries prefer it.
WHERE right.pk IS NULLfinds the rows with no match.- Use
COUNT(right.col), notCOUNT(*), when counting children. - A filter on the right table belongs in
ON, notWHERE.