The IN operator filters rows whose expression matches one of several specified values. It replaces a long series of OR conditions with a shorter, easier-to-read list. section 6.1
Write an IN value list with these rules:
- Separate values with commas and enclose the list in parentheses. section 6.1
- Use single quotes for string and date values, just as with other SQL filters. section 6.1
- Use comparison operators for ranges when the complete list of matching values is unknown. section 6.2
- Use NOT IN to exclude listed values, such as a single price or a set of prices. section 6.7
Filter titles by several prices
WHERE Price IN (10.95, 12.95) finds titles priced at either amount. For dates, write values such as PublicationDate IN ('2015-04-30', '2016-02-06'). The order of values in the list does not determine result order; use ORDER BY when ordering matters. section 6.1
SELECT
TitleName,
Price
FROM title
WHERE Price IN (10.95, 12.95);
A compact replacement for separate Price equality conditions joined with OR. section 6.1
IN also accepts a subquery. For example, it can select titles whose TitleID appears among the items in order 1034. In a subquery, IN provides equality matching; comparisons such as > or <= require ANY instead. section 11.2.1
SELECT
t.TitleName
FROM title t
WHERE TitleID IN (
SELECT TitleID
FROM orderitem
WHERE OrderID = 1034
);
Matches titles against TitleID values returned by an order-item subquery. section 11.2.1
