May 16, 2008 at 8:57 am
Hi,
Does it matter in using OR or IN in a WHERE clause.
SELECT a.Col1, b.Col2
FROM dbo.Table1 a
INNER JOIN dbo.Table2 b ON a.Col1 = b.Col1
WHERE b.Col3 IN ('Q', 'P')
Vs
SELECT a.Col1, b.Col2
FROM dbo.Table1 a
INNER JOIN dbo.Table2 b ON a.Col1 = b.Col1
WHERE b.Col3 = 'Q' OR b.Col3 = 'P'
Thanks,
-R
May 16, 2008 at 9:33 am
AFAIK in this case it would be the same plan. An IN is a shortcut for multiple ORs.
May 16, 2008 at 9:37 am
I find "in" easier to read, in these cases. As far as SQL is concerned, both statements are the same.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 16, 2008 at 9:38 am
Steve, can you give an example where using IN Vs OR would be advantageous.
Thanks.
May 16, 2008 at 9:48 am
Neither one is the very best way to do things, but I took this query and ran it against Adventureworks. The execution plans were identical. Both used Index Seeks. The number of scans and reads were identical. The execution time was identical as was compile time. Depending on the data you're actually talking about, this doesn't have to make any difference, but, depending on the data, it could.
SELECT a.[AddressID],
s.[Name]
FROM [Person].[Address] a
INNER JOIN [Person].[StateProvince] s
ON a.[StateProvinceID] = s.[StateProvinceID]
WHERE s.[StateProvinceCode] IN ( 'OK', 'MA' )
SELECT a.[AddressID],
s.[Name]
FROM [Person].[Address] a
INNER JOIN [Person].[StateProvince] s
ON a.[StateProvinceID] = s.[StateProvinceID]
WHERE s.[StateProvinceCode] = 'OK'
OR s.[StateProvinceCode] = 'MA'
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 16, 2008 at 9:51 am
But, do it this way, and the execution time decreases radically. The number of reads is slightly higher though... for whatever that's worth:
SELECT a.[AddressID],
s.[Name]
FROM [Person].[Address] a
INNER JOIN [Person].[StateProvince] s
ON a.[StateProvinceID] = s.[StateProvinceID]
WHERE s.[StateProvinceCode] = 'OK'
UNION
SELECT a.[AddressID],
s.[Name]
FROM [Person].[Address] a
INNER JOIN [Person].[StateProvince] s
ON a.[StateProvinceID] = s.[StateProvinceID]
WHERE s.[StateProvinceCode] = 'MA'
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 19, 2008 at 1:54 pm
Grant Fritchey (5/16/2008)
But, do it this way, and the execution time decreases radically. The number of reads is slightly higher though... for whatever that's worth:
SELECT a.[AddressID],s.[Name]
FROM [Person].[Address] a
INNER JOIN [Person].[StateProvince] s
ON a.[StateProvinceID] = s.[StateProvinceID]
WHERE s.[StateProvinceCode] = 'OK'
UNION
SELECT a.[AddressID],
s.[Name]
FROM [Person].[Address] a
INNER JOIN [Person].[StateProvince] s
ON a.[StateProvinceID] = s.[StateProvinceID]
WHERE s.[StateProvinceCode] = 'MA'
Also, if your data supports it that you'd have each AddressID in one and only one StateProvinceCode, you could use a UNION ALL, which should help further.
May 20, 2008 at 12:52 am
In this case the T-SQL with ' IN ' looks better than ' OR ' till the execution plan is the same for both scripts!
I prefere the ' IN '
May 20, 2008 at 4:29 am
Its whatever your preference is.
With the OR statement, you will inadvertently end up writing more letters, but the net effect should remain the same.
Same reads, same amount of scans, same everything.
But there are cases where there may be a difference even though you could get the same results using the IN versus the OR.
Steve, it would be nice to get an example where the reads and scans differ to illustrate
May 20, 2008 at 7:07 am
Functionally they are the same.
I prefer the IN syntax as one of the risks with using OR is that if you have other WHERE clause predicates, which would probably be "AND" filters, you must be very, very, careful to put your "in" list equivalent (coded with ORs) in a proper set of parentheses. It is amazing what a missing, or improperly placed, set of parens will do when there is an OR operand in a WHERE clause.
May 20, 2008 at 7:10 am
Thanks John,
Agreed, functionally they do the same. Just call me a lazy developer with 200 projects, thats why I like the IN keyword.
But from a performance perspective?
May 20, 2008 at 7:11 am
pduplessis (5/20/2008)
Its whatever your preference is.With the OR statement, you will inadvertently end up writing more letters, but the net effect should remain the same.
Same reads, same amount of scans, same everything.
But there are cases where there may be a difference even though you could get the same results using the IN versus the OR.
Steve, it would be nice to get an example where the reads and scans differ to illustrate
Since there is no difference at all in OR vs IN, how would it be possible to have different reads and scans between the two? If you look at the execution plan, SQL turns IN into multiple ORs before it runs the query. They are, to the database, the same command. There is NO difference, and the IO is going to be the same for both.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2008 at 7:17 am
Ahhhhh, okey. Thanks for the reply....
I was asked in a job interview once from a performance perspective what my preference was, to which I had no reply.
The interviewer hammered on the point that the OR would be better.
Never quite could get anything that could substantiate his response.
May 20, 2008 at 8:02 am
Someone probably told him that. You'd be amazed what people will start to make up to fill in the gaps in their knowledge.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2008 at 8:05 am
Thats why I would rather come find out from the professionals and thank my lucky stars that I never got that job
😉
Once again, thanks....
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply