|
Checks membership by searching a value from the main query with another value in the subquery.
expression [ NOT ] IN
{ ( subquery ) | ( value-expr, ... ) }
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.
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 ).
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')