Why a JOIN duplicates rows, and how to fix cardinality

The quick answer

Decide the grain you want. For a per-customer total, aggregate the many side with SUM and GROUP BY the customer, instead of hiding the duplicates with DISTINCT.

A minimal example

SELECT a.id, a.name, b.title
FROM authors a
JOIN books b ON b.author_id = a.id;

SELECT a.id, a.name, COUNT(b.id) AS book_count
FROM authors a
JOIN books b ON b.author_id = a.id
GROUP BY a.id, a.name;

How to debug it

1. State the grain you expect: one row per customer, per order, or per day.
2. Count rows on each side of the JOIN for a known key.
3. If the result has more rows than the intended grain, the JOIN is fanning out.
4. Aggregate the many side to the intended grain rather than masking rows with DISTINCT.

Common wrong fixes

• Adding DISTINCT, which hides duplicates but still sums nothing and can drop legitimately equal rows.
• Using LIMIT to make the row count look right without fixing the grain.
• Joining on an incomplete key so some rows match by accident.

Limits of this guide

This covers one-to-many fan-out for a per-customer total. Cardinality bugs also appear with many-to-many joins and missing predicates. The challenge focuses on the fan-out case shown here.

Authoritative references

1. PostgreSQL — joined tables and GROUP BY
2. PostgreSQL — SELECT and DISTINCT
3. PostgreSQL — aggregate functions

Related pages