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

User · Answer

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

User · Answer

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

User · Answer

And has precedence over Or  so  even if a  lt   gt  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

User · Answer

Arithmetic operators Concatenation operator  Comparison conditions  IS  NOT  NULL  LIKE   NOT  IN   NOT  BETWEEN  Not equal to  NOT logical condition  AND logical condition  OR logical condition   You can use parentheses to override rules of precedence

[sql] SQL Logic Operator Precedence: And and Or

Examples related to sql

Examples related to logical-operators

Examples related to operator-precedence