Question on TSQL

for the where condition in TSQL;

where
   conditionA and conditionB or conditionC or condition D and condition E


In this case, how does the TSQL process this condition ?

Tks
AXISHKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
According to the operator precedence rule.
The resulting clause is an equivalent to:
where
   (conditionA and conditionB) or conditionC or (condition D and condition E)

Open in new window

Please note that SQL Server does not have a short-circuiting, meaning that in the clause like this:
where 
    'a' = 'b' and 1/0 = 2

Open in new window

there will be an exception due to division by zero
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AXISHKAuthor Commented:
So, either one of these 3 condition, correct ?

     (conditionA and conditionB)
or conditionC
or (condition D and condition E)
0
Carl TawnSystems and Integration DeveloperCommented:
Just as an extra note;  in situations like yours it would be common practice to bracket the clauses in order to make it clear to anybody else looking at the code what your intention was.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I would always go for a solution with brackets. It's better for read and also if in the future you want to add or change something in the logic it's more easy to move a block inside the brackets than loosing time to remember again all the logic.
0
Scott PletcherSenior DBACommented:
>> Please note that SQL Server does not have a short-circuiting, meaning that in the clause like this: <<

SQL Server does indeed short-circuit when it "believes" it can.  Not sure where you got your claim from.

Indeed, if we run your exact statement:
select 'c'
where
    'a' = 'b' and 1/0 = 2
we get an empty result set, not a "divide by zero" error.
0
chaauCommented:
@Scott: What do you say to this statement:
select 'c'
where
    1/0 = 2 and 'a' = 'b'

----

(0 row(s) affected)

Open in new window

0
Scott PletcherSenior DBACommented:
@chaau:

I say it's absolute proof that SQL does indeed short-circuit when possible, as I stated above.

Again, where did you get the info that SQL does not s-c?
0
chaauCommented:
@Scott: The 1/0 = 2 and 'a' = 'b' proves that it is not a short circuit but what they claim is a cost-based validation. SQL Server chooses what is in its opinion is "less cost" but not left to right validation as in C++ for example. You can't really say what SQL Server chooses as the first statement to validate.
Take for example this statement:
select 1 where 
CEILING(1.22) = 2 and 1/0 = 2

-----------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Open in new window

In this case SQL Server chose to validate the second condition first.
0
chaauCommented:
OK, I must admit, it is a short-circuiting, but the order of the validation is unpredictable
0
Scott PletcherSenior DBACommented:
@chaau:
Yes, SQL may change the order of evaluation of expressions.  But it can short-circuit as well, and does so.  You don't really seem to understand what a short-circuit is if you can try to claim that ignoring the condition after an "and" is not a short-circuit.
0
chaauCommented:
As I admitted before, it does short-circuit, however for a person that comes from a C/C++/Java development world it can be a bit confusing. Because of the unpredictable nature of its validation you can't really use a left-to-right logic, like
select textvalue from aTable WHERE ISNUMERIC(textvalue) = 1 and convert(int, textvalue) = 1

Open in new window

There is of course a workaround to use a CASE statement which I actually use from time to time
0
Scott PletcherSenior DBACommented:
I'm stunned that C++ and/or Java don't optimize the order of comparisons as well.  If there are multiple conditions, it makes great sense to do that cost the least to do first.

In particular, if you have a condition like this:
(where exists(...) and exists(....) and exists(...)) or col1 = 5
It will be vastly less overhead to check the single column comparison first before having to do all perhaps three exists checks.  If the second condition is true, the first set of conditions can be short-circuited.
0
AXISHKAuthor Commented:
Tks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.