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
• ROW / ARRAY / ARRAY<ROW> operands are supported; membership uses the same three-valued = rules
• Equivalent to chaining = with OR across the list

Related operators

• IN

Examples

IN_LIST — registry encoding

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

IN_LIST — NULL edge cases

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