以下两个语句等价吗?
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
有没有一种真值表可以用来验证这个?
以下两个语句等价吗?
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
对于喜欢查阅参考资料的人(按字母顺序排列):
AND
然后 OR
的优先级是 SQL 标准的一部分吗? - Jaime HablutzelX或Y且Z
,它的运行结果是(X或Y)且Z
。 - Abdul Rehmandeclare @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
select case when (@x=1 or @y=1) and @z=1 then'T' else 'F' end
- Charles Bretana我再补充两点:
因此这两个表达式根本不相等。
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
因此,当您拆分IN子句时,会拆分串行OR,并更改优先级。
2.2.1 :007 > true or true and false
=> false
2.2.1 :008 > true || true && false
=> true
- Alex L您可以使用括号来覆盖优先规则。
显示一个三变量布尔表达式的真值表的查询:
;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
(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
(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
WITH truth_values AS
(SELECT FALSE AS A,
FALSE AS B,
FALSE AS C
UNION ALL SELECT FALSE,
FALSE,
TRUE
UNION ALL SELECT FALSE,
TRUE,
FALSE
UNION ALL SELECT FALSE,
TRUE,
TRUE
UNION ALL SELECT TRUE,
FALSE,
FALSE
UNION ALL SELECT TRUE,
FALSE,
TRUE
UNION ALL SELECT TRUE,
TRUE,
FALSE
UNION ALL SELECT TRUE,
TRUE,
TRUE),
logics AS
(SELECT truth_values.*,
a
OR b
AND c AS no_parens, (a
OR b)
AND c AS or_parens
FROM truth_values)
SELECT *,
no_parens != or_parens AS parens_made_a_difference
FROM logics
ORDER BY a,
b,
c
以下是结果:
# | A | B | C | no_parens | or_parens | parens_made_a_difference |
---|---|---|---|---|---|---|
1 | 假 | 假 | 假 | 假 | 假 | 假 |
2 | 假 | 假 | 真 | 假 | 假 | 假 |
3 | 假 | 真 | 假 | 假 | 假 | 假 |
4 | 假 | 真 | 真 | 真 | 真 | 假 |
5 | 真 | 假 | 假 | 真 | 假 | 真 |
6 | 真 | 假 | 真 | 真 | 真 | 假 |
7 | 真 | 真 | 假 | 真 | 假 | 真 |
8 | 真 | 真 | 真 | 真 | 真 | 假 |
如果 'parens_made_a_difference' 为 true,则说明括号有影响。