IN() vs. OR

[Bob Barrows [MVP]]

MDW wrote:

> Hey all.
>
> Assume I have two SQL statements:
>
> "SELECT Col1,Col2 FROM MyTable WHERE Col1 = 'Val1' OR Col1 = 'Val2'
> OR Col1 = 'Val3' OR Col1 = 'Val4'"
>
> and
>
> "SELECT Col1,Col2 FROM MyTable WHERE Col1
> IN('Val1','Val2','Val3','Val4')"
>
> Is there any difference in the recordet created by those statements?
> Is one preferable over another? Does than answer change if you've got
> a lot (say, more than 200) values?

It depends on the database. Most rdbms's (even Jet) provide the ability to
view and analyze the query plans generated by the query optimizers. I
suggest you utilize that functionality to answer your own question.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron Bertrand - MVP]

> Is there any difference in the recordet created by those statements?

No, they will return the same data.

> Is one preferable over another?

Depends on your criteria. They should generate the same query plan, at
least in SQL Server... an IN list will be translated by the query engine as
a series of OR clauses. But you can always test it yourself, with 4 values
or over 200.

> Does than answer change if you've got a lot (say, more than 200) values?

Again, depends on your criteria. If you're writing this statement manually,
obviously you wouldn't want to do either if there are over 200 values.
Hopefully you have iterative code that will generate the list/statement for
you. As for performance, see above, and test it yourself. As Bob
indicates, this is why performance/analysis tools come with the product.

REMEMBER! It is *ALWAYS* relevant to tell us what database you are using!!!

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]