SQL IN Operator Explained (With Examples)

The SQL IN operator lets you check whether a column's value matches any value in a list you supply. Instead of writing out a long chain of OR conditions comparing the same column against several possible values, IN lets you write that list once and get the same result in a single, much more readable condition.

It's one of the most commonly used operators in everyday SQL, especially when filtering rows against a known, finite set of values — specific departments, specific statuses, specific IDs pulled from another query.

Basic IN Syntax

The syntax is column IN (value1, value2, value3, ...). Suppose you want every employee in either the Sales or Marketing department from the employees table:

SELECT name, department, salary
FROM employees
WHERE department IN ('Sales', 'Marketing');

Result:

IN vs a Chain of OR Conditions

Without IN, the same query needs to repeat the column name for every value you're checking, which gets noisy fast as the list grows:

-- Equivalent to the IN query above, but harder to read
SELECT name, department, salary
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

Both queries return exactly the same rows — IN is pure syntactic sugar over a series of OR comparisons, but it scales much better. Checking a column against ten possible values with OR means writing the column name ten times; with IN it's one tidy list.

NOT IN: Excluding a List of Values

Add NOT in front to flip the logic and exclude every row whose value appears in the list:

-- Everyone except Sales and Marketing
SELECT name, department
FROM employees
WHERE department NOT IN ('Sales', 'Marketing');

Result:

name | department ---------+----------- Ravi | Engineering Meena | Finance Tom | Engineering

One gotcha worth knowing: if the list used with NOT IN contains a NULL value — for example, from a subquery that happens to return one — the entire condition can unexpectedly match zero rows, because SQL can't determine whether an unknown value is or isn't equal to anything. If you're using NOT IN with a subquery, it's worth checking the subquery can't produce NULLs, or filtering them out with a WHERE ... IS NOT NULL clause first.

Using IN with a Subquery

The list inside IN doesn't have to be hardcoded — it can come from another query entirely, which is one of the most practical uses of the operator:

-- Employees who work in a department with more than one person
SELECT name, department
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    GROUP BY department
    HAVING COUNT(*) > 1
);

This pairs the IN operator with a subquery to dynamically build the list of values to match against, rather than typing them out by hand. It's a pattern you'll use constantly once you're comfortable combining IN with the rest of your filtering logic covered in the WHERE clause.

SQL IN Operator Explained

Basic IN Syntax

IN vs a Chain of OR Conditions

NOT IN: Excluding a List of Values

Using IN with a Subquery

Discussion