- Categories:
[ 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 | +-------+-------+-------+