Performance difference between the IN and OR statements

Hi,
I would be grateful if someone could tell me if there is any performance
difference between the IN and OR statements when searching for multiple
values in the same field.
For example
type = 'DVD' Or type = 'CD' Or type = 'VIDEO'
and
type in ( 'DVD' , 'CD' , 'VIDEO' )
Is either of the above faster or more efficient than the other ?
Thanks
Mike TowersMike,
They'll probably resolve to the same execution method. Test it by using the
Display Estimated Exectution Plan in Query Analyzer. That being said all
things being equal...IN is easier to read.
HTH
Jerry
"Mike Towers" <MikeTowers@.discussions.microsoft.com> wrote in message
news:805D0A51-F7D7-4096-BB4A-BBD8D46213DE@.microsoft.com...
> Hi,
> I would be grateful if someone could tell me if there is any performance
> difference between the IN and OR statements when searching for multiple
> values in the same field.
> For example
> type = 'DVD' Or type = 'CD' Or type = 'VIDEO'
> and
> type in ( 'DVD' , 'CD' , 'VIDEO' )
> Is either of the above faster or more efficient than the other ?
> Thanks
> Mike Towers
>|||> I would be grateful if someone could tell me if there is any performance
> difference between the IN and OR statements when searching for multiple
> values in the same field.
Well, did you try it against a large data set? Did you compare the
execution plans?
A|||They do the same thing. IN will be internally converted to a number of OR's
(check the execution
plan and you will see).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Towers" <MikeTowers@.discussions.microsoft.com> wrote in message
news:805D0A51-F7D7-4096-BB4A-BBD8D46213DE@.microsoft.com...
> Hi,
> I would be grateful if someone could tell me if there is any performance
> difference between the IN and OR statements when searching for multiple
> values in the same field.
> For example
> type = 'DVD' Or type = 'CD' Or type = 'VIDEO'
> and
> type in ( 'DVD' , 'CD' , 'VIDEO' )
> Is either of the above faster or more efficient than the other ?
> Thanks
> Mike Towers
>sql