SQL逻辑运算符优先级: And and Or

Question

下面的两个陈述是否等效？

SELECT [...]
FROM [...]
WHERE some_col in (1,2,3,4,5) AND some_other_expr

和

SELECT [...]
FROM [...]
WHERE some_col in (1,2,3) or some_col in (4,5) AND some_other_expr

是否有某种真值表可以用来验证这一点？

Answer 1

And优先于Or ，所以，即使 a <=> a1 Or a2

Where a And b 

不等于

Where a1 Or a2 And b,

因为这将被执行为

Where a1 Or (a2 And b)

为了使它们相同，您需要执行以下操作(使用括号来覆盖优先级规则):

 Where (a1 Or a2) And b

这里有一个例子来说明:

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

对于那些喜欢查阅引用资料的人(按字母顺序排列):

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