Why did my rows multiply?
NOVA has 12 customers. Harry joins customers to orders for a quick report, half- expecting 12 rows back — one per customer.
SELECT customers.name, orders.id, orders.amount
FROM customers
JOIN orders ON customers.id = orders.customer_id;Wait, why did my rows multiply? I joined a 12-row table and got 22 rows out.
Nothing multiplied by accident — it did exactly what you asked. Let's look at just one customer and see it happen in slow motion.
Alice has placed 4 orders. Filter the join down to just her:
SELECT customers.name, orders.id, orders.amount
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE customers.id = 1
ORDER BY orders.id;| name | id | amount |
|---|---|---|
| Alice Sharma | 1 | 79.99 |
| Alice Sharma | 2 | 49.98 |
| Alice Sharma | 3 | 89 |
| Alice Sharma | 4 | 39 |
One customer row. Four order rows that point at her. A JOIN doesn’t collapse those four into one — it pairs Alice’s row with each matching order, once per match. Her name just looks “duplicated” because you’re seeing the same customer stitched onto four different orders. This is a one-to-many relationship: one row on the customers side, many rows on the orders side. Multiply that out across all 12 customers and 22 total orders, and 22 rows out is exactly correct — not a bug.
A JOIN doesn’t limit how many rows match. It pairs every match it finds, on both sides.
Running…
This is where it actually bites people. Say Harry wants to know how many customers have placed an order. He’s just joined the two tables, so he reaches for COUNT(*):
SELECT COUNT(*) AS row_count, COUNT(DISTINCT customers.id) AS customer_count
FROM customers
JOIN orders ON customers.id = orders.customer_id;| row_count | customer_count |
|---|---|
| 22 | 10 |
COUNT(*) counts rows — and after this join, a row means “one order,” not “one customer.” Alice alone accounts for 4 of those 22 rows. If you actually want “how many customers,” you have to say so: COUNT(DISTINCT customers.id) counts unique customer ids, no matter how many order rows each one is attached to.
One-to-many happens because orders.id is unique but orders.customer_id isn’t — one side of the join key repeats. What if neither side is unique? You get every combination of matches on both sides — a many-to-many blow-up. NOVA doesn’t have a table built specifically for that, but you can see the exact same arithmetic by joining a table to itself.
Say ops wants every pair of orders placed by the same customer, to review side by side for accidental duplicates:
SELECT o1.id AS order_a, o2.id AS order_b
FROM orders o1
JOIN orders o2
ON o1.customer_id = o2.customer_id
AND o1.id < o2.id
WHERE o1.customer_id = 1;| order_a | order_b |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 3 |
| 2 | 4 |
| 3 | 4 |
Alice’s 4 orders produce 6 pairs, not 4. Every order can pair with every other order from the same customer — o1.id < o2.id just stops each pair from being counted twice (once as (1, 2), once as (2, 1)) and stops an order from pairing with itself. That’s the many-to-many rule in miniature: neither side of customer_id is unique here, so the row count multiplies out combinatorially — matches on one side times matches on the other, not just added together.
Try it: Drop the WHERE o1.customer_id = 1 filter — how many pairs come back across every customer?
Run your query to see results here.
22 rows — the same total as the number of orders, just by coincidence in this dataset, not because of any rule. Customers with only one order (like Diego or Farah) contribute zero pairs; Emma Wilson, with 5 orders, alone contributes 10. The lesson isn’t the exact number — it’s that the row count from a join is never just “the size of the bigger table.” It’s a function of how many matches each individual row finds on the other side.
Finance ran a query joining customers to orders, filtered to completed orders, and counted 18 rows — then reported “18 customers have completed an order.” Figure out the actual number of distinct customers with at least one completed order, and write the query that gets it right.
Hint 1
Finance's 18 is real — but it's counting completed order rows, not customers. Some customers have more than one completed order.
Hint 2
You want the number of unique customers left after the join and the WHERE filter, not the number of rows.
Hint 3
SELECT COUNT(DISTINCT customers.id) FROM customers JOIN orders ON customers.id = orders.customer_id WHERE orders.status = 'completed';
Solution
Running…
The real number is 10, not 18 — Alice alone has 3 completed orders, Emma has 4. Both numbers were computed correctly by SQL; only one of them answers the question finance actually asked. Whenever a row count after a join looks surprising, the fix isn’t to distrust the join — it’s to ask which side of the relationship is repeating, and count the key you actually care about instead of the rows.
A JOIN pairs every matching row on both sides — it never limits or collapses matches. One-to-many multiplies rows by however many match on the “many” side; many-to-many multiplies by both sides at once. If a row count looks wrong after a join, count DISTINCT on the key you actually mean, don’t just COUNT(*).