⌘+k ctrl+k
1.2 (stable)
Search Shortcut cmd + k | ctrl + k
IN Operator

The IN operator checks containment of the left expression inside the collection on the right hand side (RHS). Supported collections on the RHS are tuples, lists, maps and subqueries that return a single column.

IN (val1, val2, ...) (Tuple)

The IN operator on a tuple (val1, val2, ...) returns true if the expression is present in the RHS, false if the expression is not in the RHS and the RHS has no NULL values, or NULL if the expression is not in the RHS and the RHS has NULL values.

SELECT 'Math' IN ('CS', 'Math');

true

SELECT 'English' IN ('CS', 'Math');

false

SELECT 'Math' IN ('CS', 'Math', NULL);

true

SELECT 'English' IN ('CS', 'Math', NULL);

NULL

IN [val1, val2, ...] (List)

The IN operator works on lists according to the semantics used in Python. Unlike for the IN ⟨collection⟩ operator, the presence of NULL values on the right hand side of the expression does not make a difference in the result:

SELECT 'Math' IN ['CS', 'Math', NULL];

true

SELECT 'English' IN ['CS', 'Math', NULL];

false

IN Map

The IN operator works on maps according to the semantics used in Python, i.e., it checks for the presence of keys (not values):

SELECT 'key1' IN MAP {'key1': 50, 'key2': 75};

true

SELECT 'key3' IN MAP {'key1': 50, 'key2': 75};

false

IN Subquery

The IN operator works with subqueries that return a single column. For example:

SELECT 42 IN (SELECT unnest([32, 42, 52]) AS x);

true

If the subquery returns more than one column, a Binder Error is thrown:

SELECT 42 IN (SELECT unnest([32, 42, 52]) AS x, 'a' AS y);

Binder Error:
Subquery returns 2 columns - expected 1

NOT IN

NOT IN can be used to check if an element is not present in the set. x NOT IN y is equivalent to NOT (x IN y).

About this page