Using Or with And

1

Hi all,

I am just trying to make sense of the order of precedence in a query I have inherited and I am still checking my results.. As I understand it, the AND operator is evaluated before OR unless brackets are used to change the precedence.
Here is the code
SELECT

DISTINCT c.customerid as Client,

c.name,

c.Postcode,

c.Pref_Corr_Type,

c.Status,

c.type,

FROM Customer c

INNER JOIN ReportingCampaign rc

ON c.customerid = rc.customerid

INNER JOIN Orders o

ON c.customerid = o.customerid

INNER JOIN deal d

ON d.customerid = o.customerid

AND d.product_No = o.product_No

AND d.product_code = o.product_code

INNER JOIN Product p

ON p.customerid = o.customerid

AND p.product_No = o.product_no

WHERE rc.CampaignID ='30'

and rc.emailaddress ='y'

and rc.priority = '1'

and (c.modified_date<>'1900-01-01 00:00:00.000' or c.modified_date<>'')

and p.product_code not in ('AA','AB','AC','AD')

and o.order_quantity>='1'

OR (p.product_Code not in ('AA','AB','AC','AD') and o.order_quantity< '1' and d.deal_date between '2019-01-01 00:0:0' and '2019-12-01 00:0:0')

Am I correct in saying that the last part of the WHERE filter OR (p.product_Code not in ('AA','AB','AC','AD') and o.order_quantity< '1' and d.deal_date between '2019-01-01 00:0:0' and '2019-12-01 00:0:0') will be evaluated first? And the preceding AND's will be evaluated in the order they are written?

I don't normally use this many filters so I just want to feel confident in how the query is working. Any input would be appreciated.

Thanks

Vinnie

2

It's impossible to say for sure what SQL will evaluate "first", no matter what the written order is or even what parentheses are used. However, SQL does insure that logically the conditions evaluate as specified, but the specific evaluation order is as SQL sees fit to do it.

What's known is that all the separate ANDed conditions must be true ~or~ the condition(s) in the OR must be true for a row to be selected.

That is, if I write:
WHERE A = 1 AND B = 2 OR C = 3
Then both A must be 1 and B must be 2, in which case C doesn't matter ~or~ C must be 3, in which case A and B values don't matter.

2 Likes