SQL Logic Operator Precedence: And and Or
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
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.
- 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.