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.
Get Using SQLite now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.