Practical SQL Guide · MySQL 8.0

Why JOIN creates duplicates or missing rows

Diagnose JOIN row-count changes through result grain, key uniqueness, and unmatched rows.

Problem and reasoning

The first JOIN decision is not INNER versus LEFT; it is the result grain. State whether one output row represents a customer, an order, or a customer-order pair. That contract determines whether extra rows are expected expansion or accidental multiplication.

Join-key cardinality controls repetition. A one-to-many join repeats the one-side row for each match, while a many-to-many join multiplies matches on both sides. Repeated customer details can therefore be correct at order grain but dangerous if customer totals are aggregated afterward.

Missing rows usually come from join type or predicate placement. INNER JOIN removes unmatched rows. LEFT JOIN preserves them until a WHERE predicate on the right table rejects the resulting NULL values. Compare total rows, COUNT(DISTINCT business_key), and unmatched right-side keys while debugging.

For one row per customer, aggregate orders to customer grain before joining or group explicitly after the join. For one row per order, keep the order key and accept repeated customer attributes.

Do not use DISTINCT as a default repair. It only removes identical selected rows, can hide an incorrect relationship, and cannot prevent different order amounts from being counted more than once.

Examples

Sample table

Table：customers

customers
id	name
101	Alice
102	Bob
103	Cindy

Table：orders

orders
customer_id
101
102
101
104

Before / Not recommended

SELECT c.id, c.name FROM customers c JOIN orders o ON c.id = o.customer_id ORDER BY c.id;

Before result

result
id	name
101	Alice
101	Alice
102	Bob

After / Recommended

SELECT c.id, c.name, COUNT(o.id) AS order_count FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name ORDER BY c.id;

After result

result
id	name	order_count
101	Alice	2
102	Bob	1
103	Cindy	0

Why it is better：The first query is one row per matched order, so Alice appears twice and Cindy disappears. The second defines one row per customer and aggregates order count to that grain.