# 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
Microsoft SQL Server 2008

Last Comment
AXISHK
chaau

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
AXISHK

So, either one of these 3 condition, correct ?

(conditionA and conditionB)
or conditionC
or (condition D and condition E)
Carl Tawn

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.
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.
SOLUTION
Scott Pletcher

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
chaau

@Scott: What do you say to this statement:
select 'c'
where
1/0 = 2 and 'a' = 'b'

----

(0 row(s) affected)
Scott Pletcher

@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?
chaau

@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.
In this case SQL Server chose to validate the second condition first.
chaau

OK, I must admit, it is a short-circuiting, but the order of the validation is unpredictable
Scott Pletcher

@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.
chaau

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
There is of course a workaround to use a CASE statement which I actually use from time to time
Scott Pletcher

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.
AXISHK

Tks
Microsoft SQL Server 2008

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.

50K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY