SQL Server: Select in vs or?

Question

Which is faster?

SELECT UserName
FROM dbo.UserTable
WHERE UserID in (1,3,4)

SELECT UserName
FROM dbo.UserTable
WHERE UserID = 1 
      OR UserID = 3
      OR UserID = 4

http://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause — rdo, Apr 18 '14 at 07:54

score 25 · Accepted Answer · answered Jan 21 '10 at 16:11

25

Due to Sql Server's optimization of queries these will run at the same speed since they are logically equivalent.

i favor the IN syntax for brevity and readability though.

answered Jan 21 '10 at 16:11

Paul Sasik

76,452
19
147
184

1

Rather then Or/In is there any way to query? that will perform faster? – Worgon Jun 11 '13 at 06:33
1

@Worgon if the number of values is large you could put the distinct values into an indexed temp table and join on that. – Martin Smith Sep 13 '14 at 19:18

score 7 · Answer 2 · answered Jan 21 '10 at 16:13

7

Actually it is the same.

If you display the estimated execution plan you will see that it is performing the same action.

answered Jan 21 '10 at 16:13

David Espart

11,142
7
35
49

score -1 · Answer 3 · answered Apr 30 '13 at 18:46

-1

This is relevant when it comes to producing SQL via Linq. There are some instances where the sql created is in the form of: field='xxx' OR field='yyy'.

answered Apr 30 '13 at 18:46

Herbey Zepeda

1

Can you please clarify that? I have no idea what you are saying. – tbodt Sep 13 '14 at 22:18

SQL Server: Select in vs or?

3 Answers3

Linked