SQL in a Nutshell, 2nd Edition

Name

IN Operator

Synopsis

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
DB2	Supported
MySQL	Supported
Oracle	Supported, with variations
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

Permitted under either the WHERE or HAVING clauses. The IN comparison may also be a part of a 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 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 none. 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 ...