We help IT Professionals succeed at work.
Troubleshooting Question

MS SQL Server OR Statement

46 Views
Last Modified: 2020-11-09
In the code below... I found this in an old procedure.

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;


Comment
Watch Question

Daniel PineaultPresident / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
I think you would be best to look at the Execution Plan for each and compare, but yes IN is typically better.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.
Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

Commented:
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.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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. 
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
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?
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
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
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
>> 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.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
I checked oracle and sql
no difference, same execution plans
table has index on that column...
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.