BETWEEN

All functions > COMPARISON > BETWEEN

Returns TRUE if a value is within a specified range (inclusive)

Syntax

expr BETWEEN lower AND upper

expr BETWEEN lower AND upper

Notes

• All three values must be of comparable types
• The range is inclusive of both bounds
• Equivalent to: value >= lower_bound AND value <= upper_bound (same three-valued AND as comparison chains)
• A NULL (or nested-NULL composite) in the probe or a bound propagates through that conjunct; the other conjunct can still force FALSE
• For ARRAY and ROW, uses the same ordering/= poisoning rules as < / >=
• On FLOAT/DOUBLE, write .N (e.g. >.2, =.4, BETWEEN.3) for N digits of rounded precision via DECIMAL(38, N) staging
• Any NaN operand makes the comparison NULL
• Infinity, or any finite value too large for DECIMAL(38, N), falls back to a raw float compare — .N rounding is not applied on that path

Related Functions

• BETWEEN()

Examples

BETWEEN — inclusive bounds

SELECT
    -- Inside range
    f1 := 3 BETWEEN 1 AND 5,
    -- Lower bound inclusive
    f2 := 1 BETWEEN 1 AND 5,
    -- Upper bound inclusive
    f3 := 5 BETWEEN 1 AND 5,
    -- Below range
    f4 := 0 BETWEEN 1 AND 5
;

BETWEEN — arrays and rows

SELECT
    -- Value within lexicographic bounds
    f1 := ARRAY(2, 3) BETWEEN ARRAY(1, 1) AND ARRAY(3, 3)
;

BETWEEN — NULL propagation

SELECT
    -- NULL value
    f1 := NULL::BIGINT BETWEEN 1 AND 10,
    -- NULL bound
    f2 := 3 BETWEEN NULL::BIGINT AND 10,
    -- Bound poisoned; other conjunct FALSE → FALSE
    f3 := ROW(9::BIGINT, 9::BIGINT)
    BETWEEN ROW(0::BIGINT, NULL::BIGINT)
    AND ROW(2::BIGINT, 2::BIGINT),
    -- Probe poisons both conjuncts → NULL
    f4 := ROW(9::BIGINT, NULL::BIGINT)
    BETWEEN ROW(0::BIGINT, 0::BIGINT)
    AND ROW(2::BIGINT, 2::BIGINT)
;