IN Operator
SQL · Filtering Rows
Theory
IN checks whether a value matches any item in a list. dept IN ('IT', 'HR', 'Finance') is shorthand for dept = 'IT' OR dept = 'HR' OR dept = 'Finance', and it is easier to read and extend. The list can also be a subquery: WHERE id IN (SELECT customer_id FROM orders) keeps customers who have placed orders.
NOT IN keeps values that are not in the list, but it hides a trap: if the list or subquery contains a NULL, NOT IN returns no rows at all, because x <> NULL is UNKNOWN. When a subquery might return NULLs, use NOT EXISTS or filter NULLs out. Values in the list must be the same type as the column. Writing NULL inside an IN list does not match NULL rows; use IS NULL for that.
Why we use it
Filtering by a set of allowed values is very common: a few cities, a list of IDs, a set of statuses. IN replaces long OR chains and lets the list come straight from another query.
When to use it
Use IN for a fixed list of values or a subquery returning one column. For ranges use BETWEEN. For anti-matches against a subquery that may contain NULLs, prefer NOT EXISTS over NOT IN.
How it works
- Read a row
- Take the column value
- Does it equal any item in the list? Yes: Keep the row. No: Discard the row.
- Repeat for every row
- Pass kept rows on
Syntax
SELECT columns FROM table
WHERE column IN (val1, val2, ...);Example
SELECT * FROM employees WHERE department IN ('IT','HR','Finance');Key points
- Shorthand for multiple OR conditions on the same column
- Can take a subquery in place of the list
- NOT IN returns nothing if the list contains a NULL
- Prefer NOT EXISTS for anti-matches against subqueries
- Order of items in the list does not matter