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 tuple 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 

IN String

The IN operator can be used as a shorthand for the contains string function. For example:

SELECT 'Hello' IN 'Hello World'; 

true 

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).