BETWEEN Ranges

Above the Range

Above rows fail the positive range check.

highlighted = computed this step

Above the range

Values above the upper endpoint are FALSE.

above range\text{above range}

Read above rows

There is 1 above row, and FALSE plus UNKNOWN rows make 3 dropped rows.

above rows=1\text{above 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.

Above the range: base rowsidnameqty1small32edge-low53middle74edge-high95large126missingNULL BETWEEN result per rowsourcetestedValuelowerupperrangePositionbaseTruthtruthoutputRole0359belowFALSEFALSEdropped1559insideTRUETRUEkept2759insideTRUETRUEkept3959insideTRUETRUEkept41259aboveFALSEFALSEdropped5NULL59unknownUNKNOWNUNKNOWNdropped WHERE-style TRUE-only output rowsidnameqty2edge-low53middle74edge-high9 BETWEEN factsfactvalueinputRowCount6trueCount3falseCount2unknownCount1outputRowCount3droppedRowCount3belowCount1insideCount3aboveCount1equalEndpointRangenonegatednowhereKeepsTRUE_only_source_order

Above is not TRUE

The above row is visible in the range facts and absent from the output rows.

above drops\text{above drops}

Summary

Below and above both fail the positive BETWEEN check.

outside is FALSE\text{outside is FALSE}