IN or OR - Which is better performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

IN or OR - Which is better performance

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-11-17 : 04:55:32

Below where clause which one is better performance ???? .. how the execution flow will happen...

where column in ('a','b','c','d')

where column = 'a' or clumn = 'b' or column = 'c' or column = 'd'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 05:02:25

I dont think there will be much changes in performance between the two. Why dont you try running them and see it for yourself by analysing execution plan.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-17 : 05:03:36

They are the same. An IN clause is translated to a series of OR clauses.
Run the query and analyze the execution plan.

set showplan_text on

SELECT	*
FROM	TallyNumbers
WHERE	Number IN (1, 2, 3, 4)

SELECT	*
FROM	TallyNumbers
WHERE	Number = 1
	OR Number = 2
	OR Number = 3
	OR Number = 4


set showplan_text off

|--Clustered Index Seek(OBJECT:([Test].[dbo].[TallyNumbers].[PK_TallyNumbers]), SEEK:([Test].[dbo].[TallyNumbers].[Number]=(1) OR [Test].[dbo].[TallyNumbers].[Number]=(2) OR [Test].[dbo].[TallyNumbers].[Number]=(3) OR [Test].[dbo].[TallyNumbers].[Number]=(4)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([Test].[dbo].[TallyNumbers].[PK_TallyNumbers]), SEEK:([Test].[dbo].[TallyNumbers].[Number]=(1) OR [Test].[dbo].[TallyNumbers].[Number]=(2) OR [Test].[dbo].[TallyNumbers].[Number]=(3) OR [Test].[dbo].[TallyNumbers].[Number]=(4)) ORDERED FORWARD)

E 12°55'05.63"
N 56°04'39.26"

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-11-17 : 05:20:03

I would just say the in clauses looks neater and will be easier to read for some looking at your code later on perhaps

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-17 : 05:25:12

Also see http://weblogs.sqlteam.com/peterl/archive/2007/09/28/Save-some-time-and-key-typing.aspx
for other uses of IN.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources