How does GROUP BY treat rows whose grouping key is NULL, and how many groups do they form?

## Grouping does not use = The rule that surprises people is that `GROUP BY` and `WHERE` compare values by different rules. A `WHERE` predicate uses comparison operators and three-valued logic: `region = NULL` is UNKNOWN, and a row whose predicate is UNKNOWN is not kept. Grouping instead asks whether two key values are **distinct**, and the standard states that two NULLs are *not distinct* from one another, while a NULL and any non-NULL value *are* distinct. Consequences: - All rows with a NULL key belong to exactly one group. - That group is a real group like any other: it appears in the output with NULL in the key column and an aggregate computed over its rows. - The NULL group is never silently dropped. Only a `WHERE region IS NOT NULL` (or an inner join that eliminates those rows) removes it. ## Worked example ```sql -- customers(id, region): 5 rows with region NULL, 3 with 'EU' SELECT region, COUNT(*) AS n FROM customers GROUP BY region; -- 2 rows: (NULL, 5) and ('EU', 3) ``` Two rows, not six. If NULLs were compared with `=`, no two NULL rows would ever match and you would get one group per NULL row — that is the intuition candidates often bring, and it is wrong. ## Multi-column keys The comparison is per position across the key tuple. Two rows share a group when *every* key position is not distinct: | region | status | group | |--------|--------|-------| | NULL | paid | A | | NULL | paid | A | | NULL | refunded | B | | EU | paid | C | Three groups. NULLs match NULLs position by position, and a NULL never matches a non-NULL. ## Reading a NULL group in a report A NULL row in a grouped report is ambiguous to a human reader for three separate reasons, and it is worth knowing which one you are looking at: 1. **Missing source data** — the key column was genuinely NULL for those rows. This is the case discussed here. 2. **Outer-join extension** — the rows came from the unmatched side of an outer join, so the key was NULL-extended rather than stored as NULL. 3. **Super-aggregate placeholders** — if the query uses `ROLLUP` or `CUBE`, the subtotal rows carry NULL in the columns they are aggregating over. Telling those apart from data NULLs is exactly what the `GROUPING()` function is for. In a plain `GROUP BY` only case 1 and case 2 are possible, and both mean "these rows had no key value at grouping time". ## Labelling the group The usual production fix is to give the group a readable label: ```sql SELECT COALESCE(region, 'unknown') AS region_label, COUNT(*) AS n FROM customers GROUP BY COALESCE(region, 'unknown'); ``` Two cautions come with that idiom. First, you are now grouping by an expression, so the same expression must appear in `GROUP BY` — grouping by the raw column while displaying the coalesced value would still key on the raw NULL and merely relabel the output, which is usually what you want, but you must be deliberate about it. Second, the label collides: if `'unknown'` already exists as a stored value, the coalesce merges genuine unknown-region rows with genuinely-NULL rows into one indistinguishable group. Pick a sentinel that cannot occur in the data, or keep the NULL and format it in the presentation layer. ## When the NULL group is a bug signal A NULL group appearing where you did not expect one is a useful alarm. It usually means one of: - a column that the domain assumes is mandatory is not declared `NOT NULL`, and rows have slipped through; - an upstream load left a foreign key unset; - a join you thought was inner is actually outer, so unmatched rows arrived NULL-extended. In each case the interesting work is upstream of the query. Deleting the NULL group with `WHERE region IS NOT NULL` hides the count and makes the report's total disagree with the raw row count — a classic source of "the numbers don't add up" complaints. ## The one-line summary For grouping (and for duplicate elimination, which uses the same rule) NULLs are treated as equal to each other; for predicates they are not. Knowing which of the two rules a clause uses is the whole answer.