IN

Definition

IN (value1, value2, ...) tests whether a value matches ANY item in a given list — shorthand for a chain of OR-ed equality checks against the same column.

Running example — a products(id, name, category, price, stock) table, where stock can be NULL (not yet counted):

sql
SELECT name, category FROM products WHERE category IN ('Electronics', 'Books');
-- exactly equivalent to:
SELECT name, category FROM products WHERE category = 'Electronics' OR category = 'Books';

IN can also take a SUBQUERY instead of a literal list: WHERE category IN (SELECT category FROM featured_categories) — this is one of the main bridges between simple filtering and subqueries (Chapter 17).

Edge Cases and Pitfalls

• If the list (or subquery) passed to IN contains a NULL, and the tested value doesn't match any of the non-NULL items, the WHOLE IN expression evaluates to UNKNOWN rather than a clean FALSE — this matters most for NOT IN (see the classic gotcha covered in Chapter 7's Division topic), where a single NULL in the list can cause the entire NOT IN to unexpectedly match nothing.
• IN with a long literal list is fine for a handful of values; for genuinely large lookup sets, a JOIN against a real table (or a subquery) is usually more maintainable and can be more efficient.
• column IN (single_value) works but is unnecessarily indirect — a plain column = single_value says the same thing more directly.

Key Takeaways

• IN (list) is shorthand for chained OR-equality checks against one column; it also accepts a subquery instead of a literal list.
• A NULL inside the IN list can make the whole IN (and especially NOT IN) behave unexpectedly — treat this as a genuine gotcha to watch for.
• For a single value, plain equality is more direct than IN.