IN_LIST()

All functions > COMPARISON > IN_LIST()

Returns TRUE if a value exists in a list of values.

Signatures

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

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

Notes

• Value and list elements must be of compatible types
• Returns TRUE if any element in the list matches the value
• Returns FALSE if the value is not 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 OR across the list

Related operators

• IN

Examples

IN_LIST — registry encoding

SELECT
    f1 := IN_LIST(7, ARRAY[1, 3, 7, 9]), -- Equivalent to `IN` with a literal list
    f2 := IN_LIST(2, ARRAY[1, 3, 7, 9]) -- Value not in candidate array
;

IN_LIST — NULL edge cases

SELECT
    f1 := IN_LIST(NULL::BIGINT, ARRAY[1, 2, 3]), -- NULL probe value
    f2 := IN_LIST(2, ARRAY[1, NULL::BIGINT, 3]) -- NULL in candidates: unknown without a definite match
;