是否评估了 SQL WHERE 子句短路? [英] Is the SQL WHERE clause short-circuit evaluated?

问题描述

是 SQL WHERE 子句中的布尔表达式 短路评估?

例如:

SELECT *从表 tWHERE @key 为 NULL 或(@key 不为 NULL AND @key = t.Key)

如果 @key IS NULL 的计算结果为 true，@key IS NOT NULL AND @key = t.Key 是否计算?>

如果不是，为什么不呢?

如果是，是否有保证?它是 ANSI SQL 的一部分还是特定于数据库?

如果特定于数据库，SQLServer?甲骨文?MySQL?

解决方案

ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf

<块引用>

6.3.3.3 规则评估顺序

[...]

如果优先级不是由格式或由括号，表达式的有效评估通常是从左到右执行.然而，它是依赖于实现 表达式是否实际上是从左到右计算的，特别是当操作数或运算符可能导致条件被引发 或者如果表达式的结果可以在不完全评估所有部分的情况下确定表达.

Are boolean expressions in SQL WHERE clauses short-circuit evaluated ?

For example:

SELECT * 
FROM Table t 
WHERE @key IS NULL OR (@key IS NOT NULL AND @key = t.Key) 

If @key IS NULL evaluates to true, is @key IS NOT NULL AND @key = t.Key evaluated?

If no, why not?

If yes, is it guaranteed? Is it part of ANSI SQL or is it database specific?

If database specific, SQLServer? Oracle? MySQL?

解决方案

ANSI SQL Draft 2003 5WD-01-Framework-2003-09.pdf

6.3.3.3 Rule evaluation order

[...]

Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether expressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression.

这篇关于是否评估了 SQL WHERE 子句短路?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！