When does adding DISTINCT to a JOIN fail to reproduce the EXISTS semi-join's result?

## Two operations that look alike `EXISTS` filters. `DISTINCT` de-duplicates. On a well-behaved schema — join a keyed left table to a child table and project only the key — they happen to produce the same rows, which is why `SELECT DISTINCT` over a join is such a common substitute for a semi-join. The equivalence is a coincidence of the data, not a property of the operators, and a senior candidate is expected to know where the coincidence ends. ## Case 1: the left table has real duplicates Semi-join semantics preserve the left input's multiplicity: if a left row appears three times and qualifies, three rows come out. `DISTINCT` removes multiplicity unconditionally. ```sql -- staging_emails: the same address landed twice from two files -- blocklist: one row for that address SELECT DISTINCT s.email FROM staging_emails s JOIN blocklist b ON b.email = s.email; -- 1 row SELECT s.email FROM staging_emails s WHERE EXISTS (SELECT 1 FROM blocklist b WHERE b.email = s.email); -- 2 rows ``` Which is right depends on the question, and that is the point: if you are counting how many staging rows must be rejected, the `DISTINCT` version under-reports and no error is raised. Tables without a declared key — staging areas, event logs, imported files, and any projection that omits the key — are where this bites. ## Case 2: the select list reaches the right side `DISTINCT` applies to the entire select list, after the join. Add anything that varies per match and it has nothing to collapse: ```sql -- Intent: customers who ordered, once each. Actual: one row per order. SELECT DISTINCT c.id, c.name, o.id FROM customers c JOIN orders o ON o.customer_id = c.id; ``` This is a common regression path: the query was correct when written, someone later added a column "just for debugging", and the row count silently multiplies. With `EXISTS`, adding a left-side column to the select list cannot change the row count, because the filter lives in `WHERE` and does not depend on the projection. The `EXISTS` form is robust against the edit; the `DISTINCT` form is not. ## Case 3: NULL handling inside DISTINCT `DISTINCT` compares rows treating two NULLs as the same value, so `(1, NULL)` and `(1, NULL)` collapse to one row. Equality in a join predicate does the opposite: `NULL = NULL` is UNKNOWN and never matches. So the two operators disagree about NULLs in opposite directions. When the projected columns are nullable, reason about it explicitly rather than assuming `DISTINCT` is a no-op on unique data. ## Case 4: what you can de-duplicate at all De-duplication requires comparing whole rows, so every projected column must be comparable for equality. Engines differ in which column types they will de-duplicate, and some large or structured types cannot participate. `SELECT DISTINCT big_document_column, ...` may therefore be rejected or expensive where the `EXISTS` version has no such requirement — check your engine before relying on it. ## The mechanical rewrite Whenever you see `SELECT DISTINCT` over a join, ask why the duplicates exist: - **Fan-out from a right table whose columns are not projected** — the existence test belongs in `WHERE` as `EXISTS`; delete the join and the `DISTINCT`. - **Duplicates already present in the left input** — `DISTINCT` may be exactly right, but say so deliberately. - **You want one specific matching row per left row** — neither operator expresses that; that is a top-N-per-group problem. `GROUP BY` on the left key is not a better patch than `DISTINCT`: it collapses the same rows and forces every projected column into the grouping list or into an aggregate. ## Why interviewers like this question `SELECT DISTINCT` is the single most common cover-up in production SQL. Asking when it fails to match `EXISTS` separates candidates who treat it as a formatting habit from those who can state precisely what it changes: it alters the multiset of the result rather than the filter, and altering the multiset is only harmless when the multiset was already what you wanted.