Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I had logic in a CS curriculum many years ago and I’ve worked with AND and OR statements for years. I’ve sometimes confused myself, but I usually ensure I have parenthesis included to clarify the code. Not just for me, but for anyone that might glance at the code later.
As a side note, I also try to format code so a quick glance can reveal what happens.
However, I learned something new this week. I saw a question about the order of logical operations in this form: a or b and c.
I had somewhat assumed, like math, we’d use a left to right evaluation. However, that’s not correct. Look at this snippet:
If we went left to write, we’d have two rows from the OR (n=1, n=2) and then an AND that produces no rows. So no results?
That’s not correct. According to BOL for OR, the AND operations occur first. So n=2 AND n > 3 occurs, with 0 rows. Then the OR with n=1 is evaluated to return 1 row.
Fascinating.
At least to me. I’ve never thought because I’d write
WHERE (n = 1 OR n = 2) AND n > 3
or
WHERE n = 1 OR (n = 2 AND n > 3)
and be sure that what I wanted to occur would occur.
A quick lesson. While it’s good to know what the order or evaluation is for your platform, don’t count on this. If there is a chance for confusion or unintended consequences, use parenthesis. It’s simpler and easier, and I might argue, more elegant.
Voice of the DBA 
Very important point, Steve. This type of logic issue can really mess up a query. “Protecting” your OR or your AND with parentheses is often not just a good idea, it’s required.
LikeLike
For sure. It prevents mistakes, especially if someone ports code to a new platform that might treat AND/OR differently.
LikeLike
From the Proofreading Police: The phrase should be “left to right”, not “left to write”. Probably just a mental glitch while typing.
LikeLike
But doesn’t maths use BODMAS so if you had a + b * c the actual result would be a + (b * c) and not necessarily left to right.
LikeLike
I agree with you. Math is not always left to right. It is left to right when the precedence is the same but usually the precedence is the same only when the evaluation order doesn’t matter.
LikeLike
yes, BODMAS applies, though left to right is how we really look at things in the English world. Even within BODMAS, we’d look at parens left to write, exponents, etc.
LikeLike