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