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 |
+-------+-------+-------+
``` 