SQL Reference ยท Operators and Predicates
SQL IN operator
Reference for the SQL IN operator: test a value against a list or a subquery, NOT IN, how NULL changes the result, and dialect and performance notes.
The IN operator tests whether a value equals any member of a list. status IN ('shipped', 'delivered') is a shorter, easier-to-read way to write a chain of = comparisons joined by OR. The list can be written out as literals or produced by a subquery.
NOT IN is the opposite, but it has a well-known trap: if the list contains a NULL, NOT IN never returns true. See the usage notes below before you use it with a subquery.
SELECT name FROM products WHERE category_id IN (1, 4);
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
expression [NOT] IN (value1, value2, ...) expression [NOT] IN (subquery)
| Part | Description |
|---|---|
expression |
The value to test: a column, literal or any expression. |
value1, value2, ... |
A non-empty list of expressions. They are compared with =. |
subquery |
A SELECT that returns exactly one column. The test is true if any returned value equals expression. |
Returns: true, false or NULL (unknown). A WHERE clause keeps a row only when the result is true.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
IN with a list or subquery |
Yes | Yes | Yes | Yes | Yes |
Row values: (a, b) IN (SELECT x, y ...) |
Yes | Yes | Yes | No | Yes |
| Maximum list length | Limited by the statement size | Limited by the statement size | Limited by max_allowed_packet |
Limited by the batch size | 1,000 literal items |
IN with an array parameter |
No | = ANY($1) is the idiom |
No | Use a table-valued parameter | No |
-- SQL Server: row values are not supported, use EXISTS instead SELECT * FROM order_items AS oi WHERE EXISTS ( SELECT 1 FROM wanted AS w WHERE w.order_id = oi.order_id AND w.product_id = oi.product_id );
Usage notes
x IN (1, 2)meansx = 1 OR x = 2. Every comparison is a normal=, so aNULLon the left givesNULL, not false, and the row is dropped.NOT INandNULL.x NOT IN (1, NULL)meansx <> 1 AND x <> NULL. The second part is never true, so the whole test is never true. Remove theNULLs in the subquery, or useNOT EXISTS, which does not have this problem.INwith a plain value that isNULLin the list is harmless:2 IN (1, NULL, 2)is still true. Only a missing match turns intoNULL.- An empty list is a syntax error in most databases (
IN ()). SQLite accepts it and returns false. A subquery returning no rows is fine everywhere. - Types are compared as for
=, so mixing text and numbers follows each database's own conversion rules. Keep both sides the same type. - Performance: a list with a few values can use an index on the column. For large lists, join against a table or temporary table instead of building a huge statement. Most modern optimizers treat
IN (subquery)andEXISTSthe same way. - Duplicates in the list or the subquery do not duplicate result rows, unlike a join.