SQL join producing duplicate rows: how to debug

TL;DR

Check key uniqueness on each side of the join separately with GROUP BY plus HAVING COUNT(*) > 1, then join against a deduplicated version of the offending side. Duplicate rows come from a join key that isnt unique on one or both sides, so every matching pair fans out into a cartesian product for that key.

SQL join producing duplicate rows: how to debug

Use this when

• Row count explodes after adding a JOIN
• Sums or counts double (or worse) after joining a dimension table
• You need to prove which table holds the duplicate keys

Not for this skill when

• The join returns too few rows (thats a key-mismatch problem)
• You are merging dataframes in pandas
• The join is correct but slow (thats an indexing problem)

Steps

1. Measure the fanout. Count rows before and after the join:

SELECT COUNT(*) AS orders_rows FROM orders;
SELECT COUNT(*) AS joined_rows
FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

Expected output: two numbers. If joined_rows is bigger than orders_rows, the join multiplied rows and the key isnt unique on the customers side (or both sides).

1. Find duplicate keys on each side independently:

SELECT customer_id, COUNT(*) AS n
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY n DESC
LIMIT 20;

Expected output: the offending keys and how many times each repeats. Run the same query against orders. Whichever side shows duplicates is the fanout source.

1. See the fanout per key to confirm the multiplication:

SELECT o.customer_id,
       COUNT(*) AS order_rows,
       COUNT(c.customer_id) AS joined_rows
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id
HAVING COUNT(c.customer_id) > COUNT(*)
LIMIT 20;

Expected output: keys where one order row became several joined rows. The ratio shows the duplication factor.

1. Fix it by deduplicating the many side before joining. Pick the row you actually want per key:

WITH latest_customer AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM customers
)
SELECT o.order_id, c.name
FROM orders o
JOIN latest_customer c ON o.customer_id = c.customer_id AND c.rn = 1;

Expected output: the joined row count now matches the orders row count. The ROW_NUMBER pattern keeps the latest record per key; use an aggregate instead if you want rolled-up values.

1. Verify the fix with the counts from step 1:

SELECT COUNT(*) FROM orders o
JOIN (SELECT DISTINCT customer_id FROM customers) c
  ON o.customer_id = c.customer_id;

Expected output: a count equal to the base table count (for an inner join on a clean key). If it still exceeds, both sides have duplicates and you need to dedupe both.

Variant phrasings

join returns more rows than left table

The textbook fanout symptom. Steps 1-2 take about a minute and name the culprit table.

SQL join duplicates sum doubled

Aggregates computed after a fanning join are wrong. Aggregate first, then join, or dedupe first, then aggregate. Never aggregate over a fanned join and hope.

how to check if join key is unique in SQL

Step 2 is the query. A unique key returns zero rows from the HAVING query.

Why it happens

A join pairs every row on the left with every row on the right that shares the key. If a key appears twice on the right, each left row for that key produces two output rows. Analysts assume dimension tables have unique keys, but slowly changing dimensions, late-arriving corrections, and missing dedupe steps break that assumption silently.

Edge cases

• Both sides duplicated: dedupe both, or the fanout multiplies (2 x 3 = 6 rows per key).
• NULL keys never join to each other in an inner join, but they do fan out in some outer-join patterns; filter them first if they shouldnt be there.
• Joining on multiple columns: uniqueness must hold for the combination, check the tuple not each column alone.
• SELECT DISTINCT on the final result hides the fanout instead of fixing it and can mask real duplicates you wanted to keep.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_Bbp1q4vGfZb4PosqG88bBw