A report uses INNER JOIN plus SELECT DISTINCT only to filter — what does that cost at scale?

## How the pattern gets written Nobody sets out to write this. It arrives incrementally: someone joins `customers` to `orders` to filter to active buyers, notices repeated customers in the output, adds `DISTINCT` to make the report look right, and ships. The query is correct. It is also doing several times more work than the question requires, and the amount of extra work grows with the data. ## What the join costs An inner join produces one row per matching pair. If the parent table has N rows and each matched parent averages k children, the intermediate is on the order of N x k rows. Every one of those rows carries the **full projected width** of the select list — if the report selects a customer's name, address and notes column alongside, each of the k copies drags all of it along. That intermediate has to be produced, and it has to be moved between operators. ## What the DISTINCT costs `SELECT DISTINCT` is not a filter applied row-by-row; it is a blocking operator. To know that a row is not a duplicate, the engine must compare it against everything else, which in practice means sorting the intermediate or building a hash table over it. Two consequences follow: 1. **Nothing is emitted until the input is exhausted.** The query's time-to-first-row becomes its time-to-last-row. For an interactive report or a `LIMIT`ed page, that is the difference between fast-feeling and unusable. 2. **It needs memory proportional to the intermediate, at full row width.** When the operator's memory budget is exceeded, it spills to temporary storage and the cost jumps from CPU-bound to I/O-bound. This is a classic "it got slow all at once" cliff: the query is fine right up until the working set crosses the threshold. There is also a correctness edge worth naming: `DISTINCT` compares the entire select list. It does not know which duplicates the join manufactured and which were legitimately in your data, so it removes both. If two genuinely distinct business rows happen to be identical across the projected columns, the report silently loses one. ## The rewrite When the child table contributes nothing to the output, the query is a semi-join, and SQL has a direct spelling for it: ```sql -- before: build N x k pairs, then dedup them away SELECT DISTINCT c.customer_id, c.name, c.notes FROM customers c JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'PAID'; -- after: one row per customer, no intermediate, no dedup SELECT c.customer_id, c.name, c.notes FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status = 'PAID' ); ``` The rewritten form never produces the pairs, so there is nothing to deduplicate, no blocking operator, no spill risk, and no chance of collapsing legitimate duplicates. Note that the child-side predicate (`o.status = 'PAID'`) moves inside the subquery, where it belongs. ## When the join is the right idiom after all The rewrite only applies when the child contributes nothing to the select list. Two cases keep the join: - **The join key is unique on the child side.** A one-to-one relationship, or a foreign key referencing a unique key, produces no fan-out at all — each parent matches at most one child. Then `DISTINCT` is pure waste and should simply be deleted, not rewritten around. A `DISTINCT` in a plan whose output is already unique is exactly the signal to check this. - **You need child columns or aggregates.** Then reduce the child side to one row per parent *before* it reaches the outer query: aggregate in a derived table or CTE and join to that, so the fan-out never happens and no dedup is needed. ## Diagnosing it in the wild In a plan, look for a sort or hash-aggregate node sitting directly above a join whose output row count is far larger than the query's final row count. That ratio *is* the waste. In an execution-time plan, the same node is where the time goes, and where a spill to temporary storage will be reported if it happened. A useful review heuristic: **every `SELECT DISTINCT` deserves the question "which join put the duplicates there, and does that join need to exist?"** Most of the time the honest answer is that it does not, and the fix is a semi-join rather than an index, a hint, or more memory. ## Why this matters more as systems grow The fan-out factor k is not a constant of your schema; it is a property of your data, and it grows. Customers accumulate orders, documents accumulate revisions, tenants accumulate events. A query written when k was 3 quietly becomes a query where k is 300, and the DISTINCT that was free becomes the dominant cost and then a spilling one. That is why this belongs in review rather than in later tuning: the semi-join version simply does not have the growth term.