NULL Tests

No UNKNOWN for IS NULL

IS NULL itself returns TRUE or FALSE.

highlighted = computed this step

No UNKNOWN here

IS NULL itself returns TRUE or FALSE, not UNKNOWN.

no UNKNOWN\text{no UNKNOWN}

Read UNKNOWN count

The compiler reports 0 UNKNOWN rows for IS NULL.

UNKNOWN rows=0\text{UNKNOWN rows}=0

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.

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

Different from comparison

This is not the same as comparing a value to NULL.

NULL test not comparison\text{NULL test not comparison}

Summary

IS NULL is the safe finite test for missingness in this book.

TRUE or FALSE\text{TRUE or FALSE}