MS SQL Server OR Statement
In the code below... I found this in an old procedure.
Instead of all the or statements
Isn't an "IN" statement better?
Instead of all the or statements
Isn't an "IN" statement better?
WHERE p.SalesSiteID IN ( 104038, 104018 )
AND p.Hold = 0
AND p.Refunded = 0
AND p.TransactionStatus = 'succeeded'
AND ISNULL(p.ApiResponseID, '') <> ''
AND va.Code = 'TAB'
AND YEAR(p.DateAdded) > 2017
AND p.IndividualID > 3790292
AND p.TABUpdated = 1
AND ISNULL(p.APIStatusID, 0) = 1
AND ( p.Merchant = 'TAB External Vendor'
OR p.Merchant = 'TSX'
OR p.Merchant = 'TSX-5664'
OR p.Merchant = 'TSX-5613'
OR p.Merchant = 'TSX-5665'
OR p.Merchant = 'TSX-4618'
OR p.Merchant = 'GlobalOne'
OR p.Merchant = 'EWFlex'
OR p.Merchant = 'EWFlex2')
ORDER BY i.IndividualID DESC;
I think you would be best to look at the Execution Plan for each and compare, but yes IN is typically better.
The optimizer will likely rewrite an IN list as an or. an IN is easier to read.
You can see that here:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5179473dc8138397c5b766cb9a5c16c6
Generate your own execution plan and see it on your data.
You can see that here:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5179473dc8138397c5b766cb9a5c16c6
Generate your own execution plan and see it on your data.
OR is a logical operator and IN is a predicate. Both are not a statement. The entire SELECT is a statement.
As already written IN is equivalent to an OR. In cases of large lists and an appropriate value distribution an EXISTS predicate with an properly indexed filter table can be faster.
As already written IN is equivalent to an OR. In cases of large lists and an appropriate value distribution an EXISTS predicate with an properly indexed filter table can be faster.
If you put both syntaxes into the same window, and hit Ctrl-L, you can see how SQL with estimate the 2 query plans, and how high a percentage of the total each query has. If they both come out at 50%, SQL estimates them to have the same cost.
>Isn't an "IN" statement better?
Regardless of the optimization (as the above experts have nailed that pretty well), there are hard-coded values, and when one of them ever changes, or a new value needs to be added, now your query is going to report incorrect results.
Far better to have these values in a table where a UI can be built to babysit this list of values, and then in T-SQL use a JOIN (IN, whatever) on that table to handle the filtering.
Regardless of the optimization (as the above experts have nailed that pretty well), there are hard-coded values, and when one of them ever changes, or a new value needs to be added, now your query is going to report incorrect results.
Far better to have these values in a table where a UI can be built to babysit this list of values, and then in T-SQL use a JOIN (IN, whatever) on that table to handle the filtering.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
probably they will be same...
I prefer IN... OR looks ugly, lots of repetition...
do you have index on that column?
and how many records on avg do you have in this table?
I prefer IN... OR looks ugly, lots of repetition...
do you have index on that column?
and how many records on avg do you have in this table?
Hi,
In most modern databases an IN statement produces only one index scan/traversal while an OR statement produces several index scans/traversals. Thus IN statement is a better choice. And any usage of functions in where clauses forces the optimizer to choose and do tablescan instead of index scans. A function-based index may help in such cases but a rewrite of the query should be a preferred choice.
Regards,
Tomas Helgi
In most modern databases an IN statement produces only one index scan/traversal while an OR statement produces several index scans/traversals. Thus IN statement is a better choice. And any usage of functions in where clauses forces the optimizer to choose and do tablescan instead of index scans. A function-based index may help in such cases but a rewrite of the query should be a preferred choice.
Regards,
Tomas Helgi
>> In most modern databases an IN statement produces only one index scan/traversal while an OR statement produces several index scans/traversals. <<
Can you provide further info on this claim. I don't think I've ever seen this in SQL Server, e.g., in a query plan. But I'm no longer familiar with Oracle, Sybase, etc., on how they might handle this.
Can you provide further info on this claim. I don't think I've ever seen this in SQL Server, e.g., in a query plan. But I'm no longer familiar with Oracle, Sybase, etc., on how they might handle this.
I checked oracle and sql
no difference, same execution plans
table has index on that column...
no difference, same execution plans
table has index on that column...
I would think that by the time the query got to that stage of optimization, the optimization engine wouldn't even know / be able to "see" that the literal values to be compared came from an "IN" rather than a bunch or "OR"s.