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 NULL group_by arity 6 rows 1 Grades arity 3 rows 5
group_by n_all n_grade n_distinct sum_grade min_grade max_grade 5 4 3 330 70 90

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}