The IN (and NOT IN) operators cause the values appearing in the WHERE clause predicate to be tested for membership in a collection of values, represented as a value list or as a subquery.
The syntax for the IN predicate is as follows:
The set of values returned by the SELECT subquery or contained
in the value list must be compatible
with the data type of x (which is typically a column name). The NULL
keyword cannot be included in the value list (for comparisons against null,
use a NULL predicate instead). If a
SELECT statement (subquery) is used with the
IN predicate, the subquery may only return values from a single column. The
individual values contained in the value list must be separated by
commas.
Example
SELECT sno, sname
FROM supplier
WHERE sno IN ('S1', 'S2', 'S3');
All data values in the sno column must match one of
the three string constants in order for their corresponding records to be
retrieved. This query retrieves the following data values:
| sno | sname |
| S1 | SMITH |
| S2 | JONES |
| S3 | BLAKE |