Why is adding SELECT DISTINCT to make duplicate rows disappear a performance smell?

## What the keyword actually asks for `SELECT DISTINCT` returns only unique rows of the **whole select list** — the full tuple, not one column. There is no way to guarantee that without comparing every produced row against the others, so the engine either sorts the result and collapses adjacent equal rows, or builds a hash table keyed on the entire row. Either way the work sits on top of everything the query already did, because duplicate elimination is logically the last step before `ORDER BY`. That ordering is the whole point. The joins run first and produce whatever they produce; only then does the dedup see the rows. If a join fans 50,000 orders out to 5 million rows, `DISTINCT` processes 5 million rows to hand you back 50,000. The row count you see in the result tells you nothing about the row count the engine had to build. When the implementation is a sort, it is also a blocking step: the first row cannot be returned until the last input row has been read, so a query that could have streamed now has the latency of its entire result set. ## Where the duplicates came from Duplicates in a join result are not random noise, they are arithmetic. Joining a parent to a one-to-many child multiplies each parent row by the number of matching children: ```sql -- one row per order_item, not per order SELECT o.id, o.customer_id, o.total FROM orders o JOIN order_items i ON i.order_id = o.id WHERE i.sku = 'ABC-1'; ``` If an order contains that SKU three times, its row appears three times. Other common sources: an incomplete join predicate (joining on `customer_id` when the key is `(customer_id, region)`), a join through a bridge table, or a versioned table where you forgot to pick the current row. ## Why the reflex is a smell Because it patches the end of the pipeline for a defect introduced at the beginning, and it silences only one of that defect's symptoms. Consider what happens when the same query grows an aggregate: ```sql SELECT DISTINCT o.id, o.total, SUM(i.qty) AS qty -- still wrong ``` The fan-out that duplicated `o.total` will also double-count it in any `SUM(o.total)` you add later, and `DISTINCT` cannot help there — it only collapses rows that are identical in every selected column. A query carrying an unexplained `DISTINCT` is therefore a query nobody has reasoned about, and the next person to extend it inherits a landmine. There is a second, quieter cost: `DISTINCT` makes the query's correctness depend on the select list. Add one more column with a differing value and the duplicates come back, because the rows are no longer identical. ## Removing the duplicates at the source Three rewrites cover almost every case. **The join only tests existence.** If the child table contributes no columns to the output, do not join to it: ```sql SELECT o.id, o.customer_id, o.total FROM orders o WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.id AND i.sku = 'ABC-1'); ``` This returns each order once by construction — there is nothing to deduplicate, because no extra rows were ever produced. **You need a value from the many side.** Aggregate it first, then join one-to-one: ```sql SELECT o.id, o.total, x.qty FROM orders o JOIN (SELECT order_id, SUM(qty) AS qty FROM order_items GROUP BY order_id) x ON x.order_id = o.id; ``` **The join predicate is incomplete.** Add the missing key column or the missing filter that selects one row per parent. This is the case where `DISTINCT` is most dangerous, because it makes a genuinely wrong join look right. ## When DISTINCT is the right answer When uniqueness is the question, not a repair. "Which cities do we ship to" is a set-valued question and `SELECT DISTINCT city FROM addresses` states it directly; the dedup is the work you asked for, over a narrow projection. `DISTINCT` is also fine as a deliberate, commented decision when the alternative rewrite is genuinely more complex and the intermediate row count is small. ## Reviewing a query that already has one Delete the keyword and run the query. If the row count is unchanged, it was dead weight — remove it. If duplicates appear, count them: compare the result's row count with the count of distinct keys of the driving table, and look for the join that multiplies. Then decide which of the three rewrites applies. Keep `DISTINCT` only when you can say in one sentence why uniqueness is part of the requirement rather than part of the cleanup.