JOIN: rows from more than one table
A join matches rows across tables; which join you pick decides what happens when there is no match.
What a join actually does
A join pairs rows from two tables on a condition, usually a key: FROM orders JOIN customers ON orders.customer_id = customers.id. The result has the columns of both, one row per matching pair.
That "per matching pair" is worth holding on to. If a customer has three orders, joining customers to orders gives three rows for that customer, not one. A join does not merge tables; it multiplies rows wherever the condition matches more than once, which is why a count after a join is so often wrong.
Inner, left, and the question each answers
A plain JOIN — an inner join — keeps only rows that matched on both sides. A customer with no orders disappears entirely, which is right when you are listing orders and wrong when you are listing customers.
LEFT JOIN keeps every row from the left table and fills the right side with NULL where nothing matched. "Every customer, with their orders if they have any" is a LEFT JOIN, and the customers with none come back with NULLs rather than vanishing.
The trap is putting a condition on the right table in WHERE instead of ON. WHERE orders.status = 'paid' after a LEFT JOIN throws away exactly the rows the LEFT JOIN was there to keep, because NULL <> 'paid' — the join quietly becomes an inner one. Conditions about the right table belong in ON.
Counting after a join
COUNT(*) counts rows, including ones where the right side is all NULL. After a LEFT JOIN that means a customer with no orders counts as 1, not 0.
COUNT(orders.id) counts non-NULL values of that column, which is what you wanted: a customer with no orders counts 0. The difference between COUNT(*) and COUNT(column) is invisible until a join introduces NULLs, and then it is the whole answer.
Worked examples
Every customer, with how many orders they have placed
/SELECT c.name, COUNT(o.id) AS orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.name/Against:
customers(id, name) · orders(id, customer_id, status)COUNT(o.id), not COUNT(*): a customer with no orders should read 0, and COUNT(*) would say 1.
The same, but only paid orders — and still every customer
/SELECT c.name, COUNT(o.id) AS paid FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid' GROUP BY c.name/Against:
customers(id, name) · orders(id, customer_id, status)The status test is in ON. In WHERE it would drop every customer who has never paid, turning the LEFT JOIN into an inner one.
Check yourself
Practice questions written for this lesson — not past exam papers.
Practise every question in this subject
After a LEFT JOIN, what does putting a condition about the right table in WHERE do?
Why use COUNT(o.id) rather than COUNT(*) after a LEFT JOIN?