(X OR Y) vs. IN (X,Y)

Question

(X OR Y) vs. IN (X,Y)

Jurriaan Themmen

SSC-Addicted

Points: 457
More actions
December 20, 2006 at 1:21 am

#115539

Hi,
which of these two statements executes faster ?
WHERE <<FIELDNAME>> = (X OR Y)
or
WHERE <<FIELDNAME>> IN (X,Y)
Might be a nice one for QOD....

Regards
Jurriaan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No difference. the query plans for the two are identical. In fact, checking the query plan, the query parser expanded the second out into ORs.

I tried these two

SELECT

* FROM sysobjects WHERE xtype='P' OR xtype = 'S' OR xtype = 'U'

SELECT

* FROM sysobjects WHERE xtype IN ('P','U','S')

Identical reads, time, query cost. Checking the predicate of the clustered indx scan that was done, they were the same for both.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SELECT * FROM sysobjects WHERE xtype='P' OR xtype = 'S' OR xtype = 'U'

|--Compute Scalar(<Removed for brevity> )

|--Clustered Index Scan(OBJECT: ([master].[dbo].[sysobjects].[sysobjects]), WHERE: (([sysobjects].[xtype]='P' OR [sysobjects].[xtype]='S') OR [sysobjects].[xtype]='U'))

SELECT * FROM sysobjects WHERE xtype IN ('P','U','S')

|--Compute Scalar(<Removed for brevity> )

|--Clustered Index Scan(OBJECT: ([master].[dbo].[sysobjects].[sysobjects]), WHERE: (([sysobjects].[xtype]='S' OR [sysobjects].[xtype]='U') OR [sysobjects].[xtype]='P'))

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jurriaan Themmen SSC-Addicted Points: 457 More actions · Answer 3

Thanks.

I don't have access to SQL Query analyzer at the moment.

The IN looks like the way to go if the list of values becomes a bit longer.

Happy holidays

Jurriaan

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Now MY answer would be that the one using IN would work faster. Why? Because the first one would fail for a syntax error.

Jurriaan asked about this...

WHERE <> = (X OR Y)

NOT this:

WHERE <> = X OR <> = Y

Makes a difference.

-SQLBill