In SELECT DISTINCT city, state FROM addresses, what exactly does DISTINCT deduplicate?
answer
- It is a quantifier, not a function
- Look at the entire select list
- Every selected column is compared together
- Extra columns can only add rows
basics
~10 sDISTINCT applies to the whole select list, not to one column. SELECT DISTINCT city, state returns every unique city-and-state combination, so one city appears repeatedly if it occurs with several states.
solid answer
~50 s`DISTINCT` is a set quantifier on the entire select list — the alternative is the default `ALL`. The engine evaluates every expression in the select list first, then removes rows whose values match in *every* selected column. So `SELECT DISTINCT city, state` returns unique `(city, state)` pairs: `('Springfield','IL')` and `('Springfield','MO')` are two different rows and both survive. A direct consequence is that adding a column to a `DISTINCT` select list can only keep the row count the same or raise it, never lower it. `DISTINCT` is also not a function: `SELECT DISTINCT (city), state` parses as `DISTINCT` applied to the list `(city), state`, so the parentheses change nothing. If you truly want one row per city, you need `GROUP BY city` with an aggregate for the other columns, or a row-numbering pattern to pick a representative row.
go deeper
Be ready to state in one sentence that DISTINCT compares the whole output row, and to predict the row count of a two-column DISTINCT over a small sample table.
Explain the mechanics: the select list is evaluated first, deduplication uses the full tuple, and widening the list can only keep or increase the row count. Know that DISTINCT is a quantifier, not a function.
Show judgment about when DISTINCT is the wrong tool — when the requirement is one row per key with a chosen representative value, name the grouping or ranking rewrite instead of reaching for the keyword.
Own the review standard: a DISTINCT in a query is a claim about the result's grain. Push teams to state that grain explicitly in the query shape rather than encoding it in a keyword nobody re-derives later.
## What DISTINCT is The select clause is written `SELECT [ ALL | DISTINCT ] <select list>`. `ALL` is the default and means "keep every row, duplicates included". `DISTINCT` is the opposite quantifier: after the select list has been evaluated for each qualifying row, rows that are duplicates of one another are collapsed so each surviving combination of values appears exactly once. The crucial word is *combination*. `DISTINCT` is a modifier of the whole select list, not of the column that happens to follow it. Two output rows are duplicates only when they agree in **every** selected column. ## The whole-row rule, worked Given an `addresses` table containing `('Springfield','IL')`, `('Springfield','MO')` and `('Springfield','IL')`: ```sql SELECT DISTINCT city, state FROM addresses; -- ('Springfield','IL') -- ('Springfield','MO') ``` Two rows, not one. `'Springfield'` is repeated because the pair it belongs to differs. Candidates who read the query as "distinct cities, plus the state" expect one row and are surprised in production when a report shows the same name several times. If you only want the cities: ```sql SELECT DISTINCT city FROM addresses; -- ('Springfield') ``` One row. Nothing about the query changed except the width of the select list — and that is the whole point. ## Monotonicity: more columns, never fewer rows Because deduplication happens on the full tuple, widening the select list refines the key it deduplicates on. Adding a column can only leave the row count unchanged (if the new column is functionally determined by the others) or increase it. It can never reduce it. This is a useful sanity check: if someone "fixes" a duplicate-rows complaint by selecting more columns, the duplicates will get worse, not better. The extreme case is a primary key. `SELECT DISTINCT * FROM t` where `t` has a primary key is a guaranteed no-op — every row is already unique — so the `DISTINCT` is pure cost with zero effect. ## DISTINCT is not a function A very common habit is to write `SELECT DISTINCT(city), state FROM addresses`, as if `DISTINCT` were a function taking one argument. SQL has no such function. The parser sees the keyword `DISTINCT`, then a select list whose first element is the parenthesised expression `(city)` — and parentheses around a single column reference mean nothing. The query behaves exactly like `SELECT DISTINCT city, state`. The syntax is legal, which is why the misconception survives: nothing errors, the results just aren't what the author believed. ```sql SELECT DISTINCT (customer_id), order_date FROM orders; -- same as DISTINCT customer_id, order_date ``` ## Where DISTINCT sits in the query Logically, rows are produced by `FROM` and `WHERE` (and grouping, if present), then the select-list expressions are evaluated, then `DISTINCT` removes duplicates, and only then does `ORDER BY` sort and `LIMIT`/`FETCH FIRST` truncate. Two consequences follow. First, deduplication sees the *projected* values, so `SELECT DISTINCT UPPER(city)` dedups on the uppercased strings and can return fewer rows than `SELECT DISTINCT city`. Second, `DISTINCT` never influences which rows the `WHERE` clause keeps — it only compresses what the select list already produced. ## Getting one row per key When the requirement really is "one row per city, plus some information about it", `DISTINCT` is the wrong tool because it has no way to choose among the competing values of the other columns. The portable answers are: ```sql -- one row per city, with a chosen representative value SELECT city, MAX(created_at) AS latest_signup FROM users GROUP BY city; ``` or a ranking pattern that numbers rows within each city and keeps number 1. Both make explicit the decision that `DISTINCT` silently cannot make. ## Mistakes to avoid Assuming `DISTINCT` binds to the first column; reading `DISTINCT(x)` as a function call; expecting a wider select list to remove more duplicates; and sprinkling `DISTINCT` on queries whose rows are already unique, where it adds deduplication work for no result change. In every case the fix is the same sentence: `DISTINCT` compares the entire output row.
- What is the default if you write neither DISTINCT nor ALL?`ALL` is the default set quantifier: every qualifying row is returned, duplicates included. `SELECT city FROM addresses` and `SELECT ALL city FROM addresses` are the same query. Writing `ALL` explicitly is legal and occasionally used for documentation, but it is rare in practice.
- Does SELECT DISTINCT * ever change the result of a query over a table with a primary key?No. A primary key guarantees every row differs in at least one column, so no two rows of `SELECT *` can be duplicates and the deduplication removes nothing. The keyword is then pure overhead, and its presence usually signals a copy-pasted habit rather than a deliberate choice.
- How does DISTINCT behave when the select list contains an expression rather than a bare column?Deduplication happens on the evaluated expression values, because the select list is computed before duplicates are removed. `SELECT DISTINCT UPPER(city)` can therefore return fewer rows than `SELECT DISTINCT city` — `'Boston'` and `'BOSTON'` are two distinct cities but one distinct uppercased value.
DISTINCT filters printed lines, not individual words on them: two lines count as the same only when every word matches, so one repeated word is not enough to drop a line.
saying these in an interview costs you the question
- Thinks DISTINCT applies only to the column right after it
- Reads DISTINCT(col) as a function call on that column
- Expects one row per city from SELECT DISTINCT city, state
- Adds columns to a DISTINCT query hoping for fewer rows
- Believes DISTINCT filters rows before WHERE runs