Why can GROUP BY on a primary key let you select non-grouped columns of that table?

## The rule, and the hole the standard punched in it The baseline rule for a grouped query is that each select-list item must be a grouping expression or an aggregate, because an ungrouped column can hold many values inside one group. SQL:1999 noticed that this is sometimes provably not the case, and added a relaxation: a column may be selected bare if it is **functionally dependent** on the grouping columns. ## What functional dependence means here Column `B` is functionally dependent on column `A` when knowing `A` fixes `B` — one value of `A` never coexists with two different values of `B`. A **primary key** is the canonical source of such a dependency: `users.id` uniquely identifies a row of `users`, so `users.email`, `users.created_at` and every other column of that table are determined by it. If you group by `u.id`, each group corresponds to a single `users` row, so `u.email` has exactly one value in the group — the ambiguity the rule guards against cannot arise, and the standard lets you select it. ## The query this actually helps The common shape is a parent table joined to a child table and counted: ```sql SELECT u.id, u.email, u.created_at, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id; ``` Without the relaxation you must write `GROUP BY u.id, u.email, u.created_at` — and add another column to the `GROUP BY` every time you add one to the select list. With it, grouping by the key alone suffices, and adding `u.display_name` to the select list requires no other edit. On a wide parent table the difference in readability is real. Note the join does not weaken the argument. The join may multiply rows (one per order), but every row of the result that shares `u.id` carries the *same* `u.email`, because they all originate from the same `users` row. The dependency is a property of the base table and survives the join. ## Where it stops The relaxation is far narrower in practice than the abstract rule suggests. - **PostgreSQL recognizes only the primary-key case.** Its documentation is explicit that functional dependence is recognized when the grouping columns include the primary key of the table containing the ungrouped column. Grouping by a `UNIQUE NOT NULL` column that happens to determine the others is not recognized, even though it determines them just as well. - **Many engines implement none of it** and still require every select-list column to be listed in `GROUP BY`. A query that runs on one engine can fail outright on another. - **It does not cross tables the way people hope.** Grouping by `o.user_id` (a foreign key) does not license selecting `u.email` in every implementation; the dependency the engine reasons about is keyed to the table that owns the ungrouped column. - **It does not apply to expressions.** Grouping by `u.id` does not let you select `COUNT(*) / SOME_UNGROUPED_COLUMN`; only column references of the dependent table are covered. ## Portability advice Two defensible policies: 1. **Use it deliberately** on a codebase pinned to an engine that implements it, and note in the query's comment that it depends on the primary-key relaxation. 2. **Spell out every column** in `GROUP BY`. Verbose, but valid everywhere and immune to a future engine change. Since the columns are functionally dependent, listing them cannot change the result — you get the same groups either way. A third pattern avoids the question entirely: aggregate first, then join the parent for its columns. ```sql SELECT u.id, u.email, COALESCE(c.order_count, 0) AS order_count FROM users u LEFT JOIN (SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id) c ON c.user_id = u.id; ``` Here nothing is grouped and projected in the same block, so the select-list rule never binds on `u.email` at all. ## Interview framing The answer that lands says three things: what functional dependence is, why a primary key supplies it (one row per group, hence one value per column), and that support is engine-dependent so the explicit `GROUP BY` list is the portable choice. Candidates who only say "Postgres lets you do it" have the fact but not the reason.