Why your SQL JOIN returns duplicate rows (and doubles your total)

SELECT o.order_id, p.product_id
FROM orders o
LEFT JOIN products p
  ON p.seller_id = o.seller_id;

Your sales total looked right until you added a JOIN. Now it’s more than double. Where did the extra money come from?

Say an order costs €40 and contains two items. Join that order to its items and you get two rows, each carrying the same €40 order total. Add the column and you report €80 for a €40 sale. The JOIN did what you asked; the problem is what you chose to add up.

Below are the minimum and maximum row counts for each SQL JOIN type — useful for a data analyst interview and for the first time someone asks why your report doesn’t match theirs.

What the extra rows do to your money

On the Meridian practice marketplace there are 9,071 paid orders with a total goods value (GMV) of €552,645. Every order has one to four item lines — 1.79 on average. Now we want GMV, but the query also joins the items, say to filter by product later:

SELECT COUNT(*)                AS rows_after_join,
       SUM(o.items_total_eur)  AS gmv_eur
FROM meridian.orders AS o
JOIN meridian.order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid';
-- rows_after_join: 16,192   gmv_eur: 1,239,938

9,071 orders became 16,192 rows. An order with three items now appears three times, and its items_total_eur is added three times. The result, €1,239,938, is 2.2 times the real number.

SQL interview question: two tables have ten rows each. How many after a JOIN?

“Two tables have ten rows each. What is the minimum and maximum number of rows after an INNER, LEFT, RIGHT, FULL or CROSS JOIN?”

This is a classic SQL interview question for data analysts. A good answer starts with the join condition and whether the keys are unique. Knowing only the table sizes gives you a range, not an exact result.

For example, can a LEFT JOIN between two ten-row tables return 100 rows?

Yes. A join returns matching pairs, not one result for each distinct key. Assume both tables contain exactly ten rows, the condition is left.key = right.key, duplicate keys are allowed, and there is no later WHERE, DISTINCT, grouping or limit:

For FULL OUTER JOIN, no matches gives 20, not zero or ten: all ten unmatched rows from each side survive. The minimum of ten happens when all rows match one-to-one. With an ordinary equality condition, NULL keys do not match each other.

The maximum is easy to construct. Put the value 1 in every key on both sides. Each of the ten left rows matches ten right rows: 10 × 10 = 100, including with LEFT and RIGHT JOIN. By contrast, if both join keys are unique and every row has a match, all four conditional joins return ten rows.

Try the all-to-all case in DuckDB:

WITH l AS (SELECT range AS row_id, 1 AS join_key FROM range(10)),
     r AS (SELECT range AS row_id, 1 AS join_key FROM range(10))
SELECT COUNT(*) AS joined_rows
FROM l LEFT JOIN r ON l.join_key = r.join_key;
-- 100

These bounds change when you know more: uniqueness constraints, an empty table, extra filters or a different join condition. DuckDB’s JOIN documentation describes matching pairs and unmatched rows.

How to spot it

• Count rows before and after the join. If the count grew and you didn’t expect it to, you have multiplication.
• Know the grain of each table. “One row per order”, “one row per order line”, “one row per customer per day”. Write the grain next to the table name when you start.
• Compare with a trusted total. If GMV is suddenly twice last week’s, suspect the join before celebrating.

Three ways to fix it

1. Aggregate first, then join. Bring the many-side down to one row per key:

WITH items AS (
  SELECT order_id, SUM(qty) AS units
  FROM meridian.order_items
  GROUP BY order_id
)
SELECT SUM(o.items_total_eur) AS gmv_eur, SUM(i.units) AS units
FROM meridian.orders AS o
JOIN items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid';
-- gmv_eur: 552,645

2. Don’t join what you don’t need. If the question is about orders, sum the orders table alone. Many duplicated totals come from a join that was left in “just in case”.

3. Count distinct keys when you count entities. COUNT(DISTINCT o.order_id) gives the right number of orders after the join. Be careful: there is no safe SUM(DISTINCT ...) for money — two different orders can have the same amount.

The opposite trap: joins that drop rows

An inner JOIN silently removes rows without a match. Join orders to a sellers table that is missing a few sellers and those orders disappear from every total. Use LEFT JOIN when the left table must stay whole, and again: compare row counts.

Practise predicting before you run the query

Atterna Start introduces INNER and LEFT JOIN in day 8, then uses day 9 to investigate orders joined to item lines and inflated totals. You predict what happens, run queries and check the result on the fictional Meridian marketplace. Understanding what one row represents matters more than memorising a diagram.

See the Start curriculum. Days 1–2 are free without an account; a free account opens the rest, including the full JOIN sessions on days 8 and 9. The two mini-case decisions on this page are free too. Case rounds and the Meridian simulator are the paid levels: compare them on the levels page.

FAQ

How should I answer a SQL JOIN row-count interview question?

State your assumptions before giving a number. For two ten-row tables joined with equality, allowing duplicate keys and with no later filters, INNER JOIN can return 0–100 rows; LEFT, RIGHT and FULL OUTER JOIN can return 10–100; CROSS JOIN returns exactly 100. Then explain one example: if every key is 1, each of ten rows matches ten others. If the interviewer gives you unique keys or filters, work out the bounds again using those conditions.

Why does my JOIN return more rows than the table?

Because the other table has more than one matching row for some keys. Every match produces its own output row, so a one-to-many join repeats the rows of the “one” side.

How do I sum after a join without double counting?

Aggregate the many-side to one row per key in a subquery or CTE first, then join; or avoid the join if you don’t need its columns. COUNT(DISTINCT ...) works for counting entities, not for summing money.

Should I use DISTINCT to remove duplicates after a join?

SELECT DISTINCT hides the symptom and can remove legitimate rows. Fix the grain instead: aggregate before joining or join to a table with one row per key.

Where can I practise join traps?

Atterna Start spends a whole day on join traps on marketplace data, with every answer checked automatically. The level check includes a question on exactly this.