SQL Logic Operator Precedence: And and Or
Are the two statements below equivalent?
SELECT [...]
FROM [...]
WHERE some_col in (1,2,3,4,5) AND some_other_expr
and
SELECT [...]
FROM [...]
WHERE some_col in (1,2,3) or some_col in (4,5) AND some_other_expr
Is there some sort of truth table I could use to verify this?
Answer
And has precedence over Or, so, even if a <=> a1 Or a2
Where a And b
is not the same as
Where a1 Or a2 And b,
because that would be Executed as
Where a1 Or (a2 And b)
and what you want, to make them the same, is the following (using parentheses to override rules of precedence):
Where (a1 Or a2) And b
Here's an example to illustrate:
Declare @x tinyInt = 1
Declare @y tinyInt = 0
Declare @z tinyInt = 0
Select Case When @x=1 OR @y=1 And @z=1 Then 'T' Else 'F' End -- outputs T
Select Case When (@x=1 OR @y=1) And @z=1 Then 'T' Else 'F' End -- outputs F
For those who like to consult references (in alphabetic order):