Sql server 'in' or 'or' - which is the fastest

Question

So, I have a request:

select distinct(a) from mytable where b in (0,3) 

Which will be faster, higher or

 select distinct(a) from mytable where b = 0 or b = 3 

Is there a general rule?

thanks

Answer 1

Both IN and OR will execute the query for b = 0 , followed by one for b = 3 , and then merge the merge on the two result sets and finally filter out any duplicates.

With IN duplicates don't really make sense, because b cannot be 0 and 3 , but the fact is that IN will be converted to b = 0 OR b = 3 , and with OR , duplicates make sense because you could would have b = 0 OR a = 3 , and if you joined two separate result sets, you could get duplicates for each record that meet all the criteria.

Thus, re-filtering will always be performed regardless of whether you use IN or OR . However, if from the very beginning you know that you will not have duplicates, which usually happens when using IN , then you can get some performance using UNION ALL , which does not filter out duplicates:

 select distinct(a) from mytable where b = 0 UNION ALL select distinct(a) from mytable where b = 3 

Answer 2

As far as I know, IN converted to OR . Thus, the performance is the same. Only a shorter way to write this.

Answer 3

Hopefully in this simple example there will be no difference which version you use (since the query optimizer should turn them into equivalent queries under the hood), however it is likely that this will depend on the indexes you have on mytable . I suggest you run both queries in Sql Server Management Studio after turning on “Include the actual execution plan” and compare the results to determine which query has the lowest “cost” in your scenario.

For this:

• Place the request in a new Sql Sever Management Studio request window
• Right-click on a window in the space that you typed in
• Click Enable Actual Execution Plan
• Run the query as usual.

At the bottom of the "results" half of the window will now have a 3rd tab showing the "Execution Plan", which should contain two "flowcharts", one for the first request and the second for the second. If they are identical, then Sql Server treats the two queries as equivalent, and therefore you should choose any form that you and / or your colleagues prefer.