IN Operator

IN checks whether a value matches any value in a given list. It is shorthand for a chain of OR-connected equality checks, and it reads much more cleanly once you have more than two or three values to compare against.

Basic syntax

IN as shorthand for multiple ORs

SQL

SELECT first_name, last_name, department
FROM employees
WHERE department IN ('Engineering', 'Data Science', 'Product');

This is exactly equivalent to writing:

What IN expands to

SQL

SELECT first_name, last_name, department
FROM employees
WHERE department = 'Engineering'
   OR department = 'Data Science'
   OR department = 'Product';

NOT IN

Excluding a list of values

SQL

SELECT first_name, last_name, department
FROM employees
WHERE department NOT IN ('Sales', 'Marketing');

NOT IN with NULL is a serious, common trap

If the list passed to NOT IN contains even a single NULL — often because the list came from a subquery rather than literal values — the entire condition can unexpectedly match *zero* rows, even ones that clearly should not be excluded. This is a consequence of SQL’s three-valued logic, where any comparison against NULL produces “unknown” rather than true or false.

This silently returns no rows if excluded_ids contains a NULL

SQL

SELECT id, name
FROM products
WHERE id NOT IN (SELECT product_id FROM discontinued_products);
-- If even one product_id in discontinued_products is NULL,
-- this query returns ZERO rows, no matter what.

The safer, equivalent alternative is NOT EXISTS, which does not suffer from this problem because it checks row existence rather than comparing values directly against a list that might contain NULL:

A safer alternative to NOT IN

SQL

SELECT id, name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM discontinued_products d
  WHERE d.product_id = p.id
);

IN with a subquery

Instead of a literal list of values, IN can also be given a subquery that produces a list of values at run time — a very common pattern for filtering one table based on results from another. We cover subqueries in depth in a dedicated section later in this series.

IN with a subquery

SQL

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total &gt; 500);

Tip

As a rule of thumb: IN is fine and safe with a list of literal values you control. Once the list comes from a subquery that might contain NULL, prefer NOT EXISTS over NOT IN.