sknox (12/30/2010)
I know how to write an XOR using AND/OR/NOT.
Yes, but Gianluca wasn't to know that. You might have been asking a sensible question, rather than just making the point that in the sentence "Any boolean expression is capable of being short-circuited, in the right circumstances." it would have been more accurate to say 'many' rather than 'any'...
But while you can write it, you can't short-circuit it:
(first_name IS NULL AND middle_name IS NOT NULL) OR (first_name IS NOT NULL AND middle_name IS NULL)In that code, both first_name and middle_name have to be evaluated. First we must evaluate first_name. If it's not NULL, then we can, yes, ignore middle_name here and short-circuit the first AND. But then we return false to the first part of the OR so we must evaluate the second part. Since first_name is not NULL, we know we must evaluate the second part of second AND, which evaluates middle_name. So you have to evaluate both sides of the XOR.
You can reorder the AND and OR operators, but since the two sides are mutually exclusive, you will always have to evaluate both of the original expressions. So not all boolean expressions can be short-circuited.
So, you're saying that an XOR written in T-SQL can be short-circuited? Or just a bit? 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi