SQL in a Nutshell, 3rd Edition

Name

IN Operator

The IN operator provides a way to delineate a list of values, either explicity listed or from a subquery, and compare a value against that list in a WHERE or HAVING clause. In other words, it gives you a way to say “Is value A in this list of values?”

Platform	Command
MySQL	Supported
Oracle	Supported
PostgreSQL	Supported
SQL Server	Supported

SQL2003 Syntax

{WHERE | HAVING | {AND | OR}}
   value [NOT] IN ({comp_value1, comp_value2[, ...] | subquery})

Keywords

{WHERE | HAVING | {AND | OR}} value

IN is permitted under either the WHERE or the HAVING clause. The IN comparison may also be a part of an AND or OR clause in a multicondition WHERE or HAVING clause. value may be of any datatype, but is usually the name of a column of the table referenced by the transaction, or perhaps a host variable when used programmatically.

NOT

Optionally tells the database to look for a result set that contains values that are not in the list.

IN ({comp_value1, comp_value2[, . . . ] | subquery})

Defines the list of comparative values (hence, comp_value) to compare against. Each comp_value must be of the same or a compatible datatype as the initial value. They are also governed by standard datatype rules. For example, string values must be delimited by quotes, while integer values need no delimiters. As an alternative to listing specific values, you may use parentheses to enclose a subquery that returns one or more values of a compatible datatype.

In the following example, generated on SQL Server, we look for ...