sql - SQL 中 bool 表达式的求值顺序

Question

我无法确定 SQL 中 bool 谓词的求值顺序。

考虑以下对我们(假想的)汽车数据库的选择谓词:

WHERE
make='Honda' AND model='Accord' OR make='VW' AND model='Golf';

我知道 AND 优先于 OR，但是我很困惑这个表达式是否会按如下方式求值:

((make='Honda' AND model='Accord') OR make='VW') AND model='Golf';

或作为:

(make='Honda' AND model='Accord') OR (make='VW' AND model='Golf');

或者完全不同的东西？!

非常感谢您的帮助。

Answer 1

这应该被评估为

WHERE
(make='Honda' AND model='Accord' ) OR (make='VW' AND model='Golf');

说明:在SQL服务器中AND has precedence over OR因此你可以想象 AND 部分在括号内并在它们之前和之后评估 OR

基于您的评论的详细信息

AND has percedence over OR, it something I already mentioned in my post. This precedence is Left tor Right, therefore it is still not clear which evaluation order takes place here: ((make='Honda' AND model='Accord') OR make='VW') AND model='Golf'; or (make='Honda' AND model='Accord' ) OR (make='VW' AND model='Golf'); –

L2R 解析

1. WHERE (make='Honda' AND model='Accord') OR make='VW' AND model='Golf';

因为首先所有的 AND 和最左边的

1. WHEREresult1OR (make='VW' AND model='Golf');

因为首先所有的 AND

1. WHEREresult1ORresult2;

最后或

R2L 解析

1. WHERE make='Honda' AND model='Accord' OR (make='VW' AND model='Golf');

因为首先是所有的 AND 和最右边的 AND 第一个

1. WHERE (make='Honda' AND model='Accord') 或result1;

因为首先所有的 AND 都超过 OR

1. WHEREresult2ORresult1;

最后或

所以在这两种情况下，条件的计算结果都是

WHERE
(make='Honda' AND model='Accord' ) OR (make='VW' AND model='Golf');

所以我评估了下面查询中的所有三个表达式

   -- create table t(make varchar(100), model varchar(100))
   -- insert into t values ('Honda','Golf'),('Honda','Accord'),('VW','Golf'),('VW','Accord')
    select *, 
    case when make='Honda' AND model='Accord' OR make='VW' AND model='Golf' then 1 else 0 end as result,
    case when (make='Honda' AND model='Accord') OR (make='VW' AND model='Golf') then 1 else 0 end as result1,
    case when ((make='Honda' AND model='Accord') OR make='VW' ) AND model='Golf' then 1 else 0 end as result2
    from t
    ;

并且结果一直显示result =result1，证明它被评估为

WHERE
(make='Honda' AND model='Accord' ) OR (make='VW' AND model='Golf');

See sqlfiddle demo