IN

All functions > COMPARISON > IN

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

Syntax

expr IN (value, value, ...)

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
  • Equivalent to chaining three-valued = with OR across the list (NOT IN is NOT of that OR)
  • NULL value, NULL list element without a definite match, or nested-NULL ROW/ARRAY membership → NULL
  • ROW / ARRAY / ARRAY<ROW> membership uses the same poisoned-pair = rules

Related Functions

Examples

Numeric values

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

String values

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

Other types

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

NULL edge cases

FeatureQL
SELECT
    -- NULL in list: unknown without a definite match
    f1 := 2 IN (1, NULL::BIGINT, 3),
    -- ROW membership uses three-valued `=`
    f2 := ROW(1::BIGINT, NULL::BIGINT) IN (ROW(1::BIGINT, NULL::BIGINT))
;
Result
f1 BOOLEANf2 BOOLEAN
NULLNULL

Last update at: 2026/08/04 11:25:38