IN conditions

Checks membership by searching a value from the main query with another value in the subquery.

Syntax

expression [ NOT ] IN 
{ ( subquery ) |  ( value-expr, ... ) }

Parameters

value-expr are expressions that take on a single value, which may be a string, a number, a date, or any other SQL data type.

Remarks

An IN condition, without the NOT keyword, evaluates according to the following rules:

TRUE if expression is not NULL and equals at least one of the values.
UNKNOWN if expression is NULL and the values list is not empty, or if at least one of the values is NULL and expression does not equal any of the other values.
FALSE if expression is NULL and subquery returns no values; or if expression is not NULL, none of the values are NULL, and expression does not equal any of the values.

You can reverse the logic of the IN condition by using the NOT IN form.

The following search condition expression IN ( values ) is identical to the search condition expression = ANY ( values ). The search condition expression NOT IN ( values ) is identical to the search condition expression <> ALL ( values ).

Example

Select the company name and state for customers who live in the following Canadian provinces: Ontario, Manitoba, and Quebec.

SELECT CompanyName , Province
FROM Customers
WHERE State IN( 'ON', 'MB', 'PQ');

Discuss this page in DocCommentXchange.
Send feedback about this page using email.