SQL Logic Operator Precedence: And and Or

Question

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?

Wernher

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. :) – Barbiebarbieri

Answer 1

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

• Microsoft Transact-SQL operator precedence
• Oracle MySQL 9 operator precedence
• Oracle 10g condition precedence
• PostgreSQL operator Precedence
• SQL as understood by SQLite

Haematoma

It is good practice to use parentheses even if they are not needed. very few programers (if any) know precedence of all operators available. – Unreserved

@Unreserved Wish it weren't so... it shouldn't be so, but I'm guessing you are right. – Haematoma

This AND then OR precedence is part of the SQL standard? – Muticous

@Jaime, Yes, and, afaik, it is also part of the standard for all programming languages. – Haematoma

i tried this in mysql: X or Y AND Z and it worked as (X or Y) AND Z – Offering

@Bsienn, Not sure what you did, but that is inconsistent with standard SQL and with MySQL documentation... dev.mysql.com/doc/refman/5.0/en/operator-precedence.html You should try again, - carefully this time...try 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 select case when (@x=1 or @y=1) and @z=1 then'T' else 'F' end – Haematoma

i just tried ur suggestion, and u r right. maybe my query or page got cashed thats why i got weird result. i'm beginner though i new it should be as u said, but i was baffeled why it worked in my case before. – Offering

Did I miss this day in computer class? I just always assumed they were equal precedence, so I've just always been using parens to be explicit. Now I'm going back to see if I've ever just assumed left to right with and's and or's. – Lyle

Answer 2

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.

Vespine

gbn Is there associativity in ORACLE SQL? IF YES then how and where I can get all operators associativity? – Living

As much as it pains me to say it, AND does not have precedence over OR in ruby! To make things worse, && does have precedence over ||! One of the reasons I don't like ruby--it violates the principle of least astonishment over and over for me. 2.2.1 :007 > true or true and false => false 2.2.1 :008 > true || true && false => true – Ingratitude

Answer 3

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.

Telophase

Answer 4

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

Jamijamie

Answer 5

Here's a variant of the '3-variable truth table' using booleans

WITH truth_values AS
  (SELECT FALSE AS A,        
          FALSE AS B,
          FALSE AS C
   UNION ALL SELECT FALSE,
                    FALSE,
                    TRUE
   UNION ALL SELECT FALSE,
                    TRUE,
                    FALSE
   UNION ALL SELECT FALSE,
                    TRUE,
                    TRUE
   UNION ALL SELECT TRUE,
                    FALSE,
                    FALSE
   UNION ALL SELECT TRUE,
                    FALSE,
                    TRUE
   UNION ALL SELECT TRUE,
                    TRUE,
                    FALSE
   UNION ALL SELECT TRUE,
                    TRUE,
                    TRUE),
     logics AS
  (SELECT truth_values.*,
          a
   OR b
   AND c AS no_parens, (a
                        OR b)
   AND c AS or_parens
   FROM truth_values)
SELECT *,
       no_parens != or_parens AS parens_made_a_difference
FROM logics
ORDER BY a,
         b,
         c

With these results:

#	A	B	C	no_parens	or_parens	parens_made_a_difference
1	false	false	false	false	false	false
2	false	false	true	false	false	false
3	false	true	false	false	false	false
4	false	true	true	true	true	false
5	true	false	false	true	false	true
6	true	false	true	true	true	false
7	true	true	false	true	false	true
8	true	true	true	true	true	false

If 'parens_made_a_difference' is true, then the parentheses made a difference.

Cretin