NOT_BETWEEN()

All functions > COMPARISON > NOT_BETWEEN()

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

Signatures

Returns: TRUE if expr lies in the inclusive range, or NULL if any argument is NULL

NOT_BETWEEN(expr: T, lower: T, upper: T, [digits: BIGINT]) → BOOLEAN

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
• 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 operators

• NOT BETWEEN

Examples

NOT_BETWEEN — functional call

SELECT
    -- `NOT_BETWEEN(expr, lower, upper)` matches keyword `NOT BETWEEN … AND …`
    f1 := NOT_BETWEEN(10, 1, 5),
    -- Inside the inclusive range
    f2 := NOT_BETWEEN(3, 1, 5)
;

NOT_BETWEEN — chained

SELECT
    -- Receiver is the value; arguments are lower and upper bounds
    f1 := (10).NOT_BETWEEN(1, 5),
    -- Same semantics as the prefix call
    f2 := (3).NOT_BETWEEN(1, 5)
;