NOT IN filters out rows whose value appears in a specified list or set. It is useful when a task says, “Show everything except these prices” or “Find titles that were not included in this order.” section 6.3.2 section 11.2.2
Exclude selected prices
SELECT TitleName, Price FROM title WHERE Price NOT IN (10.95, 12.95) ORDER BY Price ASC; This returns every title except those priced at $10.95 or $12.95. The same filtering idea can be written as WHERE NOT Price IN (...), but NOT IN is clearer and more common for exclusion lists. section 6.3.2
SELECT
t.TitleName
FROM title t
WHERE TitleID NOT IN (
SELECT TitleID
FROM orderitem
WHERE OrderID = 1034);
Use this query to find titles whose TitleID does not appear among the items in order 1034. section 11.2.2
Keep these practical points in mind:
- Use NOT IN with a list when excluding specific values, such as prices; use it with a subquery when excluding values returned by another query. section 6.3.2 section 11.2.2
- Do not replace NOT IN with <> ANY when the subquery returns several values: <> ANY can match a different value and return every title. section 11.2.2
- Choose ALL instead when expressing a comparative condition rather than directly using the membership operator, and remember that subqueries may require more processing and memory. section 11.2.2
