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
    -- 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
;