NOT BETWEEN

All functions > COMPARISON > NOT BETWEEN

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

Syntax

expr NOT BETWEEN lower AND upper

expr NOT BETWEEN lower AND upper

Notes

• Returns TRUE if expr < lower OR expr > upper
• Range is inclusive of both bounds
• NULL comparisons return NULL (three-valued logic)
• Opposite of BETWEEN
• All values must be of comparable types
• For strings, uses lexicographic (dictionary) ordering
• For dates, compares chronologically
• For ARRAY and ROW, same ordering rules as BETWEEN

Related Functions

• NOT_BETWEEN()

Examples

NOT BETWEEN — outside inclusive band

SELECT
    f1 := 0 NOT BETWEEN 1 AND 5, -- Below lower bound
    f2 := 10 NOT BETWEEN 1 AND 5, -- Above upper bound
    f3 := 3 NOT BETWEEN 1 AND 5 -- Inside range is false
;

NOT BETWEEN — arrays and rows

SELECT
    f1 := ARRAY(0, 0) NOT BETWEEN ARRAY(1, 1) AND ARRAY(3, 3) -- Below the lower bound in lexicographic order
;

NOT BETWEEN — NULL

SELECT
    f1 := NULL::BIGINT NOT BETWEEN 1 AND 10 -- NULL value
;