I recently learned that when combining multiple operators in a SQL expression,
AND has a higher precedence than
& 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)
- ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
- = (Assignment)
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.