Why a SQL join returns too many rows, and how to fix it

Two causes cover almost every case

• Non-unique keys: the column you join on repeats in both tables (one-to-many on both sides, so many-to-many overall).
• Incomplete join condition: the ON clause uses only part of a composite key, or a table is listed with no condition at all, producing a cross join.

Both give a result that looks plausible, which is why the error often shows up later as an inflated total. If you are new to joins, start with combining two tables in SQL; this page is about diagnosing row explosion.

A worked example of fan-out

You want revenue and refunds per order. Orders are joined to both order_lines and refunds, each of which can have several rows per order.

SELECT o.order_id,
       SUM(l.amount) AS revenue,
       SUM(r.amount) AS refunded
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
LEFT JOIN refunds r ON r.order_id = o.order_id
GROUP BY o.order_id;

Every line is repeated once per refund, and every refund once per line. Revenue doubles (two refunds) and refunds triple (three lines). Adding DISTINCT inside the SUM would not fix it reliably, because two genuine lines with the same amount would collapse into one.

Step 1: count keys before you join

Check the grain of each table. Any key that appears more than once will multiply rows on the other side.

SELECT order_id, COUNT(*) AS n
FROM refunds
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY n DESC;

Do this for every table in the query. Then compare the row count of the joined result with the row count of the table whose grain you expect (here, orders). If the joined count is higher, the extra rows came from a repeated key.

Step 2: aggregate the many side in a CTE

Bring each child table to one row per key before joining. Then every join is one-to-one or many-to-one and nothing multiplies.

WITH line_totals AS (
  SELECT order_id, SUM(amount) AS revenue
  FROM order_lines
  GROUP BY order_id
),
refund_totals AS (
  SELECT order_id, SUM(amount) AS refunded
  FROM refunds
  GROUP BY order_id
)
SELECT o.order_id,
       COALESCE(lt.revenue, 0)  AS revenue,
       COALESCE(rt.refunded, 0) AS refunded
FROM orders o
LEFT JOIN line_totals   lt ON lt.order_id = o.order_id
LEFT JOIN refund_totals rt ON rt.order_id = o.order_id;

Order 501 now returns revenue 100 and refunded 15. The pattern scales to any number of child tables, which is why it is the standard approach when joining multiple tables in SQL.

Step 3: check every ON condition

• Composite keys: if prices are unique per (sku, region), join on both: ON p.sku = o.sku AND p.region = o.region.
• Comma joins: FROM a, b WHERE a.id = c.id with a typo or missing predicate makes a Cartesian product of a and b. Prefer explicit JOIN … ON so a missing condition is a syntax error in most databases.
• Date-effective tables: a price or rate table with valid_from and valid_to needs a range condition, such as o.order_date >= p.valid_from AND o.order_date < p.valid_to, or each order matches every historical price.
• Overlapping ranges: if valid ranges overlap in the lookup table, the range join still returns two rows. Check for overlaps with a self-join.

Why DISTINCT is usually the wrong fix

SELECT DISTINCT hides the symptom: it removes rows that are identical in every selected column. It is expensive on large results, it silently collapses legitimate duplicate rows, and it does nothing when the duplicated rows differ in one column. If you find yourself adding DISTINCT to make totals correct, the grain of a join is wrong. Fix it upstream with a CTE, a window function such as ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) to keep one row per key, or EXISTS when you only need to know a match exists.

-- Keep only customers who have ordered, without multiplying rows
SELECT c.*
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Analistable runs DuckDB in your browser and the AI writes read-only SQL from your sheets' schema. When it joins tables with repeated keys it should aggregate first, and you can open the generated SQL to confirm. See the pandas, R and SQL merge guide for the same problem in other languages.

Make the grain visible

Write the expected grain of every query as a comment at the top, such as 'one row per order'. Then end with a check: SELECT COUNT(*), COUNT(DISTINCT order_id) FROM (...). If the two numbers differ, a join has multiplied rows. This takes seconds and catches most fan-out bugs before a report goes out.

Frequently asked questions

Is a many-to-many join ever correct?

Yes, when you genuinely want every pairing, such as every product with every store for a stock matrix. It's only a problem when you then sum values that were repeated.

How many rows should a join return?

For each key, the number of matching rows on the left times the number on the right. If either side is unique per key, an inner join returns at most the row count of the other side.

Does LEFT JOIN cause duplicate rows?

Only when the right table has more than one row per key. A LEFT JOIN keeps every left row and repeats it once per extra match.

How do I join to only the latest row per key?

Number the rows with ROW_NUMBER() OVER (PARTITION BY key ORDER BY date DESC) in a CTE, filter to rn = 1, and join to that.

Can GROUP BY after the join fix the totals?

Not for sums from two different child tables, because the multiplication has already happened. Aggregate each child table before joining.

Related guides

• How to join multiple tables in SQL
• How to combine two tables in SQL
• Why pandas merge creates duplicate rows, and how to stop it
• How to use MERGE (upsert) in BigQuery and Snowflake
• How to merge datasets in Stata