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
• Equivalent to chaining != with AND across the list
• Opposite of IN

Related operators

• NOT IN

Examples

NOT_IN_LIST — registry encoding

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

NOT_IN_LIST — NULL edge cases

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