SQL fixed-value IN() vs. INNER JOIN performance

Question

In answer to this SQL question, I've encountered a statement that fixed-value IN() operator is much slower than INNER JOIN with the same content, to the point that it is better to create temporary table for the values and JOIN them. Is it true (in general, with MySQL, any other SQL engine) and if yes - why? Intuitively, IN should be faster - you're comparing the potential match with a fixed set of values, which are already in memory and in needed format, while with JOIN you'd have to consult the indexes, potentially load data from disk, and perform other operations that may be not needed with IN. Am I missing something important?

Note that unlike this question and it's many duplicates, I'm talking about IN() having fixed set of values, not subquery.

Answer 1

This relates to the length of the IN clause - and what is sometimes called a BUG in MySQL.

MySQL seems to have a low threshold for IN clauses, when it will swap to a TABLE/INDEX SCAN instead of collecting multiple partitions (one per IN item) and merging them.

With an INNER JOIN, it is almost always forced to use a direct row-by-row in JOIN collection, which is why it is sometimes faster

Refer to these MySQL manual pages

• In Subquery considerations
• In (constant list) performance

I could be wrong since it seems to imply that IN (constant value list) should always use a binary search on each item...