NOT_IN_LIST

All functions > COMPARISON > NOT_IN_LIST

Returns TRUE if a value does NOT exist in a list of values.

Signatures

Returns: TRUE if value is not found in the list, FALSE otherwise

NOT_IN_LIST(expr: T, list_of_exprs: ARRAY<T>) → BOOLEAN

Notes

• Value and list elements must be of compatible types
• Returns TRUE if the value does not match any element in the list
• Returns FALSE if the value is found in the list
• NULL handling: if value is NULL, returns NULL; if any list element is NULL and value doesn't match other elements, returns NULL
• Equivalent to multiple AND conditions with inequality checks
• Opposite of IN_LIST function
• More concise than: value != val1 AND value != val2 AND ...
• Can be used with operator syntax: value NOT IN (val1, val2, ...)

See also

• Operators

Examples

Numeric values

SELECT
    f1 := 6 NOT IN (1, 2, 3, 4, 5),  -- Number not in list
    f2 := 5 NOT IN (1, 2, 3, 4, 5),  -- Number in list
    f3 := NULL::BIGINT NOT IN (1, 2, 3)  -- NULL value
;

String values

SELECT
    f1 := 'grape' NOT IN ('apple', 'banana', 'cherry'),  -- String not in list
    f2 := 'apple' NOT IN ('apple', 'banana', 'cherry'),  -- String in list
    f3 := NULL::VARCHAR NOT IN ('a', 'b', 'c')  -- NULL string
;

Other types

SELECT
    f1 := FALSE NOT IN (TRUE),  -- Boolean not in list
    f2 := DATE '2024-06-15' NOT IN (DATE '2024-01-01', DATE '2024-12-31')  -- Date not in list
;