After this lesson you can
- Choose between an INNER JOIN and a LEFT JOIN deliberately
- Predict which rows survive each one
- Explain why a filter on the outer table belongs in ON, not WHERE
A join matches rows in one table against rows in another using a condition you supply.
INNER JOIN keeps only rows that matched on both sides.
LEFT JOIN keeps every row from the left table, and fills the right-hand
columns with NULL where nothing matched.
SELECT c.name, o.total_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
A customer with no orders still appears, once, with total_cents null.
Try it
CREATE TABLE customers ( id int PRIMARY KEY, name text NOT NULL);CREATE TABLE orders ( id int PRIMARY KEY, customer_id int NOT NULL, status text NOT NULL, total_cents int NOT NULL);INSERT INTO customers VALUES (1, 'Nino'), (2, 'Ana'), (3, 'Luka');INSERT INTO orders VALUES (10, 1, 'paid', 4500), (11, 1, 'cancelled', 900), (12, 2, 'paid', 12000);SELECT c.name, COALESCE(SUM(o.total_cents), 0) AS paid_centsFROM customers cLEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'GROUP BY c.id, c.nameORDER BY c.name;The trap
This mistake is easy to write and easy to miss in review:
SELECT c.name, SUM(o.total_cents)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status <> 'cancelled' -- the bug
GROUP BY c.name;
Customers with no orders survive the join with every o.* column null. The
WHERE clause then evaluates NULL <> 'cancelled', which is unknown rather
than true, so those rows are thrown away. The query is now an inner join and
nobody changed the word LEFT.
The fix is to move the condition into the ON clause, where it decides
which orders are allowed to match rather than which customers
survive:
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.status <> 'cancelled'
The rule worth carrying out of this lesson: a filter on the outer side of a
LEFT JOIN belongs in ON; a filter on the preserved side belongs in
WHERE.
Common mistakes
- Counting with
COUNT(*)after aLEFT JOIN. A customer with no orders is still one row, with every order column null, soCOUNT(*)gives them 1. Count a column from the right-hand table instead.
CREATE TABLE customers ( id int PRIMARY KEY, name text NOT NULL);CREATE TABLE orders ( id int PRIMARY KEY, customer_id int NOT NULL, status text NOT NULL, total_cents int NOT NULL);INSERT INTO customers VALUES (1, 'Nino'), (2, 'Ana'), (3, 'Luka');INSERT INTO orders VALUES (10, 1, 'paid', 4500), (11, 1, 'cancelled', 900), (12, 2, 'paid', 12000);SELECT c.name, COUNT(*) AS count_star, COUNT(o.id) AS count_ordersFROM customers cLEFT JOIN orders o ON o.customer_id = c.idGROUP BY c.id, c.nameORDER BY c.id;- Grouping by a name instead of the key. Two different customers called Ana become one group, and their orders are added together.
CREATE TABLE customers ( id int PRIMARY KEY, name text NOT NULL);CREATE TABLE orders ( id int PRIMARY KEY, customer_id int NOT NULL);INSERT INTO customers VALUES (1, 'Ana'), (2, 'Ana'), (3, 'Luka');INSERT INTO orders VALUES (10, 1), (11, 1), (12, 2);SELECT c.name, COUNT(o.id) AS order_countFROM customers cLEFT JOIN orders o ON o.customer_id = c.idGROUP BY c.nameORDER BY c.name;- Filtering the outer table in
WHERE. The trap above: theLEFT JOINbecomes an inner join without anyone touching the wordLEFT.
What interviewers ask
- What is the difference between an
INNER JOINand aLEFT JOIN? Inner keeps only matched pairs; left keeps every left row once or more, padding the right side withNULLwhere nothing matched. - How do you find customers who have never ordered?
LEFT JOIN ordersand keep the rows where the order's keyIS NULL, orWHERE NOT EXISTSwith a correlated subquery. - Why can a condition in
ONand the same condition inWHEREgive different results? For an outer join,ONdecides which rows match;WHEREruns afterwards and removes rows, including the padded ones.
Exercise · Pro
Every customer, and how many orders they have placed
Exercises are free in the first 3 lessons of every course. From here on they are part of Pro and Sprint; the lesson and its runnable examples stay open.
Upgrade