IN / NOT INQuery Techniques
Buy Me A Coffee

Test whether a value matches any value in a list or subquery. IN is shorthand for multiple OR equality conditions; NOT IN is the negation.

Tests whether a value matches any value in a list or subquery result, evaluating to TRUE if a match is found. IN is more readable than multiple OR conditions and often optimized similarly to EXISTS. Subqueries in IN can be slow with large result sets.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all Standard IN/NOT IN. Key gotcha: NOT IN with a subquery that returns any NULL will return no rows — because NULL != anything is UNKNOWN, not FALSE. Use NOT EXISTS instead when the subquery might return NULLs. IN list size is limited by max_allowed_packet.
PostgreSQL ✓ Supported all Standard IN/NOT IN. Same NULL trap: NOT IN with a NULL in the list or subquery returns no rows. Use NOT EXISTS for safety. PostgreSQL also supports = ANY(array) and <> ALL(array) as alternatives. IN with large static lists can be slower than = ANY(ARRAY[...]).
SQL Server ✓ Supported all Standard IN/NOT IN. Same NULL trap with NOT IN. No array type, so = ANY(array) is not available. IN list maximum is not explicitly documented but very large lists may cause parameter sniffing issues — a table variable or temp table is better for lists over ~1000 values.
Oracle ✓ Supported all Standard IN/NOT IN. Same NULL trap. IN list is limited to 1000 elements per expression — a hard limit. For larger lists, use IN with a subquery or a collection type. NOT EXISTS is recommended over NOT IN for correlated subqueries.
SQLite ✓ Supported all Standard IN/NOT IN. Same NULL trap with NOT IN. No array type. IN with a subquery is fully supported. No hard limit on IN list size (subject to SQLITE_MAX_VARIABLE_NUMBER, default 999 bound parameters).

Details

The NULL trap is the universal gotcha: NOT IN (subquery) returns zero rows if the subquery returns any NULL. This affects all engines identically. Use NOT EXISTS instead of NOT IN when the list or subquery may contain NULLs. Oracle's hard 1000-element IN list limit is unique and produces a runtime error, not a compile-time warning.

Standard Syntax

SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped'); SELECT * FROM orders WHERE customer_id NOT IN ( SELECT id FROM customers WHERE region = 'EU' );

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

Standard IN/NOT IN. Key gotcha: NOT IN with a subquery that returns any NULL will return no rows — because NULL != anything is UNKNOWN, not FALSE. Use NOT EXISTS instead when the subquery might return NULLs. IN list size is limited by max_allowed_packet.

SELECT * FROM orders WHERE status IN ('pending', 'processing'); SELECT * FROM orders WHERE id NOT IN (1, 2, 3); -- Safer alternative when NULLs possible: SELECT * FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM blacklist b WHERE b.id = o.customer_id );

PostgreSQL

Standard IN/NOT IN. Same NULL trap: NOT IN with a NULL in the list or subquery returns no rows. Use NOT EXISTS for safety. PostgreSQL also supports = ANY(array) and <> ALL(array) as alternatives. IN with large static lists can be slower than = ANY(ARRAY[...]).

SELECT * FROM orders WHERE status IN ('pending', 'processing'); -- Array alternative (often faster for large lists): SELECT * FROM orders WHERE status = ANY(ARRAY['pending', 'processing']); -- NOT IN NULL trap workaround: SELECT * FROM orders o WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.id = o.customer_id);

SQL Server

Standard IN/NOT IN. Same NULL trap with NOT IN. No array type, so = ANY(array) is not available. IN list maximum is not explicitly documented but very large lists may cause parameter sniffing issues — a table variable or temp table is better for lists over ~1000 values.

SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped'); SELECT * FROM orders WHERE id NOT IN (1, 2, 3); -- For large lists, use a table: DECLARE @ids TABLE (id INT); INSERT INTO @ids VALUES (1),(2),(3); SELECT * FROM orders WHERE id IN (SELECT id FROM @ids);

Oracle

Standard IN/NOT IN. Same NULL trap. IN list is limited to 1000 elements per expression — a hard limit. For larger lists, use IN with a subquery or a collection type. NOT EXISTS is recommended over NOT IN for correlated subqueries.

SELECT * FROM orders WHERE status IN ('pending', 'processing'); -- Hard limit: max 1000 items in IN list: -- SELECT * FROM t WHERE id IN (1, 2, ..., 1001); -- ORA-01795 error! -- Workaround for large lists: SELECT * FROM orders WHERE id IN (SELECT id FROM my_id_list_table);

SQLite

Standard IN/NOT IN. Same NULL trap with NOT IN. No array type. IN with a subquery is fully supported. No hard limit on IN list size (subject to SQLITE_MAX_VARIABLE_NUMBER, default 999 bound parameters).

SELECT * FROM orders WHERE status IN ('pending', 'processing'); SELECT * FROM orders WHERE id NOT IN (1, 2, 3); -- NULL trap demonstration: SELECT * FROM t WHERE x NOT IN (1, NULL); -- always returns 0 rows!