SQL / JOINS
LEFT JOIN and keeping every row on one side
Use LEFT JOIN to keep every row of the first table, read the NULLs it manufactures for unmatched rows, and count or total matches without inflating them.
What you will learn
- Keep every row of the left table and read right-side NULLs as 'no match found'
- Predict the row count: matched pairs plus one NULL-filled row per unmatched left row
- Count real matches with COUNT(o.id) instead of COUNT(*) after a LEFT JOIN
- Pre-aggregate the right table in a subquery to get exactly one row per left row
Understanding LEFT JOIN and keeping every row on one side
A LEFT JOIN produces the same set of matched pairs an INNER JOIN would, then adds back every left row that matched nothing, filling the right table's columns with NULL. Those NULLs are not stored anywhere; the join invents them as padding so the unmatched row still has the full column list. The left table therefore acts as the spine of the result: whatever is in it appears, and the right table can only contribute detail to it.
Keeping every row means at least one output row per left row, not exactly one. If three orders point at the same customer, that customer appears three times, exactly as in an inner join; the LEFT part only governs customers with zero orders. So the result size is matched pairs plus unmatched left rows, a number that is never smaller than the left table's row count and often larger.
Because the NULLs come from the join rather than the data, a right-side column declared NOT NULL can still arrive as NULL, and every expression downstream has to cope with that. COUNT(*) counts the padded placeholder row while COUNT(o.id) skips it, which is why the same grouped query can report one order or zero orders for a customer who ordered nothing. SUM over a group whose only row is padding returns NULL rather than 0, so COALESCE(SUM(...), 0) is what produces a real zero.
Which table is 'left' is a decision you make by writing order, not something the data determines. Ask which side the reader expects to see in full, put that table first, and accept that unmatched rows on the other side vanish.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount INTEGER NOT NULL
);
INSERT INTO customers (id, name) VALUES
(1, 'Ada'), (2, 'Bo'), (3, 'Cleo');
INSERT INTO orders (id, customer_id, amount) VALUES
(10, 1, 50), (11, 1, 20), (12, 3, 75);
SELECT c.id, c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;A LEFT JOIN is the inner join result plus one NULL-padded copy of every left row that found no partner, so left rows can be duplicated but never lost.
Worked examples
Counting matches per left row
Shows why COUNT(*) reports 1 for a customer with no orders while COUNT(o.id) reports 0.
-- continues from the tables created above
SELECT c.name,
COUNT(*) AS rows_out,
COUNT(o.id) AS orders,
COALESCE(SUM(o.amount), 0) AS total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY c.name;Example explained
Line 1COUNT(*) counts output rows, and Bo's NULL-padded row is still a row, so Bo gets 1.
Line 2COUNT(o.id) ignores NULL, so it counts real orders and correctly gives Bo 0.
Line 3SUM(o.amount) for Bo's group sees only NULL and returns NULL, which COALESCE turns into 0.
Line 4GROUP BY runs after the join, so it groups rows that already contain the padding.
One output row per left row
Pre-aggregating the right table makes the join key unique there, so no left row is duplicated.
-- continues from the tables created above
SELECT c.id, c.name, o.total
FROM customers c
LEFT JOIN (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) o ON o.customer_id = c.id
ORDER BY c.id;Example explained
Line 1The subquery collapses orders to one row per customer_id, so the right-side key is unique.
Line 2With a unique key on the right, Ada's two orders become one row instead of two.
Line 3Bo has no row in the subquery at all, so o.total is padded to NULL, not 0.
Line 4Wrap it as COALESCE(o.total, 0) if a zero reads better than a blank.
Swapping which side is preserved
Putting orders on the left keeps an order whose customer_id matches nothing, and drops the customer with no orders.
-- continues from the tables created above
INSERT INTO orders (id, customer_id, amount) VALUES (13, 99, 40);
SELECT o.id AS order_id, o.customer_id, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;Example explained
Line 1Order 13 points at customer 99, which does not exist, so c.name is padded with NULL.
Line 2orders is now the left table, so orphan orders survive and Bo is nowhere in the result.
Line 3The same two tables and the same ON condition give a different answer purely because of writing order.
Important notes
A right-side column declared NOT NULL can still be NULL in the result, since the join creates that NULL; the constraint says nothing about joined output.
NULL join keys never match anything, so a left row whose foreign key is NULL is always kept as an unmatched row regardless of what the right table holds.
Common mistakes
Using COUNT(*) after a LEFT JOIN with GROUP BY: customers with no orders report 1 instead of 0, because the NULL-padded placeholder is still a row.
Assuming one output row per left row: when the right table has several matching rows per key, left rows repeat and any downstream count or average is inflated.
Doing arithmetic or concatenation straight on right-side columns: o.amount * 1.2 is NULL for unmatched rows, so the whole computed column silently becomes NULL instead of 0.
Try it yourself
Change, predict, then run
In a browser SQL editor, build customers and orders, add a customer with no orders and give one customer three orders, then write a single LEFT JOIN query listing every customer with order_count and total_amount showing 0 for the customer with none. Check that the three-order customer produces three rows before grouping and one row after.
Open the SQL workspaceCheck your understanding
customers has 5 rows. orders has 8 rows: 6 point at customer ids that exist, and those 6 belong to just 3 different customers; the other 2 point at ids not in customers. How many rows does SELECT * FROM customers c LEFT JOIN orders o ON o.customer_id = c.id return?
- 8
- 5
- 6
- 13
Show answer
The 6 matching orders form 6 pairs, and the 2 customers with no orders are added back NULL-padded, giving 8; the 2 orphan orders never appear because they sit on the right side. 5 is the tempting answer because 'every customer is kept' sounds like one row per customer, but a customer with several orders still repeats once per order.