IN_LIST

All functions > COMPARISON > IN_LIST

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

Signatures

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

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 multiple OR conditions with equality checks
• More concise than: value = val1 OR value = val2 OR ...
• Can be used with operator syntax: value IN (val1, val2, ...)

See also

• Operators

Examples

Numeric values

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

String values

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

Other types

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