SQL IN: list-based row filtering

The SQL IN operator tests whether a value matches any element in a list. A SQL WHERE IN predicate replaces long OR chains when filtering by a known set of discrete values — status codes, category names, region IDs. Use a SQL WHERE IN list when the values are known up front and the condition should stay readable as it grows.

SQL IN Example for List Matching

Output:

+----------+-------------+
| name     | category    |
+----------+-------------+
| Eraser   | stationery  |
| Keyboard | electronics |
| Mouse    | electronics |
| Notebook | stationery  |
+----------+-------------+
4 row(s)

How This Example Works

1. CREATE TABLE and INSERT populate a products table with five rows across three categories.
2. WHERE category IN ('electronics', 'stationery') matches any row whose category equals one of the listed values. Logically, it’s equivalent to category = 'electronics' OR category = 'stationery'.
3. The furniture row is excluded because its category isn’t in the list.
4. ORDER BY name makes the output deterministic.

IN with a literal list is equivalent to chained OR comparisons, but scales better as the set grows — adding a value means one more item in the list, not another OR clause.

What Is the SQL IN Operator?

The SQL IN operator is shorthand for multiple equality comparisons joined with OR. WHERE x IN (a, b, c) is logically identical to WHERE x = a OR x = b OR x = c. Under SQL’s three-valued logic, if no list element matches and no element is NULL, IN returns false. If any element is NULL and no exact match exists, the result is unknown; in a WHERE clause, unknown filters the row out.

Handling an Empty SQL WHERE IN List

Most databases reject WHERE col IN () as a syntax error, which often shows up when generating a SQL WHERE IN list dynamically. Handle the empty case explicitly: skip the filter, or force an always-false predicate like WHERE 1 = 0 when an empty list should return no rows.

SQL NOT IN and the NULL Trap

SQL NOT IN inverts the check: it returns true only when the value is unequal to every element in the list. A single NULL in the list can flip the result to unknown because x <> NULL is always unknown, and WHERE treats unknown as false:

-- Returns 0 rows: the NULL makes the predicate UNKNOWN
SELECT name
FROM products
WHERE category NOT IN ('electronics', NULL);

When the list comes from a subquery, filter NULLs inside the subquery, or use NOT EXISTS for NULL-safe semantics.

IN With Subqueries

IN also accepts a subquery instead of a literal list. Conceptually, the subquery produces a set of values, and the outer query checks membership:

SELECT name FROM products
WHERE category IN (
  SELECT category FROM products WHERE name LIKE '%board%'
);

For small, non-correlated subqueries this works well. When the subquery returns many rows or involves correlation, an EXISTS predicate or a JOIN often performs better because the optimizer can apply semi-join strategies.

IN vs EXISTS vs JOIN

Rule: use IN for discrete value sets. Switch to EXISTS when the subquery is correlated or nullable. Use a JOIN when you need data from both sides.

Performance Notes

IN on an indexed column is sargable — the optimizer can perform index lookups for each list value. Some engines sort literal IN lists internally for binary-search evaluation. Very large literal lists (hundreds of values) can slow down query parsing and plan generation; load those values into a temporary table and JOIN instead.

FAQ

Is SQL IN the same as multiple OR conditions?

Logically, yes. WHERE x IN (1, 2, 3) equals WHERE x = 1 OR x = 2 OR x = 3. The optimizer typically produces the same execution plan for both forms, but IN is shorter and easier to maintain as the list grows.

Why does NOT IN return no rows when the subquery contains NULL?

NOT IN expands to a chain of AND comparisons: x <> a AND x <> b AND x <> NULL. Since x <> NULL is always unknown, the whole expression is never true. Filter NULLs from the subquery or use NOT EXISTS to avoid this.

Can I use IN with a subquery?

Yes. WHERE col IN (SELECT col FROM other_table) checks membership against the subquery result. For small, non-nullable result sets this is efficient. For large or nullable sets, EXISTS or a JOIN often gives the optimizer more flexibility.