BETWEEN Ranges

Below and Inside Range

Below rows drop and inside rows stay.

highlighted = computed this step

Below and inside

Values below the lower endpoint are FALSE; values inside are TRUE.

below vs inside\text{below vs inside}

Read below and inside

There is 1 below row and 3 inside rows.

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

Below and inside: base rowsidnameqty1small32edge-low53middle74edge-high95large126missingNULL BETWEEN result per rowsourcetestedValuelowerupperrangePositionbaseTruthtruthoutputRole0359belowFALSEFALSEdropped1559insideTRUETRUEkept2759insideTRUETRUEkept3959insideTRUETRUEkept41259aboveFALSEFALSEdropped5NULL59unknownUNKNOWNUNKNOWNdropped WHERE-style TRUE-only output rowsidnameqty2edge-low53middle74edge-high9 BETWEEN factsfactvalueinputRowCount6trueCount3falseCount2unknownCount1outputRowCount3droppedRowCount3belowCount1insideCount3aboveCount1equalEndpointRangenonegatednowhereKeepsTRUE_only_source_order

Only inside survives

The below row appears in the range table but not in the TRUE-only output.

below drops\text{below drops}

Summary

The diagram shows both the rejected row and the kept rows.

show both\text{show both}