I learned about the order of logical operations #SQLNewBlogger

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:

2018-03-22 10_06_22-SQLQuery1.sql - (local)_SQL2014.SimpleTalk_1_Development (PLATO_Steve (57))_ - M

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

6 Responses to I learned about the order of logical operations #SQLNewBlogger

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

  2. Larry says:

    From the Proofreading Police: The phrase should be “left to right”, not “left to write”. Probably just a mental glitch while typing.

  3. Bobby says:

    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.

    • Larry says:

      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.

    • way0utwest says:

      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.

Comments are closed.