In the WHERE section or anywhere really where you have multiple conditions like OR’s I know outer parenthesis are required but are the inner ones?
For example my assumption is that
WHERE A.Title='EMP' AND ( (A.NAME='Mike') OR (A.ID='9001') )
Is the same as writing
WHERE A.Title='EMP' AND ( A.NAME='Mike' OR A.ID='9001' )
However if we remove the outer parenthesis then I know the query will be different.
Example:
WHERE A.Title='EMP' AND (A.NAME='Mike') OR (A.ID='9001')
And
WHERE A.Title='EMP' AND A.NAME='Mike' OR A.ID='9001'
Are both the same thing but not at all what we want.
Is there any chance that data will be evaluated different between the first 2 conditions?
Advertisement
Answer
It’s like in math
2 + 3 * 4 ==> 14
is the same as
2 + (3 * 4) ==> 14
because the multiplication has a higher precedence than the addition. If you want to do the addition before the multiplication, you must add parenthesis.
(2 + 3) * 4 ==> 20
In SQL AND has a higher precedence than OR. = as comparison operator has a higher precedence than both of them. Therefore the inner parenthesis are not required, because the = will always be performed first. E.g.
A.Title='EMP' AND A.NAME='Mike' OR A.ID='9001'
is the same as
((A.Title='EMP') AND (A.NAME='Mike')) OR (A.ID='9001')
Parenthesis are only required if you want to perform the OR before the AND (even when it appears first in the expression):
A.Title='EMP' AND (A.NAME='Mike' OR A.ID='9001')
Only when operators have the same precedence, they are evaluated left to right.
For the full precedence list, see: Operator Precedence (Transact-SQL)