Some questions never get old, and one of those questions is the performance comparison between the IN and OR operators in SQL Server. This question has been asked numerous times, and it’s worth revisiting to reinforce the truth. So, let’s dive into it once again.

The question is: Which query runs faster – the query with the IN operator or the query with the OR operator? The answer is simple – both are equal in terms of performance.

Let’s demonstrate this with a simple example using the AdventureWorks sample database:

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

After executing the queries, we can check the 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,...

As you can see, in both cases, SQL Server performs the same number of logical reads. This indicates that the performance is identical for both queries.

Now, let’s take a look at the execution plan. The execution plans for both queries are absolutely identical, and they both have the same missing index warning as well.

However, if you carefully examine the Filter Operator in the execution plan of the query where the IN condition is used, you’ll notice that SQL Server’s optimizer engine internally converts all the values from the IN operator to the OR operator. This automatic conversion by the optimizer is the primary reason for both queries having identical performance.

Feel free to leave a comment and let me know if you would like to see any similar performance comparisons in the future.