Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT name FROM products WHERE category_id IN (1, 4);

Try it

SQL IN with a list of values ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL IN with a subquery, and the NOT IN trap ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
-- 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) means x = 1 OR x = 2. Every comparison is a normal =, so a NULL on the left gives NULL, not false, and the row is dropped.
  • NOT IN and NULL. x NOT IN (1, NULL) means x <> 1 AND x <> NULL. The second part is never true, so the whole test is never true. Remove the NULLs in the subquery, or use NOT EXISTS, which does not have this problem.
  • IN with a plain value that is NULL in the list is harmless: 2 IN (1, NULL, 2) is still true. Only a missing match turns into NULL.
  • 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) and EXISTS the same way.
  • Duplicates in the list or the subquery do not duplicate result rows, unlike a join.