SQL Semantics

Aggregates Skip NULL

SQL aggregates handle NULL differently by function.

highlighted = computed this step

Aggregates skip NULL differently

Aggregate functions have sharp edges around NULL. COUNT star counts every row, while column aggregates skip missing values. Note: every number below is read from the compiled aggregate result.

aggregate sharp edges\text{aggregate sharp edges}

Exact aggregate result

COUNT star is 5, COUNT grade is 4, and COUNT DISTINCT grade is 3. SUM, MIN, and MAX are 330, 70, and 90.

count star=5,sum=330\text{count star}=5,\quad \text{sum}=330

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

Aggregates skip NULLgroup_byarity 6 rows 1Gradesarity 3 rows 5
group_byn_alln_graden_distinctsum_grademin_grademax_grade5433307090

Duplicates and NULL both matter

For DB rows, COUNT star is 3, COUNT grade is 2, COUNT DISTINCT grade is 1, and SUM grade is 180. Note: the duplicate grade contributes to SUM, while the NULL grade is skipped.

DB count star=3,DB count grade=2,DB sum=180\text{DB count star}=3,\quad \text{DB count grade}=2,\quad \text{DB sum}=180

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

Aggregates per coursegroup_byarity 5 rows 2Gradesarity 3 rows 5
group_bycoursen_alln_graden_distinctsum_gradeDB321180OS222150

Summary

COUNT star counts rows, while COUNT column, SUM, MIN, and MAX skip NULL values. Note: bag and NULL semantics are here; set algebra was Book One.

aggregate summary\text{aggregate summary}