SQL Logic Operator Precedence: And and Or

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?

---

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

 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.

链接地址: http://www.djcxy.com/p/58892.html

上一篇: 在Bash变量赋值中找不到命令错误

下一篇: SQL逻辑运算符优先级：和和或