SQL guide · Filtering

SQL IN: Match Any Value in a List or Subquery

The IN operator checks whether a value equals any item in a list. It is a compact replacement for a chain of OR conditions on the same column, so status IN ('new', 'open') replaces status = 'new' OR status = 'open'.

The list can be literal values or the result of a subquery that returns a single column. The subquery form is handy for filtering one table by values stored in another, for example customers whose id appears in a set of recent orders. For many tasks a JOIN or EXISTS is an equivalent alternative, and the optimizer often produces the same plan.

Negating the test with NOT IN finds values outside the list. This is where NULL causes trouble: if the list or subquery contains a NULL, NOT IN can never be true, so no rows come back. Understanding this one rule avoids most IN bugs.

Basic syntax

SELECT column1
FROM table_name
WHERE column IN (value1, value2, value3);

SELECT column1
FROM table_name
WHERE column IN (SELECT column FROM other_table WHERE condition);

The list must contain values of compatible types. A subquery must return exactly one column. Large literal lists work, but databases cap their size or degrade, so load very long lists into a temporary table and join instead.

SQL IN examples

Match a literal list

This returns customers in any of three countries. It reads better than three OR conditions and is easy to extend by adding a value.

SELECT customer_id, name, country
FROM customers
WHERE country IN ('USA', 'Canada', 'Mexico');

Exclude a list with NOT IN

The negated form returns rows not matching any value. With a literal list of non-NULL values this is safe.

SELECT product_id, name, category
FROM products
WHERE category NOT IN ('Discontinued', 'Draft');

IN with a subquery

The inner query produces the set of customer ids with large orders, and the outer query returns those customers. The inner query does not reference the outer one, so it runs independently.

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE total > 1000
);

Multi-column IN

Some databases accept a row value on the left, comparing pairs at once. This checks product and warehouse combinations together, avoiding a pair of separate conditions that would match cross combinations.

SELECT *
FROM inventory
WHERE (product_id, warehouse_id) IN ((1, 10), (2, 20), (3, 10));

Where SQL dialects differ

PostgreSQL: Supports row-value IN such as (a, b) IN ((1, 2), (3, 4)). = ANY(array) is an alternative that works with array parameters.

MySQL: Row-value IN is supported. Long lists of constants are handled efficiently with a binary search, and the optimizer can use an index for them.

SQL Server: Does not support row-value IN; use EXISTS or a join against a VALUES table. Very large lists can hit a parameter limit of 2100 per request.

SQLite: Supports row values from version 3.15 (2016). The default limit on variables in a query is 32766 from version 3.32, and 999 in older releases.

Oracle: A literal IN list is limited to 1000 expressions, raising ORA-01795 beyond that. Split the list or use a subquery.

Common mistakes

  • Using NOT IN with a subquery that may return NULL. Nothing matches, so the result is empty; prefer NOT EXISTS.
  • Including a very long literal list. Hit the size limits, or poor plans, instead of loading values into a table.
  • Mixing types in the list, such as strings in a numeric column, which causes implicit conversions or errors.
  • Expecting IN to match NULL. NULL IN (NULL) is unknown, so add an explicit OR col IS NULL.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator