Why SQL Joins Multiply Rows: Diagnose and Fix the Grain

A join can multiply valid rows

A customer with two orders and three payments produces six combinations when both fact tables are joined by customer. The records need not be duplicates. The error is asking a six-row result to behave like one customer-level summary. This tutorial uses a small PostgreSQL 16 fixture to expose the inflated totals, then fixes the grain before joining. All queries use fictional VALUES data and change no stored tables.

See the multiplication before hiding it

Customer 1 has two different orders worth 10 each and three payments worth 5, 8, and 7. Correct order revenue and payments are both 20. Customer 2 has one order and one payment, each worth 7. Customer 3 has neither. Run this deliberately incorrect aggregation:

WITH customers(customer_id) AS (VALUES (1),(2),(3)),
orders(order_id, customer_id, amount) AS (
 VALUES (11,1,10),(12,1,10),(13,2,7)
), payments(payment_id, customer_id, amount) AS (
 VALUES (21,1,5),(22,1,8),(23,1,7),(24,2,7)
)
SELECT c.customer_id, COUNT(*) AS joined_rows,
       SUM(o.amount) AS order_total, SUM(p.amount) AS payment_total
FROM customers c
LEFT JOIN orders o USING (customer_id)
LEFT JOIN payments p USING (customer_id)
GROUP BY c.customer_id ORDER BY c.customer_id;

customer_id | joined_rows | order_total | payment_total
1 | 6 | 60 | 40
2 | 1 | 7 | 7
3 | 1 | NULL | NULL

The two left joins retain customer 3 as a null-extended row; COUNT(*) therefore reports one joined row, not one order. Customer 1 repeats each order three times and each payment twice. Adding more independent detail tables can multiply the problem again. The PostgreSQL join reference describes how matches and unmatched outer-join rows are formed.

Define one row in every input and output

Here the requested output is one row per customer with two independent totals. Orders are one row per order; payments are one row per payment. Neither customer_id column is unique in its fact table. Do not infer one-to-one relationships from column names or a small sample: inspect constraints and counts by key. If the requirement were one row per order-payment allocation, six rows might be correct only if the data explicitly represented those allocations.

DISTINCT cannot infer the intended amounts

SELECT DISTINCT over the final three-column aggregate changes nothing because the aggregate already has one row per customer. SUM(DISTINCT o.amount) is also wrong: customer 1 has two legitimate 10-unit orders, so it returns 10 instead of 20. Distinct monetary values are not distinct transactions. Removing an order ID from a projection can make different orders look identical without making either one erroneous.

Aggregate independent facts before joining

WITH customers(customer_id) AS (VALUES (1),(2),(3)),
orders(order_id, customer_id, amount) AS (
 VALUES (11,1,10),(12,1,10),(13,2,7)
), payments(payment_id, customer_id, amount) AS (
 VALUES (21,1,5),(22,1,8),(23,1,7),(24,2,7)
), order_totals AS (
 SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS revenue
 FROM orders GROUP BY customer_id
), payment_totals AS (
 SELECT customer_id, SUM(amount) AS payments
 FROM payments GROUP BY customer_id
)
SELECT c.customer_id, COALESCE(o.orders,0) AS orders,
       COALESCE(o.revenue,0) AS revenue,
       COALESCE(p.payments,0) AS payments
FROM customers c
LEFT JOIN order_totals o USING (customer_id)
LEFT JOIN payment_totals p USING (customer_id)
ORDER BY c.customer_id;

customer_id | orders | revenue | payments
1 | 2 | 20 | 20
2 | 1 | 7 | 7
3 | 0 | 0 | 0

Each summary now contributes at most one matching row per customer. Zero means no matching fact rows in this complete teaching fixture. If a real payment feed is missing or late, zero may be a false claim; expose freshness or an unknown status separately. Do not use pre-aggregation to paper over an incorrect customer mapping.

Choose the reduction that matches the question

First isolate one problematic key and inspect raw IDs on both sides. Predict its joined row count, then compare it with the actual result. Next choose a remedy: aggregate when you need a total, use EXISTS when you only need to know whether a matching row exists, or select a deterministic latest row when the requirement explicitly names a current status. If a relationship should be one-to-one, fix the missing key condition or invalid source records instead of arbitrarily selecting a winner.

For this fixture, the final revenue sums to 27, payments sum to 27, and the output has three customers. Check those totals against the independent source tables. Also test a customer with no matches, equal-valued transactions, and several matches on both sides. A join that looks correct for customer 2 can still be wrong for customer 1.

Separate bad data from valid repeated keys

Two rows sharing customer_id are usually expected. Two source events sharing a supposedly unique event ID may instead be an ingestion defect. Document the business key and winner rule before deduplicating. For that separate task, see DISTINCT versus ROW_NUMBER. A customer-level join cannot decide which source event is authoritative.

Verify counts at the intended grain

The reliable fix is to make each input match the requested relationship before combining it. Preserve valid detail when the output needs that detail; reduce it explicitly when the output needs one row per entity. Check both row counts and measures, because a single final row can still contain an inflated total.