Why is adding SELECT DISTINCT to a join that returns duplicate rows a fragile fix?

## The situation ```sql SELECT c.customer_id, c.name FROM customers c JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'PAID'; ``` A customer with seven paid orders appears seven times. The join's output grain is one row per *order*, not per customer, because that is what joining a one-to-many relationship does. Someone notices the repeats in a report and adds `DISTINCT`. The report looks right and the change ships. ## What DISTINCT can and cannot repair `DISTINCT` removes rows that are identical in every selected column. In the query above, all seven copies carry the same `customer_id` and `name`, so they merge and the output looks correct. But nothing about the join changed — seven rows were produced and six were thrown away at the end. That makes the correctness of the query a property of its **select list**, which is the fragility. Extend the query a month later: ```sql SELECT DISTINCT c.customer_id, c.name, o.order_id FROM customers c JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'PAID'; ``` All seven rows are back, because `order_id` differs in each. The same happens with an order date, a status, an amount, or anything else from the many side. A fix that survives only while nobody adds a column is not a fix; it is a coincidence. And `DISTINCT` can only ever merge whole rows. Anything the query computes across the multiplied rows — a total, an average, a count — was already computed over inflated input, and deduplicating the final output cannot undo it. That is a separate defect with the same root cause: the wrong grain. ## The masking problem Duplicate rows are a symptom, and symptoms are useful. They tell you the join produced more rows than the question deserved — perhaps because the relationship is one-to-many, perhaps because the `ON` condition is missing a column of a composite key, perhaps because a supposedly unique reference table has duplicates in it. `DISTINCT` silences all three without distinguishing them. The third case is worth dwelling on. If a lookup table that should have one row per code has two, every join to it doubles rows. `DISTINCT` hides that until the two rows differ in some column you later select, at which point you get a real data bug reported as a mysterious report regression. Investigating the duplicates instead of suppressing them would have surfaced a broken uniqueness assumption. ## The honest rewrites **You only need "has at least one match".** Say that with a semi-join. The customer row is never multiplied in the first place, and the intent is legible: ```sql SELECT c.customer_id, c.name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'PAID' ); ``` **You need a value derived from the many side.** Reduce that side to one row per key first, then join: ```sql WITH paid AS ( SELECT customer_id, COUNT(*) AS paid_orders, MAX(order_date) AS last_paid FROM orders WHERE status = 'PAID' GROUP BY customer_id ) SELECT c.customer_id, c.name, p.paid_orders, p.last_paid FROM customers c JOIN paid p ON p.customer_id = c.customer_id; ``` Now the join is one-to-one by construction, the result grain is one row per customer, and adding a column later cannot break it. **You need the detail rows.** Then the duplicates were never duplicates — one row per order is the correct answer and no deduplication belongs in the query at all. ## When DISTINCT is the right tool This is not an argument against the keyword. `DISTINCT` is exactly right when repeated rows are a property of the data rather than an artefact of the query: enumerating the distinct values a column takes, building a dropdown list, or collapsing a genuinely duplicated feed of records. The test is whether you can state *why* duplicates exist. "Because the source has repeats and I want each value once" is a reason. "Because otherwise the numbers look wrong" is not. ## Review heuristics Treat a `DISTINCT` that appears in the same commit as a new join as a defect until proven otherwise. Ask what the intended grain of the result is and whether the query guarantees it structurally. Check whether removing the `DISTINCT` changes the row count, and if it does, find out which join multiplied rows and why. And remember that deduplication is not free — the engine must do extra work to compare and discard rows that a better-shaped query would never have produced.