What does GROUP BY do when the SELECT list has no aggregate, and how does it compare with SELECT DISTINCT?
answer
- an aggregate is not required
- each group still emits its key
- same rows as deduplicating those columns
- NULLs fold together in both forms
- only one of them can count group members
basics
~20 sGrouping still happens: rows are partitioned by the key and each group emits its key, so the result is the distinct key combinations — the same row set SELECT DISTINCT would give over those columns. GROUP BY additionally supports aggregates and group-level filtering.
solid answer
~50 s`SELECT region FROM customers GROUP BY region` partitions the rows and projects one key per group, so its result is exactly the set of distinct regions — the same rows as `SELECT DISTINCT region FROM customers`, including a single row for NULL, and equally unordered in both cases. The difference is what each form can go on to express. `GROUP BY` has named groups, so it can add aggregates (`COUNT(*)`, `SUM(amount)`), filter on them with `HAVING`, and key on an expression. `SELECT DISTINCT` has no notion of a group — it removes duplicates from whatever the select list already produced, so it cannot filter by group size or report anything about the collapsed rows. Style follows intent: use `DISTINCT` when the job is literally "remove duplicate rows", and `GROUP BY` when the row set is a set of *groups* you are or may be summarising. How the engine executes either is its own business.
code
sql · 3 lines-- Same row set: the distinct regions, with one row for NULL in both
SELECT region FROM customers GROUP BY region;
SELECT DISTINCT region FROM customers;go deeper
Know that GROUP BY works without any aggregate and then returns the distinct key values, matching SELECT DISTINCT on the same columns. Do not assume an aggregate is mandatory in a grouped query.
Explain the equivalence and its limits: identical rows and identical NULL folding in the simple case, but only GROUP BY names groups, so aggregates, HAVING and expression keys are available to it alone.
Show intent-driven choice in review: DISTINCT for genuine dedup, GROUP BY for per-key reporting, and suspicion of a DISTINCT added to silence duplicates that a join created — since the underlying multiplication still corrupts any aggregate added later.
Set the convention and the reasoning behind it, so a codebase does not carry both idioms at random. Consistency here is mostly about readability and about not letting DISTINCT become the standard way of papering over modelling errors.
## GROUP BY without an aggregate is still grouping Nothing in SQL requires a grouped query to call an aggregate function. Write ```sql SELECT region FROM customers GROUP BY region; ``` and the engine still partitions the filtered rows by `region` and emits one row per group. Since the only projected column is the grouping key, the output is precisely the set of distinct region values. In that narrow case the query is a duplicate-elimination query written with grouping syntax. ## Where it lines up with SELECT DISTINCT `SELECT DISTINCT region FROM customers` produces the same row set, and the agreement is not a coincidence: duplicate elimination and grouping use the same value-comparison rule, under which two NULLs are *not distinct* from each other. So both forms return one row for the NULL region rather than one per NULL row, and neither guarantees any ordering. Extend it to several columns and the correspondence holds: ```sql SELECT region, status FROM orders GROUP BY region, status; SELECT DISTINCT region, status FROM orders; ``` Both give one row per `(region, status)` combination present in the data. ## Where they diverge The forms stop being interchangeable as soon as the query does anything beyond projecting the keys. **Aggregates.** Grouping names a set of rows, so the select list can summarise it: `SELECT region, COUNT(*) FROM customers GROUP BY region`. `DISTINCT` has no group to summarise; it only sees the rows the select list produced. **Group-level filtering.** `HAVING COUNT(*) > 1` is available to a grouped query and has no equivalent under `DISTINCT`. "Which emails occur more than once?" is a grouping question, and trying to express it with `DISTINCT` fails because duplicates have already been thrown away by the time you could count them. **Keying on something you do not project.** `GROUP BY` can group on an expression and project a different one, so long as what you project has a single value per group. `DISTINCT` applies to the finished select list — it deduplicates exactly the tuple of expressions you asked for, no more and no less. **Scope of the deduplication.** `DISTINCT` is a property of the whole select list. Adding a column to a `SELECT DISTINCT` can multiply the rows, because a wider tuple is more likely to be unique. That surprises people who read `DISTINCT` as attaching to the first column. Grouping is explicit about which columns define identity: they are the ones in the `GROUP BY` list. **Position in the pipeline.** Grouping happens before the select list is finally evaluated and before `HAVING`; `DISTINCT` is applied to the select list's output. That is why `DISTINCT` can deduplicate on computed expressions with no extra ceremony, while grouping requires the grouping expression to be stated in the `GROUP BY` clause. ## Choosing between them Because the two forms are interchangeable in the simple case, the choice is about communicating intent to the next reader: - Use `SELECT DISTINCT` when the sentence in your head is "the distinct values of X" — a lookup list, a set of ids to feed elsewhere, deduplicating rows that a join fanned out. - Use `GROUP BY` when the sentence is "per X" — even if today you project only X. It signals that the row set is a set of groups, and it leaves the door open for a count or a `HAVING` clause later without restructuring the query. One caution about `DISTINCT` as a habit: reaching for it to clean up unexpected duplicates often hides a real defect, typically a join that multiplied rows. The duplicates disappear from the row set, but any aggregate you later add still sees the multiplied rows. Diagnose why duplicates exist before deduplicating them away. ## Performance is not the deciding factor Candidates often assert that one form is faster. Choosing an access path and an operator for either form is the engine's job, and the two formulations describe the same result. Decide on clarity, and if a specific query is slow, investigate that query rather than swapping the keyword and hoping. ## Summary A grouped query with no aggregate emits the distinct key combinations, matching `SELECT DISTINCT` on the same columns, NULL handling included. `GROUP BY` is the more capable form because it names groups — aggregates and `HAVING` follow from that — while `DISTINCT` is the plainer statement of "remove duplicate rows from this select list".
- Which question can GROUP BY answer that SELECT DISTINCT cannot?Anything about the collapsed rows. "Which emails appear more than once?" needs GROUP BY email HAVING COUNT(*) > 1, because grouping names a set of rows it can count. DISTINCT discards duplicates before anything can measure them, so group size is unavailable to it.
- Does adding a column to SELECT DISTINCT ever increase the row count?Yes, frequently. DISTINCT deduplicates the whole select-list tuple, so a wider tuple is more likely to be unique. Adding a column with many values to SELECT DISTINCT region can turn a handful of rows into thousands, which surprises anyone reading DISTINCT as attaching only to the first column.
- Is one of the two forms faster?That is not a language-level property. The two formulations describe the same result set, and choosing an operator and access path for either is the engine's job. Pick the form that states your intent, and if a particular query is slow, investigate that query rather than swapping keywords.
saying these in an interview costs you the question
- Thinks GROUP BY requires an aggregate function in the select list
- Claims DISTINCT keeps a separate row for each NULL
- Believes DISTINCT applies only to the first selected column
- Says one of the two forms is universally faster
- Uses DISTINCT to hide duplicates from a join instead of fixing them