Search code examples
sqllogical-operatorsoperator-precedence

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?

Solution

  • 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):