Categories:

Conditional Expression Functions

# [ NOT ] IN¶

Tests whether its argument is or is not one of the members of an explicit list or the result of a subquery.

Note

In subquery form, IN is equivalent to = ANY and NOT IN is equivalent to <> ALL.

## Syntax¶

<value> [ NOT ] IN ( <value1> , <value2> , ... )

<value> [ NOT ] IN ( ( <value1> , <value2> , ... ) , ( <value3> , <value4> , ... ) )

<value> [ NOT ] IN ( <subquery> )


## Usage Notes¶

• As in most contexts, NULL is not equal to NULL. If <value> is NULL, then the return value of the function is NULL, whether or not the list or subquery contains NULL.

## Collation Details¶

Arguments with collation specifications are currently not supported.

## Examples¶

The following examples show how to use IN with literals:

SELECT
CASE
WHEN 1 IN (1, 2, 3)
THEN 'true'
ELSE 'false'
END AS RESULT;
+--------+
| RESULT |
|--------|
| true   |
+--------+

SELECT
CASE
WHEN 'a' NOT IN (SELECT column1 FROM VALUES ('b'), ('c'), ('d'))
THEN 'true'
ELSE 'false'
END AS RESULT;
+--------+
| RESULT |
|--------|
| true   |
+--------+


This example shows how to use IN with a table:

CREATE TABLE MyTable (col_1 INTEGER, col_2 INTEGER, col_3 INTEGER);
INSERT INTO MyTable (col_1, col_2, col_3) VALUES
(1, 1, 1),
(1, 2, 3),
(4, 5, NULL);

SELECT *
FROM MYTABLE
WHERE (col_1, col_2, col_3) IN ((1,2,3),(4,5,6));
+-------+-------+-------+
| COL_1 | COL_2 | COL_3 |
|-------+-------+-------|
|     1 |     2 |     3 |
+-------+-------+-------+


Remember that NULL != NULL. The following shows that “(4, 5, NULL)” does not match itself because NULL does not match itself:

SELECT *
FROM MYTABLE
WHERE (col_1, col_2, col_3) IN ((9, 8, 7), (4, 5, NULL));
+-------+-------+-------+
| COL_1 | COL_2 | COL_3 |
|-------+-------+-------|
+-------+-------+-------+


This is a simple example of using NOT IN:

SELECT *
FROM MYTABLE
WHERE (col_1, col_2, col_3) NOT IN ((1, 1, 1), (2, 2, 2));
+-------+-------+-------+
| COL_1 | COL_2 | COL_3 |
|-------+-------+-------|
|     1 |     2 |     3 |
|     4 |     5 |  NULL |
+-------+-------+-------+ 