The Double Counting Trap
When joins multiply rows.
Here's a mistake that even experienced analysts make. When you join to a table with several matching rows, each row on the left gets copied, once for each match. Count after that, and your numbers can be much too big.

- Joins can copy rows
- One copy per match
- Counts come out too big
Spot the trap
Tara Brown has two orders, but three order lines. We join all the way to order items, and count. Run it. Count star says three. That's counting order lines, not orders. Count distinct order ID gives the real answer, two.
SELECT c.first_name,
COUNT(*) AS rows_counted,
COUNT(DISTINCT o.order_id) AS real_orders
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE c.customer_id = 1;โโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโโ โ first_name โ rows_counted โ real_orders โ โโโโโโโโโโโโโโผโโโโโโโโโโโโโโโผโโโโโโโโโโโโโโค โ Tara โ 3 โ 2 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโโโโดโโโโโโโโโโโโโโ
How to avoid it
How do you protect yourself? First, know how many rows each table has per item. Second, after a join, check the row count, does it make sense? Third, use count distinct when you're counting the thing on the one side. And fourth, only join the tables you actually need.
A shortcut: USING
One last join tip. When both tables call the matching column by the same name, you can write using, and the column name in brackets, instead of on. Run it. Two thousand three hundred and sixty three order lines, the same as order items.
SELECT COUNT(*) AS order_lines
FROM orders
JOIN order_items USING (order_id);โโโโโโโโโโโโโโโ โ order_lines โ โโโโโโโโโโโโโโโค โ 2363 โ โโโโโโโโโโโโโโโ
Big teams
Your turn. Using a self join, find the managers who have three or more people in their team. Pause and try. Here's a solution. Self join, group by the manager, then having count at least three. Only Marcus Lee.
Managers with a team of 3 or more
Show hint
Self join, GROUP BY manager, HAVING COUNT(*) >= 3
Show solution
SELECT m.name AS manager, COUNT(*) AS team_size
FROM staff AS s
JOIN staff AS m ON m.staff_id = s.manager_id
GROUP BY m.staff_id
HAVING COUNT(*) >= 3;โโโโโโโโโโโโโโฌโโโโโโโโโโโโ โ manager โ team_size โ โโโโโโโโโโโโโโผโโโโโโโโโโโโค โ Marcus Lee โ 3 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโ
Summary
Joins can multiply rows, so check your counts, and use count distinct when you need to. You've now finished joins, the hardest part of beginner SQL. Next, queries inside queries.
Key takeaways- Joins can copy rows
- Check row counts
- COUNT(DISTINCT ...)