- 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¶
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 ofIN
(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);
Syntactically,
IN
is treated as an operator rather than a function. The example below shows the difference between usingIN
as an operator and callingf()
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
with simple 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 | +--------+
Using IN
with a Table¶
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 | |-------+-------+-------| +-------+-------+-------+
Using NOT IN
¶
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 | +-------+-------+-------+