Performance difference using IN or OR

Hello!
1. Is there a performance difference using the IN keyword or the OR keyword?
2. Does SQL Server internally convert one of the queries into another, e.g.
a query using IN into a query using OR?
3. When should I use OR and when IN (I know about using IN with subselects)?
For example look at those two simple queries:
USE AdventureWorks;
GO
SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.Title = 'Design Engineer'
OR e.Title = 'Tool Designer'
OR e.Title = 'Marketing Assistant';
GO
USE AdventureWorks;
GO
SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.Title IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant')
;
GO
Thanks
RobAFAIK
> 1. Is there a performance difference using the IN keyword or the OR
> keyword?
NO
> 2. Does SQL Server internally convert one of the queries into another,
> e.g.
> a query using IN into a query using OR?
YES (Not in the case of subqueries)
> 3. When should I use OR and when IN (I know about using IN with
> subselects)?
Personally, I use IN when there are 2 or more items in the list.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:6ADFDCAF-E3D8-4F93-9823-FF19C982C1A7@.microsoft.com...
> Hello!
> 1. Is there a performance difference using the IN keyword or the OR
> keyword?
> 2. Does SQL Server internally convert one of the queries into another,
> e.g.
> a query using IN into a query using OR?
> 3. When should I use OR and when IN (I know about using IN with
> subselects)?
> For example look at those two simple queries:
> USE AdventureWorks;
> GO
> SELECT FirstName, LastName, e.Title
> FROM HumanResources.Employee AS e
> JOIN Person.Contact AS c
> ON e.ContactID = c.ContactID
> WHERE e.Title = 'Design Engineer'
> OR e.Title = 'Tool Designer'
> OR e.Title = 'Marketing Assistant';
> GO
> USE AdventureWorks;
> GO
> SELECT FirstName, LastName, e.Title
> FROM HumanResources.Employee AS e
> JOIN Person.Contact AS c
> ON e.ContactID = c.ContactID
> WHERE e.Title IN ('Design Engineer', 'Tool Designer', 'Marketing
> Assistant');
> GO
> Thanks
> Rob
>
>|||You can run both the queries in one batch with the "show execution plan "
option ON and check the query cost relative to the bacth.. Both will have 50
%
Moreover you can check the PREDICATE in the execution plan for the query
using the IN clause. You will notice that it has substituted it with OR
conditions.
So using IN is not a problem, and its definitely more readable than the OR
clause.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||When thinking in sets, I prefer using EXISTS or filtering the results in the
JOIN clause.
With these two the comparison can be multi-dimensional, rather than
uni-dimensional as is the case with the IN and OR (i.e. only one condition i
s
checked).
Of course possible NULL values must be taken into account.
ML
http://milambda.blogspot.com/|||OR will likely give you better performance provided that you have
usable indexes on your table. IN is not a searchable argument (SARG)
which means that the query optimizer cannot use IN to analyze possible
indexes to use when selecting data. If your where clause used only the
IN expression, your rows would be returned using a table scan. However,
if you used OR instead, and you had an index on the columns in your OR
expression, the query optimizer would evaluate that expression against
your available indexes and possibly choose to use an index scan or
s.
I hope that all made sense. In any case, I would use OR in queries that
will be run often, but if you are just writing ad hoc reports and your
environment can afford it, IN should be fine to use if you want to save
yourself some typing.
HTH
Jason|||Jason,
Where did you come up with this unusual analysis of the optimizer?
Have you ever tested it? It is easily disproved. The optimizer has
no trouble using an index to find values from an IN.
Roy Harvey
Beacon Falls, CT
On 28 Jun 2006 10:49:57 -0700, "Jason" <jnorsworthy@.etrade.com> wrote:

>OR will likely give you better performance provided that you have
>usable indexes on your table. IN is not a searchable argument (SARG)
>which means that the query optimizer cannot use IN to analyze possible
>indexes to use when selecting data. If your where clause used only the
>IN expression, your rows would be returned using a table scan. However,
>if you used OR instead, and you had an index on the columns in your OR
>expression, the query optimizer would evaluate that expression against
>your available indexes and possibly choose to use an index scan or
>s.
>I hope that all made sense. In any case, I would use OR in queries that
>will be run often, but if you are just writing ad hoc reports and your
>environment can afford it, IN should be fine to use if you want to save
>yourself some typing.
>HTH
>Jason|||DOH!
Thanks for catching this one Roy, I had read this (or misread) a while
back in "Inside Microsoft SQL Server 2000" which is a GREAT book - if
you read it correctly :P. My analysis regarding SARGs is correct, but
due to line spacing and my haste in reading I mistakenly read/believed
that IN wasn't a SARG, however it is NOT IN that isn't a SARG - which
actually makes a lot more sense.
Sorry for any confusion!
Jason
Roy Harvey wrote:
> Jason,
> Where did you come up with this unusual analysis of the optimizer?
> Have you ever tested it? It is easily disproved. The optimizer has
> no trouble using an index to find values from an IN.
> Roy Harvey
> Beacon Falls, CT
>