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?
This question is related to
sql
logical-operators
operator-precedence
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
You can use parentheses to override rules of precedence.
I'll add 2 points:
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.
Source: Stackoverflow.com