NOT_IN_LIST()

All functions > COMPARISON > NOT_IN_LIST()

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

Signatures

Returns: Membership result; NULL in edge cases involving NULL inputs or list elements

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
• ROW / ARRAY / ARRAY<ROW> operands are supported; non-membership uses the same three-valued rules as = / NOT
• Equivalent to chaining != with AND across the list
• Opposite of IN

Related operators

• NOT IN

Examples

NOT_IN_LIST — registry encoding

SELECT
    -- Equivalent to `NOT IN` with a literal list
    f1 := NOT_IN_LIST(99, ARRAY(1, 2, 3)),
    -- Value present in candidate array
    f2 := NOT_IN_LIST(2, ARRAY(1, 2, 3))
;

NOT_IN_LIST — NULL edge cases

SELECT
    -- NULL probe value
    f1 := NOT_IN_LIST(NULL::BIGINT, ARRAY(1, 2, 3))
;