SQL SERVER – Performance Comparison IN vs OR

Some questions never get old and some question and I believe we will be discussing them for many more years in the future. Just other days, I received this question during my SQL Server Performance Tuning Practical Workshop. The question was about Performance Comparison IN vs OR. Though personally, I have answered this question quite a many times before, let us answer it once again, it never hurts to repeat the truth multiple times.

Question: Which is query runs faster –

1. The query with IN operator
2. The query with OR operator

Well, the answer is – Both are Equal. Yes, that is the truth. Let us see a simple demonstration.

Fun following query on the sample database AdventureWorks –

SET STATISTICS IO ON
GO
SELECT TOP (1000) [CustomerID]
,[PersonID]
,[StoreID]
,[AccountNumber]
FROM [AdventureWorks2014].[Sales].[Customer]
WHERE StoreID IN (934,1028,642,932,1026)
GO
SELECT TOP (1000) [CustomerID]
,[PersonID]
,[StoreID]
,[AccountNumber]
FROM [AdventureWorks2014].[Sales].[Customer]
WHERE StoreID = 934 OR StoreID = 1028
OR StoreID = 642 OR StoreID = 932 OR StoreID = 1026
GO

Now let us go and check messages:

(10 rows affected)
Table ‘Customer’. Scan count 1, logical reads 123, physical reads 0,…

(10 rows affected)
Table ‘Customer’. Scan count 1, logical reads 123, physical reads 0,…

You will notice in either of the cases, SQL Server takes the same amount of logical reads. Now let us see the execution plan as well.

As you can see from the execution plan, they are absolutely identical and they both have exactly the same missing index warning as well.

However, if you carefully look at the Filter Operator in the execution plan of the query where I have used IN condition, you can see that SQL Server execution plan has converted that all the values from the IN operator to OR automatically. Yes, that is correct, SQL Server optimizer engine internally automatically maps all the values specified in the IN operator to OR operator. As SQL Server converts IN to OR automatically that is the primary reasons for both them having identical performance.

Please leave a comment and let me know if you would love to see any similar performance comparison.

Reference: Pinal Dave (https://blog.sqlauthority.com)