SQL · join behavior

Why does a SQL JOIN duplicate rows?

A JOIN duplicates rows when the join key is not unique on one or both sides. Every matching pair becomes a row, so a parent row is copied once per child row — and SUM,COUNT and AVG then run over the copies.

Quick answer

Check the number of rows per join key before you join. If one side repeats the key, the other side's columns are repeated too. Fix the query by joining at the grain you want: pre-aggregate the many side, use EXISTS when you only filter, or join on a genuinely unique key. DISTINCT usually hides the symptom instead of repairing the grain.

A concrete example: 2 orders, 3 payments, 3 shipments

The lab below uses one parent table and two child tables. orders.order_id is unique, while payments.order_id and shipments.order_id both repeat for O-1001. Walk the steps and watch the returned row count, the distinct order count and SUM(order_amount) change together.

orders · parent2 rows, order_id unique

Each row is one order with its own order_amount.

payments · child3 rows, order_id repeats 2× for O-1001

Each row is one payment; nothing prevents several payments per order.

shipments · child3 rows, order_id repeats 2× for O-1001

Each row is one shipment; joining both children on the same key crosses them.

Joined result0 rows

No rows yet: the query has not joined the tables.

ordersone row per order · order_id unique

orders
order_id	amount
O-1001	$300
O-1002	$500

paymentsone row per payment · order_id repeats

payments
payment_id	order_id	paid_amount	paid_on
P-01	O-1001	$100	2026-02-01
P-02	O-1001	$100	2026-02-01
P-03	O-1002	$200	2026-02-03

shipmentsone row per shipment · order_id repeats

shipments
shipment_id	order_id	carrier
S-01	O-1001	DHL
S-02	O-1001	UPS
S-03	O-1002	DHL

Why the duplication happens

A JOIN is not a "paste columns together" operation. It is a matching operation: for every pair of rows where the ON condition is true, the database emits one output row. The database does not know that order_amount describes an order rather than a payment.

One-to-many: the parent is copied

When the parent key is unique and the child key repeats, one parent row matches several child rows. The parent columns are copied into each match:

-- orders: 1 row for O-1001, order_amount = 300
-- payments: 2 rows for O-1001
SELECT *
FROM orders AS o
JOIN payments AS p ON p.order_id = o.order_id
WHERE o.order_id = 'O-1001';

-- 2 rows: order_amount = 300 appears twice

Many-to-many: two repeating keys multiply

When both sides repeat the same key — or you join two child tables on that key — the matches cross each other. In the lab, joining payments and shipments onorder_id gives O-1001 2 × 2 = 4 rows, and its order amount is copied four times.

The row count is a property of the data, not of the query syntax

JOIN never reports an error for this. The only way to see it is to compare the row count, the distinct key count and the measure you are aggregating.

How duplication shows up in your numbers

The tell is always the same: a measure whose grain is finer than the result row's grain gets summed once per matching row.

Joined result versus the actual order facts
Expression	Result	Why
`COUNT(*)`	3 (or 5 with shipments)	Counts joined rows, not orders
`COUNT(DISTINCT order_id)`	2	Counts the real orders
`SUM(order_amount)`	$1,100 (or $1,700)	Adds the copied order amount
Actual order total	$800	300 + 500, one row per order

A single inflated number is easy to miss in a dashboard. Compare the result row count with the row count of the table whose grain you meant to report: if they differ, stop and check the join key before trusting any aggregate.

Why `DISTINCT` is usually the wrong fix

DISTINCT removes duplicate result rows. It does not restore the grain you intended, and it often does not remove anything at all.

SELECT DISTINCT * still returns every joined row when any selected column differs — for example payment_id is different for the twoO-1001 payments.
Removing columns until DISTINCT collapses rows silently changes the question. The query no longer returns the payment detail you selected it for, and two genuinely identical payments collapse into one row.
SUM(DISTINCT order_amount) is not a repair. If two orders legitimately share the same amount, the second one disappears:SELECT SUM(DISTINCT order_amount) FROM orders returns 300 for two $300 orders.

DISTINCT is acceptable only when you actually want distinct combinations

If the business question is "how many distinct (order, payment) pairs exist", thenDISTINCT or GROUP BY is the right tool. It is not a way to make a measure safe while keeping a wrong join.

Fixes that keep the grain correct

Fix 1

Pre-aggregate the many side, then join

Aggregate each child table to one row per order first.
The join returns one row per order and SUM(order_amount) is safe.
Child totals such as payments_total stay separate measures.

Fix 2

Use EXISTS when you only filter

EXISTS tests the child table without adding columns.
The parent grain never changes, so no measure is copied.
Prefer NOT EXISTS over a join plus IS NULL anti-join.

Fix 3

Join on the key that is actually unique

If the real grain is (order_id, payment_id), include both columns.
Do not assume a column named like an id is unique in every table.
Confirm uniqueness with COUNT(*) versus COUNT(DISTINCT key).

Fix 4

Aggregate at the target grain after the join

Add GROUP BY order_id when the output should be one row per order.
If multiple child tables fan out, group each child in its own CTE first.
A GROUP BY alone cannot repair measures copied by a wrong join.

The right fix depends on the target grain. Decide what one output row must represent, then choose the join shape that produces exactly that — this is the same decision described indata warehouse grain.

Edge cases and trade-offs

Intentional fan-out. A line-item fact table is supposed to have one row per line item. Fan-out is a bug only when the result row's grain is finer than the measure you aggregate.
LEFT JOIN with no match. An unmatched parent produces one row with NULL child columns; it does not duplicate, but later aggregations must handle NULL.
Two child tables on the same key. Joining both in one query creates a cross product per key. Aggregate each child to the target grain in separate CTEs and join the CTEs.
Pre-aggregation cost. Pre-aggregating scans the child table and can be more expensive than sorting for DISTINCT, but it produces a correct result. Measure both on realistic data.
Data model choice. If duplicated joins keep appearing, the model may be missing a key or a bridge table rather than a query hint.