This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
I'd say it is equivalent to a list of ORs. The exact handling depends on the database probably. For example, Oracle would probably take all the values into account when guessing cardinality of the (sub)query, possibly creating a different plan if you use just a few values in the IN cluase, versus a few hundred.
According to Tom Kyte's book "Troubleshooting Oracle Performance" (p 386-387), each comparison gets treated separately via an "INLIST ITERATOR" operation i.e. it's handled as if it were a set of "OR col = value" conditions (his examples are searching on indexed columns, so they are actually looking in the index).
So on Oracle at least, it looks like IN does turn into a bunch of ORs (even if IN is still much easier to write/read).
I never knew that!
PS: I think this is specific to "IN (val1, val2, ...)" comparisons , as I think Oracle will try to be smart when dealing with "IN ( sub-query )" e.g. using hashes etc, and there are often ways to make your sub-query much more efficient anyway.
Beware there could be some limit. Oracle allows at most 1000 items in the IN list. Other databases could have limitations too, including a limit on the length of the SQL query. And I've a few times triggered the dreaded ORA-600 error (internal error) by submitting a long (and complicated) query to Oracle.
If you have a lot of "OR" conditions, you might consider breaking them into separate SELECTs to eliminate the ORs and make the alternatives easier to read/write/generate, then "UNION ALL" the results within a single SQL statement. But how this performs will depend on your DB and the rest of your query conditions.