Counting with LEFT JOIN

With LEFT JOIN, you can count even the things that have no match at all. How many orders has each customer placed, including the customers who have placed none?

SELECT c.name, COUNT(*) AS row_count, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY order_count, c.name;

Look at Henry Scott: COUNT(*) says 1, COUNT(o.id) says 0. Why? COUNT(*) counts rows, and the LEFT JOIN gave Henry one row, the one with NULLs in the columns of orders. COUNT(o.id) counts only the values that aren't NULL (lesson 5), and in Henry's only row, o.id is NULL. So it says 0, and that's the right answer. Isla Young has one real order, and 1 in both columns.

So when you count matches after a LEFT JOIN, don't use COUNT(*). Count a column of the right table that's never NULL in a real match, the same one you'd test with IS NULL: its primary key, or a column with NOT NULL.

SUM, AVG, MIN and MAX skip NULLs too, and for a group with nothing but NULLs, they give NULL, not 0. The money spent by a customer without orders would come out as NULL. When you want a 0, use COALESCE from lesson 4: COALESCE(SUM(i.quantity * i.price), 0).

Typical mistake: counting with a plain JOIN. The customers without orders are gone before GROUP BY, so the result has no zeros at all, and nothing tells you that someone is missing.