What goes wrong when an application sends WHERE id IN (...) with 50,000 literal values?

## The list is code, not data The crucial distinction is that `WHERE id IN (1, 2, 3, …)` puts the values into the SQL text. Everything the server does with SQL text — transmit, tokenise, parse, resolve names, plan — scales with that text. A 50,000-element list is hundreds of kilobytes to a few megabytes of statement, parsed from scratch on every call. Parameters are the opposite: `WHERE id = ?` has a fixed text and a small value alongside it. That also breaks reuse. Because the text differs whenever the list length differs, an application that fetches "the ids I happen to have" produces a different statement almost every time, so any work the server does per statement text is done again and again. Frameworks sometimes mitigate this by padding the list to fixed sizes (1, 10, 100, 500…), which is a hint about the underlying problem rather than a solution to it. ## Hard limits and estimation Engines differ, but several impose an explicit maximum on the number of expressions permitted in an `IN` list, or on statement length, and a query that worked at 900 values fails at 1,100. That failure typically shows up in production, at whatever data volume first crosses the line — a nasty class of bug because the code is "correct" and the test data was small. Even where no limit bites, the optimizer has to estimate how many rows `id IN (…50,000 values…)` returns. With a short list it can reason value by value; with a very long one it typically falls back to coarser assumptions. A poor estimate here propagates into join order and join method decisions for the rest of the query. ## Ask where the ids came from Most giant `IN` lists are a join performed in application code. The shape is: query one, `SELECT id FROM …`, materialise the result in a list, query two, `SELECT … WHERE fk IN (that list)`. The database could have done both in one statement: ```sql -- instead of two round trips with a 50k-element IN list SELECT o.* FROM orders o JOIN customers c ON c.id = o.customer_id WHERE c.region = 'EU'; -- or, when no customer columns are needed SELECT o.* FROM orders o WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.region = 'EU'); ``` This is the answer an interviewer is usually fishing for: the fix is not a bigger `IN` list, it is not having one. ## When the set really is external Sometimes the ids arrive from outside the database — an upload, a message, another service. Then give the engine a **relation**: **A temporary table.** Insert the ids (ideally in one batched insert), then join. The optimizer now sees a table it can hash, sort, or drive a nested loop from, and it can choose which side leads. ```sql CREATE TEMPORARY TABLE wanted (id BIGINT PRIMARY KEY); -- bulk-insert the ids, then: SELECT o.* FROM orders o JOIN wanted w ON w.id = o.id; ``` **A derived table built with `VALUES`.** Standard SQL allows a table value constructor in `FROM`; support and syntax vary between engines, and the values are still statement text, so this helps the *shape* of the plan more than the parse cost. It suits moderate lists — hundreds, not tens of thousands. ```sql SELECT o.* FROM orders o JOIN (VALUES (101), (102), (103)) AS w(id) ON w.id = o.id; ``` **A driver-level array or table-valued parameter**, where the engine and driver support it: one bound parameter carrying the whole set, so the statement text stays constant. The spelling is dialect-specific. **Chunking**, as a last resort: split into batches of a few hundred and run several statements. It keeps you under engine limits and keeps statement text bounded, but it multiplies round trips and makes a single consistent snapshot across chunks something you have to think about. ## Duplicates and NULLs in the list Two details worth knowing. Duplicate values in an `IN` list do not duplicate result rows — `IN` is an existence test, unlike a join to a table containing duplicate keys, which would fan out. And a NULL among the values changes nothing for `IN` but is a trap for `NOT IN`, where an unmatched row yields unknown rather than true, so nothing is returned at all. ## How to answer Name the three costs (statement text and parse work, engine limits, degraded estimates), then immediately ask where the ids came from, because the strongest answer converts the two-query pattern into one join. Offer the temporary-table or array-parameter route for genuinely external sets, and mention chunking as the pragmatic fallback rather than the design.