Great question! Let’s break down how these two syntaxes stack up when dealing with large sets of static values (hundreds to thousands of entries). First, let’s confirm the equivalent queries we’re comparing:

-- IN syntax with static values
SELECT col1, col2, col3 
FROM your_table 
WHERE col1 IN (1, 2, 3, ..., 999);

-- Equivalent EXISTS syntax using VALUES clause
SELECT col1, col2, col3 
FROM your_table 
WHERE EXISTS (
    SELECT 1 
    FROM (VALUES (1),(2),...,(999)) AS t(v) 
    WHERE t.v = your_table.col1
);

Small to Medium Value Lists (Up to ~1000 Values) #

For smaller lists, the performance gap between the two is negligible—you’ll barely notice a difference in most cases:

• IN behavior: Most databases (MySQL, PostgreSQL, SQL Server) optimize the value list into a hash set or sorted list. If col1 has an index, the database will use it to quickly look up matches; without an index, it’ll do a full table scan paired with a hash match or linear check against the list. The IN syntax is more concise, and optimizers have mature rules for handling this pattern.
• EXISTS behavior: The VALUES clause creates an in-memory temporary table. The database will join this temp table to your main table using strategies like nested loops or hash joins. While slightly more verbose, it performs almost identically to IN here.

Large Value Lists (1000+ Values) #

This is where the differences become meaningful:

• IN limitations: Many databases impose hard limits on IN list length. For example, MySQL defaults to a 1000-value cap (exceed it, and you’ll get an error). Even without hard limits, extremely long lists slow down query parsing and execution plan generation—optimizers spend more time sorting or hashing thousands of values, and some databases may even fall back to less efficient execution strategies.
• EXISTS advantages: The VALUES-based temp table avoids length restrictions entirely. Optimizers can treat this temp table like any other small table: they can analyze its statistics (row count, value distribution), pre-process it (e.g., deduplicate values, sort), and choose efficient join methods like hash joins or merge joins. For very large lists, this leads to more stable, predictable performance compared to IN.
• Index impact: If col1 has an index, both syntaxes benefit, but EXISTS often shines here. The pre-sorted temp table can be merged with the main table’s index in a merge join, reducing the number of index lookups needed.

Database-Specific Quirks #

Different databases handle these syntaxes with subtle differences:

• PostgreSQL: PostgreSQL optimizes long IN lists into arrays or temp tables automatically, so the gap is smaller. However, EXISTS still has an edge with extremely large lists, as optimizers can apply more granular join strategies.
• MySQL: The 1000-value IN limit is a big gotcha. To work around it, you’d have to split the list into multiple IN clauses joined with OR—which ruins execution plan efficiency. The EXISTS syntax avoids this entirely and uses reliable hash joins for the temp table.
• SQL Server: No hard IN length limit, but parsing thousands of values slows down plan generation. The EXISTS temp table (often treated as a memory-optimized table variable) lets the optimizer use better statistical data to pick the best join strategy.

Key Edge Cases #

• Duplicate values: IN automatically ignores duplicates (e.g., IN (1,1,2) behaves the same as IN (1,2)). For EXISTS, duplicate values in the VALUES list don’t change the result (since EXISTS stops at the first match), but pre-deduplicating the temp table will save minor processing overhead.
• Null values: Both syntaxes handle nulls consistently—col1 IN (1, NULL) won’t match nulls in col1, just like the EXISTS version’s t.v = your_table.col1 won’t match nulls (since NULL = NULL evaluates to unknown).

Final Takeaways #

• Small lists (<=1000 values): Use IN for its simplicity—performance is nearly identical to EXISTS.
• Large lists (>1000 values): Go with the EXISTS syntax. It avoids database-specific limits, offers more stable performance, and plays nicer with optimizer strategies for big datasets.
• Always index: Regardless of which syntax you choose, make sure col1 has an index—this will drastically speed up both queries.

内容的提问来源于stack exchange，提问作者Oliver