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
• NULL comparisons always return NULL (three-valued logic)
• Equivalent to: value >= lower_bound AND value <= upper_bound
• For strings, uses lexicographic (dictionary) ordering
• For dates, compares chronologically
• For ARRAY and ROW, uses the same ordering as < / >= (lexicographic / field-by-field)
• Supports optional decimal precision for numeric comparisons

Related Functions

• BETWEEN()

Examples

BETWEEN — inclusive bounds

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

BETWEEN — arrays and rows

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

BETWEEN — NULL propagation

SELECT
    f1 := NULL::BIGINT BETWEEN 1 AND 10, -- NULL value
    f2 := 3 BETWEEN NULL::BIGINT AND 10 -- NULL bound
;