blocks|key|1359846|text|综上所述，短路并不是真正可用的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1359847|如果您需要它，我建议使用Case语句：|1359848|Where+Case+when+Expr1+then+Expr2+else+Expr3+end+=+desiredResult|code-block|syntax|javascript|1359849|始终计算Expr1，但每行只计算Expr2和Expr3中的一个。|offset|length|style|CODE|1359850|entityMap^0|0|0|0|4|5|G|5|M|5|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]|$K|W|L|X|M|N]|$K|Y|L|Z|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|P|$]]

From the above, short circuiting is not really available.

If you need it, I suggest a Case statement:

<pre><code>Where Case when Expr1 then Expr2 else Expr3 end = desiredResult
</code></pre>

<code>Expr1</code>is always evaluated, but only one of <code>Expr2</code> and <code>Expr3</code> will be evaluated per row.

blocks|key|1574308|text|我不相信SQL+Server+(2005)中的短路是有保证的。SQL+Server通过其优化算法运行您的查询，该算法考虑了许多因素(索引、统计信息、表大小、资源等)，以得出有效的执行计划。在评估之后，您不能肯定地说您的短路逻辑是有保证的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1574309|不久前，我自己也遇到了同样的问题，我的研究真的没有给我一个明确的答案。您可能会编写一个小查询来证明它是有效的，但您是否可以确保，随着数据库负载的增加，表变得更大，数据库中的内容得到优化和更改，这一结论将成立。我不能，因此我犯了错误，在WHERE子句中使用了CASE来确保短路。|1574310|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I don't believe that short circuiting in SQL Server (2005) is guaranteed. SQL Server runs your query through its optimization algorithm that takes into account a lot of things (indexes, statistics, table size, resources, etc) to come up with an effective execution plan. After this evaluation, you can't say for sure that your short circuit logic is guaranteed. 

I ran into the same question myself sometime ago and my research really did not give me a definitive answer. You may write a small query to give you a sense of proof that it works but can you be sure that as the load on your database increases, the tables grow to be bigger, and things get optimized and changed in the database, that conclusion will hold. I could not and therefore erred on the side of caution and used CASE in WHERE clause to ensure short circuit.

blocks|key|1359349|text|你必须牢记数据库是如何工作的。在给定参数化查询的情况下，db基于该查询构建执行计划，而不使用参数值。无论实际提供的值是什么，每次运行查询时都会使用此查询。具有特定值的查询是否短路对执行计划无关紧要。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1359350|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You have to keep in mind how databases work. Given a parameterized query the db builds an execution plan based on that query without the values for the parameters. This query is used every time the query is run regardless of what the actual supplied values are. Whether the query short-circuits with certain values will not matter to the execution plan.

blocks|key|1359414|text|我通常将其用于可选参数。这和短路是一样的吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1359415|SELECT++[blah]
FROM++++Emp
WHERE++((@EmpID+=+-1)+OR+(@EmpID+=+EmpID))|code-block|syntax|javascript|1359416|这为我提供了传递-1或其他值的选项，以说明对属性的可选检查。有时，这涉及到连接多个表，或者最好是连接一个视图。|1359417|非常方便，不能完全确定它给数据库引擎带来的额外工作。|1359418|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I typically use this for optional parameters. Is this the same as short circuiting?

<pre><code>SELECT [blah]
FROM Emp
WHERE ((@EmpID = -1) OR (@EmpID = EmpID))
</code></pre>

This gives me the option to pass in -1 or whatever to account for optional checking of an attribute. Sometimes this involves joining on multiple tables, or preferably a view.

Very handy, not entirely sure of the extra work that it gives to the db engine.

blocks|key|1359756|text|只是偶然发现了这个问题，并且已经找到了这个博客条目：http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1359757|SQL服务器可以自由地在她认为合适的任何地方优化查询，所以在博客文章中给出的例子中，你不能依赖于短路。|1359758|然而，一个案例显然是以书面顺序进行评估的-检查该博客帖子的评论。|1359759|entityMap|0|LINK|mutability|MUTABLE|url|http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/^0|Q|22|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

Just stumbled over this question, and had already found this blog-entry: <a href="http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/" rel="nofollow">http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/</a>

The SQL server is free to optimize a query anywhere she sees fit, so in the example given in the blog post, you cannot rely on short-circuiting.

However, a CASE is apparently documented to evaluate in the written order - check the comments of that blog post.

blocks|key|1574198|text|对于SQL+Server，我认为这取决于版本，但我使用SQL+Server2000的经验是，即使@key为null，它仍然会计算@key+=+t.Key。换句话说，在计算WHERE子句时，它不会进行有效的短路。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1574199|我见过有人推荐像你的例子这样的结构，作为一种灵活的查询方式，用户可以输入或不输入各种条件。我的观察是，当@key为null时，Key仍然包含在查询计划中，并且如果Key被索引，那么它不能有效地使用索引。|1574200|这种具有不同条件的灵活查询可能是动态创建SQL的最佳选择。如果@key为null，那么您根本就不需要在查询中包含它。|1574201|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

For SQL Server, I think it depends on the version but my experience with SQL Server 2000 is that it still evaluates @key = t.Key even when @key is null. In other words, it does not do efficient short circuiting when evaluating the WHERE clause.

I've seen people recommending a structure like your example as a way of doing a flexible query where the user can enter or not enter various criteria. My observation is that Key is still involved in the query plan when @key is null and if Key is indexed then it does not use the index efficiently.

This sort of flexible query with varying criteria is probably one case where dynamically created SQL is really the best way to go. If @key is null then you simply don't include it in the query at all.

blocks|key|1359158|text|短路评估的主要特点是，一旦确定结果，它就会停止对表达式的评估。这意味着表达式的其余部分可以忽略，因为无论计算与否，结果都是相同的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1359159|二元布尔运算符是互换的，意思是：|1359160|a+AND+b+==+b+AND+a
a+OR++b+==+b+OR++a
a+XOR+b+==+b+XOR+a|code-block|syntax|javascript|1359161|因此，对评估的顺序没有保证。评估的顺序将由查询优化器决定。|1359162|在带有对象的语言中，可能会出现这样的情况:您可以编写只能使用短路求值进行求值的布尔表达式。您的示例代码构造通常在这些语言(C#、Delphi、VB)中使用。例如：|1359163|if(someString+==+null+%7C+someString.Length+==+0+)
++printf("no+text+in+someString");|1359164|此C#示例将在someString+==+null的情况下导致异常，因为它将被完全求值。在短路评估中，它每次都会起作用。|offset|length|style|CODE|1359165|SQL仅对无法取消初始化的标量变量(无对象)进行操作，因此无法编写无法计算的布尔表达式。如果您有一些空值，则任何比较都将返回false。|1359166|这意味着在SQL中，您不能编写根据使用短路或完全求值而不同求值的表达式。|1359167|如果SQL实现使用短路求值，那么它只能希望加速查询执行。|1359168|entityMap^0|0|0|0|0|0|0|7|I|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|15|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|17|8|@]|9|@]|A|$G|H]]|$1|O|3|P|5|6|7|18|8|@$Q|19|R|1A|S|T]]|9|@]|A|$]]|$1|U|3|V|5|6|7|1B|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1D|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|11|$]]

Main characteristic of short circuit evaluation is that it stops evaluating the expression as soon as the result can be determined. That means that rest of expression can be ignored because result will be same regardless it is evaluated or not. 

Binary boolean operators are comutative, meaning:

<pre><code>a AND b == b AND a
a OR b == b OR a
a XOR b == b XOR a
</code></pre>

so there is no guarantee on order of evaluation. Order of evaluation will be determined by query optimizer. 

In languages with objects there can be situations where you can write boolean expressions that can be evaluated only with short circuit evaluation. Your sample code construction is often used in such languages (C#, Delphi, VB). For example:

<pre><code>if(someString == null | someString.Length == 0 )
 printf("no text in someString");
</code></pre>

This C# example will cause exception if <code>someString == null</code> because it will be fully evaluated. In short circuit evaluation, it will work every time. 

SQL operates only on scalar variables (no objects) that cannot be uninitialized, so there is no way to write boolean expression that cannot be evaluated. If you have some NULL value, any comparison will return false. 

That means that in SQL you cannot write expression that is differently evaluated depending on using short circuit or full evaluation. 

If SQL implementation uses short circuit evaluation, it can only hopefully speed up query execution.

blocks|key|1574461|text|我不知道什么是短路，但我会把它写成if-else语句|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1574462|if+(@key+is+null)
begin

+++++SELECT+*+
+++++FROM+Table+t+

end
else
begin

+++++SELECT+*+
+++++FROM+Table+t+
+++++WHERE+t.Key=@key

end|code-block|syntax|javascript|1574463|此外，变量应该始终位于等式的右侧。这就使它成为了sargable。|1574464|http://en.wikipedia.org/wiki/Sargable|offset|length|1574465|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|11|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|U|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|V|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|W|8|@]|9|@$K|X|L|Y|1|Z]]|A|$]]|$1|M|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|J]]]]

i don't know about short circuting, but i'd write it as an if-else statement

<pre><code>if (@key is null)
begin

 SELECT * 
 FROM Table t 

end
else
begin

 SELECT * 
 FROM Table t 
 WHERE t.Key=@key

end
</code></pre>

also, variables should always be on the right side of the equation. this makes it sargable.

<a href="http://en.wikipedia.org/wiki/Sargable" rel="nofollow noreferrer">http://en.wikipedia.org/wiki/Sargable</a>

blocks|key|1359645|text|下面是对SQL+Server2008+R2的一个快速而肮脏的测试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1359646|SELECT+*
FROM+table
WHERE+1=0
AND+(function+call+to+complex+operation)|code-block|syntax|javascript|1359647|这会立即返回，没有任何记录。出现了某种短路行为。|1359648|然后尝试这样做：|1359649|SELECT+*
FROM+table
WHERE+(a+field+from+table)+<+0
AND+(function+call+to+complex+operation)|1359650|如果知道没有记录，就会满足这个条件：|1359651|(a+field+from+table)+<+0|1359652|这花费了几秒钟的时间，这表明短路行为不再存在，并且正在为每个记录评估复杂的操作。|1359653|希望这对大家有帮助。|1359654|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

Below a quick and dirty test on SQL Server 2008 R2:

<pre><code>SELECT *
FROM table
WHERE 1=0
AND (function call to complex operation)
</code></pre>

This returns immediately with no records. Kind of short circuit behavior was present.

Then tried this:

<pre><code>SELECT *
FROM table
WHERE (a field from table) &lt; 0
AND (function call to complex operation)
</code></pre>

knowing no record would satisfy this condition:

<pre><code>(a field from table) &lt; 0
</code></pre>

This took several seconds, indicating the short circuit behavior was not there any more and the complex operation was being evaluated for every record.

Hope this helps guys.

blocks|key|1359539|text|这在查询分析器中需要额外的4秒，所以从我可以看到的是，如果没有被缩短...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1359540|SET+@ADate+=+NULL

IF+(@ADate+IS+NOT+NULL)
BEGIN
++++INSERT+INTO+#ABla+VALUES+(1)
++++++++(SELECT+bla+from+a+huge+view)
END|code-block|syntax|javascript|1359541|如果有一个有保证的方式就好了！|1359542|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This takes an extra 4 seconds in query analyzer, so from what I can see IF is not even shorted...

<pre><code>SET @ADate = NULL

IF (@ADate IS NOT NULL)
BEGIN
 INSERT INTO #ABla VALUES (1)
 (SELECT bla from a huge view)
END
</code></pre>

It would be nice to have a guaranteed way!

blocks|key|1359902|text|简单的回答是：“短路”行为是未记录的实现。这里有一篇很好的文章解释了这个主题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1359903|Understanding+T-SQL+Expression+Short-Circuiting|offset|length|1359904|entityMap|0|LINK|mutability|MUTABLE|url|https://www.sqlservercentral.com/articles/understanding-t-sql-expression-short-circuiting^0|0|0|1B|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

The quick answer is: The &quot;short-circuit&quot; behavior is undocumented implementation.
Here's an excellent article that explains this very topic.
<a href="https://www.sqlservercentral.com/articles/understanding-t-sql-expression-short-circuiting" rel="nofollow noreferrer">Understanding T-SQL Expression Short-Circuiting</a>

Are boolean expressions in SQL WHERE clauses <a href="http://en.wikipedia.org/wiki/Short-circuit_evaluation" rel="nofollow noreferrer">short-circuit evaluated
</a>?
For example:
<pre><code>SELECT * 
FROM Table t 
WHERE @key IS NULL OR (@key IS NOT NULL AND @key = t.Key) 
</code></pre>
If <code>@key IS NULL</code> evaluates to <code>true</code>, is <code>@key IS NOT NULL AND @key = t.Key</code> 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?

Is the SQL WHERE clause short-circuit evaluated?

SQL Server

数据库

服务器

MySQL

是SQL WHERE子句中的布尔表达式例如：SELECT * FROM Table t WHERE @key IS NULL OR (@key IS NOT NULL AND @key = t.Key) 如果@key IS NULL的计算结果为true，则是否计算@key IS NOT NULL AND @key = t.Key？若否，原因为何？如果是，是否有保证？它是ANSI SQL的一部分还是

问SQL WHERE子句是否计算短路？
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL WHERE子句是否计算短路？EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL WHERE子句是否计算短路？
EN