SQL Semantics

NULL is Not a Value

NULL comparisons produce UNKNOWN in SQL three-valued logic.

highlighted = computed this step

NULL makes truth three-valued

SQL predicates do not only produce TRUE or FALSE. When NULL participates in a comparison, the result is UNKNOWN. Note: the truth table is rendered as data.

three valued logic\text{three valued logic}

NULL comparisons are UNKNOWN

The recomputed truth table has row count 4. Note: UNKNOWN is not TRUE, so later WHERE filtering will drop it.

truth rows=4\text{truth rows}=4

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

Three-valued truthTrutharity 2 rows 4
Truthcasetruthninety_greater_than_thresholdTRUEnull_compared_to_numberUNKNOWNnull_equals_nullUNKNOWNseventy_greater_than_thresholdFALSE

UNKNOWN is a third result

The compiled counts are TRUE 1, FALSE 1, and UNKNOWN 2. Note: UNKNOWN is not a spelling of FALSE; WHERE treats it separately and still drops it.

TRUE=1,FALSE=1,UNKNOWN=2\text{TRUE}=1,\quad \text{FALSE}=1,\quad \text{UNKNOWN}=2

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

Truth value countsgroup_byarity 2 rows 3Trutharity 2 rows 4
group_bytruthnFALSE1TRUE1UNKNOWN2

Summary

NULL is not an ordinary value in comparisons; it produces UNKNOWN. Note: this bag and NULL logic is Book Two territory; set algebra was Book One.

null summary\text{null summary}