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 –
- The query with IN operator
- 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)
14 Comments. Leave new
Would like to see how this goes with NOT IN and NOT EXISTS
Also when you join two tables using IN operator, always use table alias for columns
https://exploresql.com/2018/11/21/sql-server-always-use-table-alias-when-using-in-operator/
So, I wonder what’s the alternate solution which is more performance-friendly?
What about IN using sub query and JOIN, which is better?
SET STATISTICS IO ON
GO
–Query using IN
select top 100 * from [Sales].[SalesOrderHeader] soh where SalesOrderID in (select SalesOrderID from [Sales].[SalesOrderDetail] sod where ProductID=710)
GO
–Query using JOIN
select top 100 soh.* from [Sales].[SalesOrderHeader] soh inner join [Sales].[SalesOrderDetail] sod on soh.SalesOrderID=sod.SalesOrderID
where sod.ProductID=710
GO
–I see both the queries are taking same number of logical reads.
–Query using IN
(44 row(s) affected)
Table ‘SalesOrderHeader’. Scan count 0, logical reads 143, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderDetail’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
–Query using JOIN
(44 row(s) affected)
Table ‘SalesOrderHeader’. Scan count 0, logical reads 143, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderDetail’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
In your case both are the same!
Thanks for the quick reply. In general is there any performance difference between IN using sub query Vs JOIN?
Not much.
Hi Pinal ,
I am big fan of your blog and I learn very much from your articles.
I have one question , How to handle options arguments in store procedure query. For example I have query to get all sales with optional argument like Item , Brand ,Product, Department , Sales Date etc.. This fields are not mandatory. Currently I am using query like this (@BrandID = 0 or SalesItem.BrandID = @BrandID) which create performance issue .. Please give me right way to pass options arguments veritable without create dynamic sql query.. Thank you in advance
Hi chirag solanki,
Pass NULL to the input parameter and change the where condition as below:
WHERE SalesItem.BrandID = ISNULL(@BrandID,SalesItem.BrandID)
–Mayura
No improvement in query
declare @Barcode varchar(120) = null
set @Barcode = ‘CCC011912061351’
select *from trnStock
where Barcode = @Barcode
(1 row affected)
Table ‘trnStock’. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
select *from trnStock
where Barcode = isnull(@Barcode,Barcode)
(1 row affected)
Table ‘trnStock’. Scan count 1, logical reads 17143, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 102 ms.
Try this method
declare @Barcode varchar(120) = null
set @Barcode = ‘CCC011912061351’
If @Barcode is not null
select *from trnStock
where Barcode = @Barcode
else
select *from trnStock;
From readability and maintainability point of view, IN instead of OR could be preferred.
At some moment while executing, SQL Server has to translate an IN query to the OR SQL equivalent.
So you *would* expect *some* performance penalty translating your IN to an OR, especially if your IN list is long. You won’t see it in the logical reads or execution plan, but I still would expect some small higher average execution *time*, on the simple fact that IN needs a translation, while the OR does not.
I have no clue how to measure this… just run it thousands of times, only measuring used time?
And when sending this SQL from some code … my SQL using the OR variant could be a *longer* message to transfer than the SQL variant using IN …. so from a code perspective it could still be a small difference.
Or am I seeing ghosts … ?
In my case, found that splitting and using “union all” was faster then IN/OR