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