SQL IN Clause — Complete Guide with Examples for MySQL, PostgreSQL & More
The SQL IN clause filters rows where a column value matches any value in a list or subquery. It is one of the most common SQL constructs — used for batch lookups, multi-value filters, and replacing long chains of OR conditions. This guide covers everything: syntax, subqueries, NOT IN, NULL pitfalls, performance, the Oracle 1000-item limit, and parameterized queries across MySQL, PostgreSQL, SQL Server, Oracle, and SQLite.
WHERE
IN clause always appears in a WHERE (or HAVING) clause to filter rows
OR
IN replaces long OR chains — WHERE id IN (1,2,3) equals id=1 OR id=2 OR id=3
1000
Oracle hard limit — IN clause cannot exceed 1000 items without workarounds
SQL IN Clause — Basic Syntax
The IN clause matches a column against a list of values. Any row where the column equals one of the listed values is included in the result set.
-- Basic IN clause with a literal list
SELECT * FROM users
WHERE id IN (1, 2, 3, 4, 5);
-- String values — must use quotes
SELECT * FROM products
WHERE category IN ('Electronics', 'Clothing', 'Books');
-- Multiple columns with IN (use tuples — MySQL and PostgreSQL)
SELECT * FROM orders
WHERE (status, region) IN (('shipped', 'US'), ('pending', 'EU'));
-- IN with NULL — NULL never matches, use IS NULL separately
SELECT * FROM users
WHERE role IN ('admin', 'editor') OR role IS NULL;IN replaces OR chains
WHERE id IN (1,2,3) is exactly equivalent to WHERE id = 1 OR id = 2 OR id = 3. IN is shorter, more readable, and easier to maintain when the list grows.
Case sensitivity depends on collation
String matching in IN is case-sensitive or case-insensitive depending on the database collation. MySQL with utf8mb4_general_ci is case-insensitive; PostgreSQL is case-sensitive by default. 'Admin' and 'admin' may or may not match.
Duplicates in the list are ignored
WHERE id IN (1, 1, 2, 2, 3) works fine — the database treats it as WHERE id IN (1, 2, 3). No need to deduplicate the list in the query itself, though it is good practice.
Works with any data type
IN works with integers, strings, dates, UUIDs, and any comparable type. The values in the list must match (or be implicitly convertible to) the column data type.
NOT IN — Exclude a List of Values
-- Exclude specific statuses
SELECT * FROM orders
WHERE status NOT IN ('cancelled', 'refunded', 'draft');
-- NOT IN with a subquery — users NOT in the premium tier
SELECT * FROM users
WHERE id NOT IN (
SELECT user_id FROM subscriptions WHERE tier = 'premium'
);NOT IN with NULL — always returns no rows
NULL value — even one — NOT IN returns no rows at all. This is because x NOT IN (1, NULL) evaluates as x != 1 AND x != NULL, and comparing anything to NULL is always unknown (never true). Use NOT EXISTS or filter out NULL from the subquery when NOT IN involves nullable columns.NOT IN with nullable subquery — the NULL trap
NOT IN with nullable column — silently returns 0 rows
-- Returns NO rows if subscriptions.user_id contains any NULL
SELECT * FROM users
WHERE id NOT IN (
SELECT user_id FROM subscriptions -- user_id might be NULL!
);Filter NULLs from subquery OR use NOT EXISTS
-- Safe: filter out NULLs from the subquery
SELECT * FROM users
WHERE id NOT IN (
SELECT user_id FROM subscriptions WHERE user_id IS NOT NULL
);
-- Or use NOT EXISTS — handles NULLs correctly
SELECT u.* FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM subscriptions s WHERE s.user_id = u.id
);IN with Subqueries
-- Non-correlated subquery — runs once, result cached
SELECT * FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE country = 'US' AND verified = 1
);
-- Correlated subquery — runs for every row (use carefully on large tables)
SELECT * FROM products p
WHERE id IN (
SELECT product_id FROM order_items oi
WHERE oi.quantity > 100 AND oi.order_id = p.latest_order_id
);
-- IN vs EXISTS — prefer EXISTS for large subquery results
-- EXISTS stops scanning after the first match; IN loads the full list
SELECT * FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 500
);
-- IN with CTE (Common Table Expression) — PostgreSQL, MySQL 8+, SQL Server
WITH high_value_customers AS (
SELECT customer_id FROM orders
WHERE total > 1000
GROUP BY customer_id
HAVING COUNT(*) >= 3
)
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM high_value_customers);Performance — IN vs JOIN vs EXISTS
IN with a short literal list — fastest
For a short list of literal values (< 100 items), IN is the fastest and clearest approach. Modern query optimizers convert IN to a hash lookup or range scan depending on the values.
IN vs EXISTS for subqueries
For large subquery results, EXISTS is often faster because it stops after the first match. IN loads the full subquery result into memory. On small subquery results, the difference is negligible.
IN vs JOIN
JOIN is better when you need columns from the joined table. IN/EXISTS is better for pure filtering. For very large lists (10,000+ items), a temporary table + JOIN often outperforms a literal IN list.
Index use with IN
A B-tree index on the filtered column is used for IN clause lookups. MySQL and PostgreSQL both use index range scans for IN lists. Add an index on the filtered column for large table performance.
-- Instead of IN (id1, id2, ..., id10000) which may be slow:
-- Create a temp table and JOIN — much faster for very large lists
CREATE TEMPORARY TABLE temp_ids (id INT PRIMARY KEY);
INSERT INTO temp_ids VALUES (1),(2),(3); -- or bulk insert
SELECT u.* FROM users u
INNER JOIN temp_ids t ON u.id = t.id;
-- Clean up
DROP TEMPORARY TABLE temp_ids;
-- PostgreSQL equivalent using VALUES
SELECT u.* FROM users u
WHERE u.id IN (VALUES (1),(2),(3),(4),(5)); -- PostgreSQL supports thisOracle 1000-Item IN Clause Limit
Oracle raises ORA-01795 when IN clause exceeds 1000 items
IN clause literal lists to 1000 items. Exceeding this limit raises ORA-01795: maximum number of expressions in a list is 1000. Use one of the workarounds below for large lists.-- Workaround 1: Split into multiple IN clauses connected with OR
SELECT * FROM users
WHERE id IN (1,2,3, ... ,1000) -- first 1000
OR id IN (1001,1002, ... ,2000) -- next 1000
OR id IN (2001,2002, ... ,3000); -- next 1000
-- Workaround 2: Use a temporary table and JOIN
INSERT INTO temp_ids (id) VALUES (1),(2),(3),...;
SELECT u.* FROM users u JOIN temp_ids t ON u.id = t.id;
-- Workaround 3: Use BETWEEN for consecutive ranges (range compression)
-- Instead of IN (1,2,3,...,1000) use:
SELECT * FROM users
WHERE id BETWEEN 1 AND 1000
OR id BETWEEN 2001 AND 3000;
-- Workaround 4: Use an inline view (works in Oracle for expression lists)
SELECT * FROM users
WHERE id IN (
SELECT column_value FROM TABLE(SYS.ODCINUMBERLIST(1,2,3,...))
);Parameterized IN Clause for Prepared Statements
// ---- MySQL (mysql2) — uses ? placeholders ----
const ids = [1, 2, 3, 4, 5];
const placeholders = ids.map(() => '?').join(', ');
const [rows] = await connection.query(
`SELECT * FROM users WHERE id IN (${placeholders})`,
ids
);
// ---- PostgreSQL (pg) — uses $1, $2, $3 placeholders ----
const ids = [1, 2, 3, 4, 5];
const placeholders = ids.map((_, i) => `$${i + 1}`).join(', ');
const { rows } = await pool.query(
`SELECT * FROM users WHERE id IN (${placeholders})`,
ids
);
// ---- SQL Server (mssql) — use TVP or IN with parameters ----
const request = new sql.Request();
ids.forEach((id, i) => request.input(`id${i}`, sql.Int, id));
const placeholders = ids.map((_, i) => `@id${i}`).join(', ');
const result = await request.query(
`SELECT * FROM users WHERE id IN (${placeholders})`
);
// ---- Python (psycopg2 for PostgreSQL) ----
# psycopg2 supports passing a list directly with %s
cursor.execute(
"SELECT * FROM users WHERE id IN %s",
(tuple(ids),) # note: must be a tuple, not a list
)Use ANY($1) in PostgreSQL instead of dynamic IN placeholders
PostgreSQL supports WHERE id = ANY($1) with a single array parameter instead of building a dynamic placeholder string. Pass ids as an array directly: pool.query("SELECT * FROM users WHERE id = ANY($1)", [ids]) — cleaner code, same performance, works with arrays of any size without string concatenation.
Database-Specific IN Clause Syntax
-- MySQL — backtick identifiers, LIMIT/OFFSET pagination
SELECT * FROM `users`
WHERE `role` IN ('admin', 'editor')
LIMIT 100 OFFSET 0;
-- PostgreSQL — double-quote identifiers, ANY() alternative
SELECT * FROM "users"
WHERE "role" IN ('admin', 'editor');
-- PostgreSQL ANY() alternative (accepts array parameter):
WHERE "role" = ANY(ARRAY['admin', 'editor'])
-- SQL Server — bracket identifiers, TOP instead of LIMIT
SELECT TOP 100 * FROM [users]
WHERE [role] IN ('admin', 'editor');
-- Oracle — double-quote identifiers, ROWNUM for pagination
SELECT * FROM "users"
WHERE "role" IN ('admin', 'editor')
AND ROWNUM <= 100;
-- SQLite — no identifier quoting required for most names
SELECT * FROM users
WHERE role IN ('admin', 'editor')
LIMIT 100;