BETWEEN Ranges

Equal-endpoint One-point Range

Equal endpoints form one exact range point.

highlighted = computed this step

One-point range

When the lower and upper endpoints are equal, the range has one exact point.

one point range\text{one point range}

Read one-point output

The equal-endpoint range outputs 1 TRUE row.

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

Equal-endpoint one-point range: base rowsidnameqty1small32edge-low53middle74edge-high95large126missingNULL BETWEEN result per rowsourcetestedValuelowerupperrangePositionbaseTruthtruthoutputRole0377belowFALSEFALSEdropped1577belowFALSEFALSEdropped2777insideTRUETRUEkept3977aboveFALSEFALSEdropped41277aboveFALSEFALSEdropped5NULL77unknownUNKNOWNUNKNOWNdropped WHERE-style TRUE-only output rowsidnameqty3middle7 BETWEEN factsfactvalueinputRowCount6trueCount1falseCount4unknownCount1outputRowCount1droppedRowCount5belowCount2insideCount1aboveCount2equalEndpointRangeyesnegatednowhereKeepsTRUE_only_source_order

Only the exact point

Rows below or above that point are FALSE.

exact point only\text{exact point only}

Summary

Inclusive endpoints still work when both endpoints are the same.

same endpoint allowed\text{same endpoint allowed}