Using SQLite

Name

IN — Test if value is in set

Syntax

Common Usage

col IN ( test1, test2, test3 )
col IN ( SELECT c FROM t )
col IN temp.in_test

Description

The IN operator tests to see if the test expression equal to (or not equal to) any of the values found on the righthand side of the expression. This is a three valued logic operator and will return 0, 1, or NULL. A NULL will be returned if a NULL is found on the lefthand side, or if a NULL appears anywhere in an unmatched test group.

There are three ways to define the test group. First, an explicit series of zero or more expressions can be given. Second, a subquery can be provided. This subquery must return a single column. The test expression will be evaluated against each row returned by the subquery. Both of these formats require parentheses.

The last way to define the test group is by providing a table name. The table must consist of only a single column. You cannot provide a table and column, it must be a single-column table. This final style is most frequently used with temporary tables. If you need to execute the same test multiple times, it can be more efficient to build a temporary table (for example, with CREATE TEMP TABLE...AS SELECT), and use it over and over, rather than using a subquery as part of the IN expression.

See Also

BETWEEN, EXISTS