SQL Semantics

GROUP BY

GROUP BY forms groups and treats NULL as its own group.

highlighted = computed this step

GROUP BY forms groups

GROUP BY partitions rows by the grouping value and computes aggregates per group. NULL forms its own group instead of disappearing. Note: the result table shows the groups.

group by\text{group by}

Grouping grades

The recomputed group count is 4. Note: the missing grade appears as its own rendered group.

groups=4\text{groups}=4

SQL bag/multiset + three-valued NULL logic, deterministic but surprising; tiny finite tables; no engine/perf claims. Set algebra is Book 1.

GROUP BY gradegroup_byarity 2 rows 4Gradesarity 3 rows 5
group_bygraden701801902NULL1

The NULL group is real

The NULL group has COUNT star 1 and COUNT grade 0. The duplicated-grade group has COUNT star 2. Note: grouping keeps the NULL bucket even though COUNT grade skips its NULL value.

NULL count star=1,NULL count grade=0\text{NULL count star}=1,\quad \text{NULL count grade}=0

SQL bag/multiset + three-valued NULL logic, deterministic but surprising; tiny finite tables; no engine/perf claims. Set algebra is Book 1.

GROUP BY grade with NULL countgroup_byarity 3 rows 4Gradesarity 3 rows 5
group_bygraden_alln_grade701180119022NULL10

Summary

GROUP BY treats NULL as a grouping value for grouping purposes. Note: bag and NULL behavior are here; set algebra was Book One.

group summary\text{group summary}