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¶

To compare individual values:

```<value> [ NOT ] IN ( <value_1> [ , <value_2> ...  ] )
```

To compare row constructors (parenthesized lists of values):

```( <value_A> [, <value_B> ... ] ) [ NOT ] IN (  ( <value_1> [ , <value_2> ... ] )  [ , ( <value_3> [ , <value_4> ... ] )  ...  ]  )
```

To compare a value to the values returned by a subquery:

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

## Parameters¶

`value`

The value for which to search.

`value_A`, `value_B`

The elements of a row constructor for which to search.

Ensure that each value on the right of `IN` (e.g. `(value3, value4)`) has the same number of elements as the value on the left of `IN` (e.g. `(value_A, value_B)`).

`value_#`

A value to which `value` should be compared.

If the values to compare to are row constructors, then each `value_#` is an individual element of a row constructor.

`subquery`

A subquery that returns a list of values to which `value` can be compared.

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

For example, the following returns NULL, not TRUE:

```SELECT NULL IN (1, 2, NULL) AS RESULT;
```
• Syntactically, `IN` is treated as an operator rather than a function. The example below shows the difference between using `IN` as an operator and calling `f()` as a function:

```SELECT
f(a, b),
x IN (y, z) ...
```

You cannot use function syntax with `IN`. For example, you cannot rewrite the preceding example as:

```SELECT
f(a, b),
IN(x, (y, z)) ...
```
• `IN` is also considered a subquery operator.

## Collation Details¶

Arguments with collation specifications are currently not supported.

## Examples¶

### Using `IN` with Simple Literals¶

The following examples show how to use `IN` and `NOT IN` with simple literals:

```SELECT 1 IN (1, 2, 3) AS RESULT;
+--------+
| RESULT |
|--------|
| True   |
+--------+
```
```SELECT 4 NOT IN (1, 2, 3) AS RESULT;
+--------+
| RESULT |
|--------|
| True   |
+--------+
```

### Using `IN` With a Subquery¶

```SELECT 'a' IN (
SELECT column1 FROM VALUES ('b'), ('c'), ('d')
) AS RESULT;
+--------+
| RESULT |
|--------|
| False  |
+--------+
```

### Using `IN` with a Table¶

These examples show how to use `IN` with a table. The statement below creates the table used in the examples.

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

This example shows how to use `IN` with a single column of a table:

```SELECT col_1, col_2, col_3
FROM my_table
WHERE (col_1) IN (1, 10, 100, 1000)
ORDER BY col_1, col_2, col_3;
+-------+-------+-------+
| COL_1 | COL_2 | COL_3 |
|-------+-------+-------|
|     1 |     1 |     1 |
|     1 |     2 |     3 |
+-------+-------+-------+
```

This example shows how to use `IN` with multiple columns of a table:

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

This example shows how to use `IN` with a subquery that reads multiple columns of a table:

```SELECT (1, 2, 3) IN (
SELECT col_1, col_2, col_3 FROM my_table
) AS RESULT;
+--------+
| RESULT |
|--------|
| True   |
+--------+
```

### Using NULL¶

Remember that NULL != NULL. Neither of the following queries returns a match when the value on the left or right of the `IN` contains a NULL:

```SELECT NULL IN (1, 2, NULL) AS RESULT;
+--------+
| RESULT |
|--------|
| NULL   |
+--------+
```
```SELECT (4, 5, NULL) IN ( (4, 5, NULL), (7, 8, 9) ) AS RESULT;
+--------+
| RESULT |
|--------|
| NULL   |
+--------+
``` 