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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s