Where Filters

Where BETWEEN NULL Unknown

BETWEEN with a NULL row value evaluates to UNKNOWN, and WHERE keeps only TRUE.

highlighted = computed this step

Input table

Start with order amounts where one amount is NULL.

Order amounts\text{Order amounts}
Order amountsorder_idamounto150o2NULLo380

Where query

Evaluate the inclusive range predicate for each amount.

WHERE amount BETWEEN bounds\text{WHERE amount BETWEEN bounds}
WHERE amount BETWEEN with NULLwhere betweenarity 2 rows 1OrderAmountsarity 2 rows 3

Predicate truth

The NULL amount produces UNKNOWN rather than TRUE.

TRUE, UNKNOWN, FALSE\text{TRUE, UNKNOWN, FALSE}
Three-valued truthrowtruth1TRUE2UNKNOWN3FALSE

Filtered output

Only the row with a known in-range amount remains.

known in-range row\text{known in-range row}
Filtered outputorder_idamounto150