AI Hints
Homeโ€บSQL Tutorialโ€บMore Kinds of Join
SQL ยท Lesson 53 of 126

The Double Counting Trap

When joins multiply rows.

โฑ 2 min readโ–ถ Video at 1:14:59๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

The Double Counting Trap
From the video: The Double Counting Trap
  • 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

Know your tablesone row per what?
Check row countsafter every join
COUNT(DISTINCT)for the 'one' side
Join lessonly what you 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.

query.sqlsql
SELECT COUNT(*) AS order_lines
FROM orders
JOIN order_items USING (order_id);
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

โœ๏ธ Exercise: Big teams

Managers with a team of 3 or more

Show hint

Self join, GROUP BY manager, HAVING COUNT(*) >= 3

Show solution
solution.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  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 ...)
Next up: Subqueries โ€” a query inside a query