IN ​

IN tests whether a value occurs in a list or query result. NOT IN negates the result using SQL three-valued logic.

Syntax ​

expression [NOT] IN (value, ...)
expression [NOT] IN (SELECT ...)
expression [NOT] IN [schema.]table

The left side can be a scalar or a row value. Its field count must match the right side. Table shorthand is equivalent to IN (SELECT * FROM table) and therefore uses every column of that table.

NULL and empty input ​

A matching value makes IN true. If no value matches and the right side contains NULL, the result can be NULL instead of false. A NULL left operand also generally gives NULL for a nonempty right side.

An empty right side makes IN false and NOT IN true, including when the left side is NULL. Use EXISTS for a correlated presence test when the selected values themselves are irrelevant.

Examples ​

Lists and missing values ​

SELECT 2 IN (1, 2, 3),
       4 IN (1, NULL),
       4 NOT IN (1, 2, 3)
FORMAT csv;

1,\N,1

Table shorthand ​

CREATE TABLE allowed(value INTEGER);
INSERT INTO allowed VALUES (1), (2);

SELECT 2 IN allowed, 3 NOT IN main.allowed
FORMAT csv;

1,1

An empty result ​

SELECT NULL IN (SELECT value FROM allowed WHERE 0),
       NULL NOT IN (SELECT value FROM allowed WHERE 0)
FORMAT csv;

0,1

See WHERE for filtering rows with membership predicates.