I recently learned that when combining multiple operators in a SQL expression, AND has a higher precedence than OR but & has the same precedence as |. I expected the precedence rules for the logical operators to be consistent with the bitwise operators.
Even Stephen Sondheim seemed to struggle with this.
AND is Always Evaluated Before OR
SELECT 'TRUE' WHERE (1 = 1) OR (1 = 1) AND (1 = 0) -- returns TRUE SELECT 'TRUE' WHERE (1 = 0) AND (1 = 1) OR (1 = 1) -- returns TRUE |
& and | are Evaluated Left To Right
SELECT 1 | 1 & 0 -- returns 0 SELECT 0 & 1 | 1 -- returns 1 |
Here Are The Official Docs
Here what Microsoft says about SQL Server’s Operator Precedence.
- ~ (Bitwise NOT)
- * (Multiply), / (Division), % (Modulo)
- + (Positive), – (Negative), + (Add), (+ Concatenate), – (Subtract), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR)
- =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
- NOT
- AND
- ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
- = (Assignment)
Practical Subset
I have a book on my shelf called Practical C Programming published by O’Reilly (the cow book) by Steve Oualline. I still love it today because although I don’t code in C any longer, the book remains a great example of good technical writing.
That book has some relevance to SQL today. Instead of memorizing the full list of operators and their precedence, Steve gives a practical subset:
- * (Multiply), / (Division)
- + (Add), – (Subtract)
Put parentheses around everything else.
You are now officially my hero, bringing Sondheim into SQL.
Comment by Ewald Cress — July 20, 2016 @ 2:28 pm
Thanks Ewald! He’s awesome.
Comment by Michael J. Swart — July 20, 2016 @ 3:27 pm
What the world needs is a query optimiser/statistics essay based on “On the steps of the palace”. Oh dear, I’m about to go into a campy phase again…
Comment by Ewald Cress — July 20, 2016 @ 3:46 pm
[…] Michael J. Swart looks at how SQL Server implements order of operations: […]
Pingback by Simplified Order Of Operations – Curated SQL — July 22, 2016 @ 9:01 am
This sort of thing is one of many reasons that we deprecated the bit and bit varying datatypes in SQL: 2003. SQL is meant to be a database language, with set orientation, predicates, and as much of an abstract model as possible. We do not have to worry about any of those stinking high end versus low end, hardware stuff, so we can ported onto existing hardware with minimum effort. Is anybody else your old enough to remember when Fortran was defined at the hardware level?
Comment by Joe Celko — July 23, 2016 @ 3:43 pm
The last sentence is the most / only important: “Put parentheses around everything (else).”
And of course it helps to understand the code a lot if you use correct indentations so that everything that belongs logical together is intended on / to the same level.
Comment by Thomas Franz — September 15, 2020 @ 3:54 am