IN checks whether a value matches any value in a given list — a shorter, cleaner alternative to a chain of OR conditions.
Syntax
WHERE column IN (value1, value2, value3)Sample Table: employees
| name | department |
|---|---|
| Aman | Engineering |
| Riya | Marketing |
| Neha | Design |
| Karan | Sales |
Example
SELECT name FROM employees
WHERE department IN ('Engineering', 'Design');Same result as:
WHERE department = 'Engineering' OR department = 'Design';but far easier to read — and to extend — once the list grows past two items.
NOT IN
SELECT name FROM employees
WHERE department NOT IN ('Sales', 'Marketing');IN with a Subquery
SELECT name FROM employees
WHERE department IN (
SELECT department FROM departments WHERE region = 'North'
);This is one of the most common real-world uses of IN — filtering against a dynamic list produced by another query. Full coverage in Subquery in WHERE.
Practical Use Case
Filtering against a known, finite set of values — statuses ('pending', 'shipped', 'delivered'), specific IDs, or a handful of category names.
Common Mistakes
- NOT IN with a subquery that can return NULL — if the subquery's result set contains even one
NULL, the wholeNOT INcomparison can return no rows at all, which surprises almost everyone the first time they hit it. PreferNOT EXISTSwhen the subquery might contain NULLs. - Using
INwith a very large list (thousands of values) instead of joining against a proper table —INis fine for a handful of literals, not as a substitute for a join
Interview Relevance
The NULL trap with NOT IN above is a favorite "gotcha" interview question — knowing it (and reaching for NOT EXISTS as the fix) is a strong signal of real SQL experience, not just textbook knowledge.
Practice Question
Rewrite WHERE status = 'pending' OR status = 'processing' OR status = 'shipped' using IN.