SQL Logic Operator Precedence: And and Or

204
votes

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?

4
sqllogical-operatorsoperator-precedence
Try: T T F. (T or T) and F. T or (T and F). The reader of code should be clearly able to see the intent of the writer of code. And the writer needs to be sure that the machine is doing what he intended. Parentheses align all three: reader, writer, and machine. :) - Assad Ebrahim

4 Answers

331
votes

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

39
votes

I'll add 2 points:

  • "IN" is effectively serial ORs with parentheses around them
  • AND has precedence over OR in every language I know

So, the 2 expressions are simply not equal.

WHERE some_col in (1,2,3,4,5) AND some_other_expr
--to the optimiser is this
WHERE
     (
     some_col = 1 OR
     some_col = 2 OR 
     some_col = 3 OR 
     some_col = 4 OR 
     some_col = 5
     )
     AND
     some_other_expr

So, when you break the IN clause up, you split the serial ORs up, and changed precedence.

24
votes
  1. Arithmetic operators
  2. Concatenation operator
  3. Comparison conditions
  4. IS [NOT] NULL, LIKE, [NOT] IN
  5. [NOT] BETWEEN
  6. Not equal to
  7. NOT logical condition
  8. AND logical condition
  9. OR logical condition

You can use parentheses to override rules of precedence.

10
votes

Query to show a 3-variable boolean expression truth table :

;WITH cteData AS
(SELECT 0 AS A, 0 AS B, 0 AS C
UNION ALL SELECT 0,0,1
UNION ALL SELECT 0,1,0
UNION ALL SELECT 0,1,1
UNION ALL SELECT 1,0,0
UNION ALL SELECT 1,0,1
UNION ALL SELECT 1,1,0
UNION ALL SELECT 1,1,1
)
SELECT cteData.*,
    CASE WHEN

(A=1) OR (B=1) AND (C=1)

    THEN 'True' ELSE 'False' END AS Result
FROM cteData

Results for (A=1) OR (B=1) AND (C=1) :

A   B   C   Result
0   0   0   False
0   0   1   False
0   1   0   False
0   1   1   True
1   0   0   True
1   0   1   True
1   1   0   True
1   1   1   True

Results for (A=1) OR ( (B=1) AND (C=1) ) are the same.

Results for ( (A=1) OR (B=1) ) AND (C=1) :

A   B   C   Result
0   0   0   False
0   0   1   False
0   1   0   False
0   1   1   True
1   0   0   False
1   0   1   True
1   1   0   False
1   1   1   True