An application builds WHERE id IN (...) with 20,000 literal ids. What breaks, and how would you rewrite it?

seniorshould knowfreq 34%

answer

Ask who controls how long the list gets
The list is data living inside the statement text
Every different length is a different statement
Move the ids into something you can join or test against

basics

~20 s

Very large literal lists hit statement-size and bind-parameter limits, and every different list length is a different statement text the engine must parse afresh. Stage the ids as a set the query can join or test against, or send them in bounded batches.

solid answer

~50 s

Three things go wrong. First, limits: engines and drivers cap statement size and the number of bind parameters, and a list that grows with the caller's input eventually crosses one of them — usually in production, not in test. Second, parse cost: a list of 20,000 literals is a large statement to transmit and parse, and because the text differs for every list length, the engine treats each variant as a new statement. Third, maintainability — the query text becomes data. The rewrite is to make the id set a set: stage the ids in a temporary table (or pass them as a parameterised set your engine supports) and express membership as a join or a WHERE EXISTS against it. If staging is not available, chunk the list into fixed-size batches sized well under your engine's limit and union the results. Deduplicate the ids either way: IN never multiplies rows, but a join to a value set with duplicates does.

Three things go wrong. First, **limits**: engines and drivers cap statement size and the number of bind parameters, and a list that grows with the caller's input eventually crosses one of them — usually in production, not in test. Second, **parse cost**: a list of 20,000 literals is a large statement to transmit and parse, and because the text differs for every list length, the engine treats each variant as a new statement. Third, **maintainability** — the query text becomes data. The rewrite is to make the id set a *set*: stage the ids in a temporary table (or pass them as a parameterised set your engine supports) and express membership as a join or a `WHERE EXISTS` against it. If staging is not available, chunk the list into fixed-size batches sized well under your engine's limit and union the results. Deduplicate the ids either way: `IN` never multiplies rows, but a join to a value set with duplicates does.

code

sql · 8 lines

sql

-- fragile: text and parameter count grow with the caller's input
SELECT * FROM orders WHERE id IN (1, 2, 3 /* ...20,000 more... */);

-- stable: ids live in data, statement text never changes,
-- and EXISTS cannot duplicate an order even if wanted_ids has repeats
SELECT o.*
FROM orders o
WHERE EXISTS (SELECT 1 FROM wanted_ids w WHERE w.id = o.id);

go deeper

for a junior

Know that IN lists are for small, human-sized sets and that very large generated lists are a smell. You are not expected to design the rewrite.

for a middle

Explain the concrete failure modes — statement-size and parameter limits, a new statement text per list length, payload size — and be able to write the EXISTS-against-a-staged-set version.

for a senior

Diagnose first, then rewrite: pick between staging, a set-valued parameter and fixed-size chunking based on the engine and the call pattern, and protect the refactor against duplicate-driven row inflation.

for a principal

Set the boundary as a policy: any predicate whose length scales with caller input is a latent production failure, so define where bulk id sets get staged and how batch sizes are chosen once, rather than per team.

The shape of the problem

sql

SELECT * FROM orders WHERE id IN (1, 2, 3, /* ... */ , 20000);

Nothing about this is semantically wrong — it is still just an OR chain of equality tests. What breaks is everything around the semantics.

What actually goes wrong

Hard limits. Engines and client drivers impose caps: on the maximum size of a statement, on the number of bind parameters a single statement may carry, sometimes on expression nesting depth. The exact numbers differ by engine and by driver, so the only safe assumption is that a cap exists and that a list whose length is controlled by the caller will eventually reach it. The failure mode is bad: it works for the 50-id case in every test you wrote, and fails the day a customer selects everything.

Statement churn. The text of the query is different for 500 ids than for 501. Every distinct length is, to the engine, a distinct statement that must be parsed and planned from scratch — and if it caches statements, the cache fills with thousands of near-identical entries that will never be reused. Even where the ids are bound as parameters rather than inlined, the number of placeholders is part of the text.

Payload and readability. Megabytes of literals cross the wire on every call, logs become unusable, and the statement stops being reviewable — you can no longer see the query for the data embedded in it.

The rewrite: make the set a set

The underlying mistake is putting data in the statement text. Move it to where data belongs:

Stage the ids, then join or semi-join. Insert them into a temporary or working table (bulk-loaded or batch-inserted), then:

sql

SELECT o.*
FROM orders o
WHERE EXISTS (SELECT 1 FROM wanted_ids w WHERE w.id = o.id);

The statement text is now constant regardless of how many ids there are, which is the whole point: one parse, one plan shape, any cardinality.

A literal row set as a derived table, where the engine offers it and the list is merely large rather than enormous:

sql

-- PostgreSQL
SELECT o.*
FROM orders o
JOIN (VALUES (1),(2),(3)) AS w(id) ON w.id = o.id;

The spelling of a literal table constructor differs between engines, so treat this as a per-engine option rather than a portable idiom.

A set-valued parameter, if your engine and driver support passing a collection as one bind value. This keeps the statement text fixed with no staging table. It is a dialect feature — check what yours offers.
Chunking, as the low-effort fallback. Split the ids into fixed-size batches (a size you fix in code, comfortably under your engine's limits) and run the query once per batch, combining results in the application or with UNION ALL. A fixed batch size matters: it keeps the statement text stable across calls, whereas a ragged final batch reintroduces one extra variant.

Correctness caveat when you rewrite

IN is a predicate: it filters, never duplicates. A join is not: if the staged set contains an id twice, every matching order comes back twice, and a downstream SUM doubles. Whenever you convert an IN list into a join, either deduplicate the set (unique constraint on the staging table, or SELECT DISTINCT) or use the semi-join form WHERE EXISTS (…), which by definition returns each left row at most once. This is the single most common bug introduced by this refactor.

When a literal list is fine

Do not over-engineer. A handful to a few dozen values — status codes, a page's worth of ids, a fixed set of country codes — is exactly what IN lists are for, and a staging table there is more machinery than the problem deserves. The judgment call is about who controls the length: a list whose size is bounded by the schema or by a page size is fine; a list whose size is bounded only by how much the caller asked for is a defect waiting for a big customer.

Answering well

Diagnose before prescribing: name the limits, the per-length statement churn, and the payload. Then give the rewrite in order of preference — fixed statement text against a staged set, dialect set-parameter, chunking as fallback — and volunteer the duplicate-row trap. That last point signals you have actually done this refactor rather than read about it.

## The shape of the problem ```sql SELECT * FROM orders WHERE id IN (1, 2, 3, /* ... */ , 20000); ``` Nothing about this is semantically wrong — it is still just an OR chain of equality tests. What breaks is everything around the semantics. ## What actually goes wrong **Hard limits.** Engines and client drivers impose caps: on the maximum size of a statement, on the number of bind parameters a single statement may carry, sometimes on expression nesting depth. The exact numbers differ by engine and by driver, so the only safe assumption is that a cap exists and that a list whose length is controlled by the caller will eventually reach it. The failure mode is bad: it works for the 50-id case in every test you wrote, and fails the day a customer selects everything. **Statement churn.** The text of the query is different for 500 ids than for 501. Every distinct length is, to the engine, a distinct statement that must be parsed and planned from scratch — and if it caches statements, the cache fills with thousands of near-identical entries that will never be reused. Even where the ids are bound as parameters rather than inlined, the *number* of placeholders is part of the text. **Payload and readability.** Megabytes of literals cross the wire on every call, logs become unusable, and the statement stops being reviewable — you can no longer see the query for the data embedded in it. ## The rewrite: make the set a set The underlying mistake is putting data in the statement text. Move it to where data belongs: 1. **Stage the ids, then join or semi-join.** Insert them into a temporary or working table (bulk-loaded or batch-inserted), then: ```sql SELECT o.* FROM orders o WHERE EXISTS (SELECT 1 FROM wanted_ids w WHERE w.id = o.id); ``` The statement text is now constant regardless of how many ids there are, which is the whole point: one parse, one plan shape, any cardinality. 2. **A literal row set as a derived table**, where the engine offers it and the list is merely large rather than enormous: ```sql -- PostgreSQL SELECT o.* FROM orders o JOIN (VALUES (1),(2),(3)) AS w(id) ON w.id = o.id; ``` The spelling of a literal table constructor differs between engines, so treat this as a per-engine option rather than a portable idiom. 3. **A set-valued parameter**, if your engine and driver support passing a collection as one bind value. This keeps the statement text fixed with no staging table. It is a dialect feature — check what yours offers. 4. **Chunking**, as the low-effort fallback. Split the ids into fixed-size batches (a size you fix in code, comfortably under your engine's limits) and run the query once per batch, combining results in the application or with `UNION ALL`. A *fixed* batch size matters: it keeps the statement text stable across calls, whereas a ragged final batch reintroduces one extra variant. ## Correctness caveat when you rewrite `IN` is a predicate: it filters, never duplicates. A join is not: if the staged set contains an id twice, every matching order comes back twice, and a downstream `SUM` doubles. Whenever you convert an `IN` list into a join, either deduplicate the set (unique constraint on the staging table, or `SELECT DISTINCT`) or use the semi-join form `WHERE EXISTS (…)`, which by definition returns each left row at most once. This is the single most common bug introduced by this refactor. ## When a literal list is fine Do not over-engineer. A handful to a few dozen values — status codes, a page's worth of ids, a fixed set of country codes — is exactly what `IN` lists are for, and a staging table there is more machinery than the problem deserves. The judgment call is about *who controls the length*: a list whose size is bounded by the schema or by a page size is fine; a list whose size is bounded only by how much the caller asked for is a defect waiting for a big customer. ## Answering well Diagnose before prescribing: name the limits, the per-length statement churn, and the payload. Then give the rewrite in order of preference — fixed statement text against a staged set, dialect set-parameter, chunking as fallback — and volunteer the duplicate-row trap. That last point signals you have actually done this refactor rather than read about it.

You replace the IN list with a join to a staging table and the row count goes up. What happened?
The staged id set contains duplicates. `IN` is a predicate and can only keep or drop a row; a join pairs each left row with every matching right row, so a duplicated id doubles that order. Fix it by making the staging column unique, deduplicating on insert, or using `WHERE EXISTS (…)`, which returns each left row at most once.
If you chunk the list, why does a fixed batch size matter?
Because the statement text depends on the number of placeholders. Fixed-size batches produce one statement shape that the engine sees again and again; ragged batches produce a new shape per call. Pad or accept one extra shape for the final partial batch, and size the batch comfortably under the engine's parameter limit rather than at it.
When is a long literal IN list acceptable rather than a problem?
When its length is bounded by something other than caller input — a page size, a fixed enumeration, a schema-limited set. A few dozen values inline is readable and cheap. The risk appears when the size scales with what a user selected, because then the working case in test tells you nothing about the failing case in production.

saying these in an interview costs you the question

Assumes IN lists have no practical size limit
Rewrites to a join without deduplicating the id set
Thinks bound parameters remove the per-length statement variation
Sizes batches right at the engine's documented cap
Treats every short IN list as needing a staging table

An application builds WHERE id IN (...) with 20,000 literal ids. What breaks, and how would you rewrite it?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The shape of the problem

What actually goes wrong

The rewrite: make the set a set

Correctness caveat when you rewrite

When a literal list is fine

Answering well

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions5 questions

▾related topics4 topics

The shape of the problem

What actually goes wrong

The rewrite: make the set a set

Correctness caveat when you rewrite

When a literal list is fine

Answering well

answer

go deeper

context