BETWEEN Ranges

NULL Gives UNKNOWN

NULL tested values or endpoints make UNKNOWN.

highlighted = computed this step

NULL gives UNKNOWN

A NULL tested value or NULL endpoint makes BETWEEN UNKNOWN.

NULL→UNKNOWN\text{NULL}\to\text{UNKNOWN}

NULL tested value

The normal range has 1 UNKNOWN tested-value row.

UNKNOWN rows=1\text{UNKNOWN rows}=1

BETWEEN examples are tiny finite-table transforms with SQL-style TRUE/FALSE/UNKNOWN results; SQL dialect completeness, type coercion, optimizer behavior, indexing, and product claims are out of scope.

NULL gives UNKNOWN: base rowsidnameqty1small32edge-low53middle74edge-high95large126missingNULL BETWEEN result per rowsourcetestedValuelowerupperrangePositionbaseTruthtruthoutputRole0359belowFALSEFALSEdropped1559insideTRUETRUEkept2759insideTRUETRUEkept3959insideTRUETRUEkept41259aboveFALSEFALSEdropped5NULL59unknownUNKNOWNUNKNOWNdropped WHERE-style TRUE-only output rowsidnameqty2edge-low53middle74edge-high9 BETWEEN factsfactvalueinputRowCount6trueCount3falseCount2unknownCount1outputRowCount3droppedRowCount3belowCount1insideCount3aboveCount1equalEndpointRangenonegatednowhereKeepsTRUE_only_source_order

NULL endpoint

With a NULL endpoint, all 6 rows are UNKNOWN.

endpoint UNKNOWN rows=6\text{endpoint UNKNOWN rows}=6

BETWEEN examples are tiny finite-table transforms with SQL-style TRUE/FALSE/UNKNOWN results; SQL dialect completeness, type coercion, optimizer behavior, indexing, and product claims are out of scope.

NULL endpoint gives UNKNOWN: base rowsidnameqty1small32edge-low53middle74edge-high95large126missingNULL BETWEEN result per rowsourcetestedValuelowerupperrangePositionbaseTruthtruthoutputRole03NULL9unknownUNKNOWNUNKNOWNdropped15NULL9unknownUNKNOWNUNKNOWNdropped27NULL9unknownUNKNOWNUNKNOWNdropped39NULL9unknownUNKNOWNUNKNOWNdropped412NULL9unknownUNKNOWNUNKNOWNdropped5NULLNULL9unknownUNKNOWNUNKNOWNdropped WHERE-style TRUE-only output rowsidnameqtynonenonenone BETWEEN factsfactvalueinputRowCount6trueCount0falseCount0unknownCount6outputRowCount0droppedRowCount6belowCount0insideCount0aboveCount0equalEndpointRangenonegatednowhereKeepsTRUE_only_source_order

Summary

UNKNOWN rows are dropped by WHERE-style TRUE-only output.

UNKNOWN dropped\text{UNKNOWN dropped}