SQL 逻辑运算符优先级：And 和 Or
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1241142/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Logic Operator Precedence: And and Or
提问by nc.
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?
是否有某种真值表可以用来验证这一点？
回答by Charles Bretana
Andhas precedence over Or, so, even if a <=> a1 Or a2
And优先于Or，所以，即使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):
对于那些喜欢查阅参考文献的人（按字母顺序）：
回答by gbn
I'll add 2 points:
我补充两点：
- "IN" is effectively serial ORs with parentheses around them
- AND has precedence over OR in every language I know
- “IN”实际上是带有括号的串行 OR
- 并且在我知道的每种语言中都优先于 OR
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.
因此，当您拆分 IN 子句时，您将串行 OR 拆分并更改了优先级。
回答by Yassine Abdul-Rahman
- 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
- 算术运算符
- 连接运算符
- 比较条件
- IS [NOT] NULL, LIKE, [NOT] IN
- [不] 之间
- 不等于
- 非逻辑条件
- AND 逻辑条件
- OR 逻辑条件
You can use parentheses to override rules of precedence.
您可以使用括号来覆盖优先规则。
回答by AjV Jsy
Query to show a 3-variable boolean expression truth table :
查询以显示 3 变量布尔表达式真值表：
;WITH cteData AS
(SELECT 0 AS A, 0 AS B, 0 AS C
UNION ALL SELECT 0,0,1
UNION ALL SELECT 0,1,0
UNION ALL SELECT 0,1,1
UNION ALL SELECT 1,0,0
UNION ALL SELECT 1,0,1
UNION ALL SELECT 1,1,0
UNION ALL SELECT 1,1,1
)
SELECT cteData.*,
CASE WHEN
(A=1) OR (B=1) AND (C=1)
THEN 'True' ELSE 'False' END AS Result
FROM cteData
Results for (A=1) OR (B=1) AND (C=1):
结果(A=1) OR (B=1) AND (C=1)：
A B C Result
0 0 0 False
0 0 1 False
0 1 0 False
0 1 1 True
1 0 0 True
1 0 1 True
1 1 0 True
1 1 1 True
Results for (A=1) OR ( (B=1) AND (C=1) )are the same.
结果(A=1) OR ( (B=1) AND (C=1) )相同。
Results for ( (A=1) OR (B=1) ) AND (C=1):
结果( (A=1) OR (B=1) ) AND (C=1)：
A B C Result
0 0 0 False
0 0 1 False
0 1 0 False
0 1 1 True
1 0 0 False
1 0 1 True
1 1 0 False
1 1 1 True
