do ins become a long list of or

sorry but this is impossible to search upon, but does an IN get expaned out to a long list of ORs?
And is there a performance diffeference?

please hurry i am meant to be coding after my lunch break finishes.
(falls off the chair at her own hilarious joke)

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.

Oh dear, I wrote such a mess once again!

Chris has it right, of course, ORs and IN are equivalent. I suppose that is true not only in Oracle, but in most databases, after all the two things lead to the same result.

In my previous post, I was elaborating on the difference between having a few and a lot of values in the IN clause. Clearly, that has no influence on decisions between IN and OR.

see i am deciding between getting our sql generator to produce a possibly very long list of ORs (reusing existing code) or adding in IN(?,?,? ...).

there could be 100 items in the list.

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.

(What database are you on?)

mssql, oracle and db2, so we have to be careful

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.