下面的两个语句是否等效?

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

我可以使用某种真值表来验证这一点吗?

最佳答案

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
  • 10-08 03:43