NULL Tests

IS NULL TRUE for NULL

IS NULL is TRUE for NULL values.

highlighted = computed this step

IS NULL TRUE

IS NULL is TRUE exactly when the tested value is NULL.

NULL⇒TRUE\text{NULL}\Rightarrow\text{TRUE}

Read TRUE rows

The city IS NULL test has 2 TRUE rows.

TRUE rows=2\text{TRUE rows}=2

NULL-test examples are tiny finite-table transforms for IS NULL and IS NOT NULL; SQL dialect completeness, optimizer behavior, indexing, and product claims are out of scope.

IS NULL true for NULL: base rowsidnamecityscore1AnnAustin102BoNULL203CyBostonNULL4DiNULL305EliDenver40 NULL test result per rowsourcetestedValueisNulltruthoutputRole0AustinnoFALSEdropped1NULLyesTRUEkept2BostonnoFALSEdropped3NULLyesTRUEkept4DenvernoFALSEdropped WHERE-style TRUE-only output rowsidnamecityscore2BoNULL204DiNULL30 NULL-test factsfactvalueinputRowCount5nullCount2presentCount3trueCount2falseCount3unknownCount0outputRowCount2droppedRowCount3negatednowhereKeepsTRUE_only_source_order

Missing rows are kept

Rows with missing city values are the TRUE output rows.

missing kept\text{missing kept}

Summary

IS NULL is the direct missing-value test.

test missing\text{test missing}