SQL 逻辑运算符优先级:And 和 Or [英] 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

和

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 优先于 Or，因此，即使 a <=>a1 或 a2

And has precedence over Or, so, even if a <=> a1 Or a2

Where a And b 

与

Where a1 Or a2 And b,

因为那将作为

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 运算符优先级
• Oracle MySQL 9 运算符优先级
• Oracle 10g 条件优先莉>
• PostgreSQL 运算符优先级
• SQLite 理解的 SQL

这篇关于SQL 逻辑运算符优先级:And 和 Or的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！