SQL Server条件流 [英] SQL Server Conditional Flow

问题描述

如果我在 IF EXISTS 条件中用 AND <写入两个 SELECT 语句这些选择查询之间的/ code>子句，即使第一个 SELECT 返回false，两个查询都会被执行吗？

If I write two SELECT statements in a IF EXISTS condition with a AND clause in between these select queries, does both queries get executed even if the first SELECT returns false?

IF EXISTS (SELECT....) AND EXISTS(SELECT ....)
BEGIN

END

在这种情况下，SQL Server引擎是否同时执行SQL语句？

Does the SQL Server Engine execute both the SQL Statement in this scenario?

谢谢
Krish

Thanks Krish

推荐答案

我会将测试重写为

IF CASE
     WHEN EXISTS (SELECT ...) THEN CASE
                                   WHEN EXISTS (SELECT ...) THEN 1
                                 END
   END = 1  

这可以保证短路如此处所述但确实意味着您需要选择最便宜的e预先评估而不是将其留给优化者。

This guarantees short circuiting as described here but does mean you need to select the cheapest one to evaluate up front rather than leaving it up to the optimiser.

在我极为有限的测试中，下面的测试似乎是正确的

In my extremely limited tests below the following seemed to hold true when testing

EXISTS AND EXISTS 版似乎最有问题的。这个将一些外部半连接链接在一起。在所有情况下都没有重新安排测试的顺序，试图先做更便宜的测试（本博文后半部分讨论的问题）。在 IF ... 版本中，如果它没有短路，它将没有任何区别。但是，当这个组合谓词放在 WHERE 子句中时，计划会发生变化，并且会短路，这样重新排列可能会有所帮助。

The EXISTS AND EXISTS version seems most problematic. This chains together some outer semi joins. In none of the cases did it re-arrange the order of the tests to try and do the cheaper one first (an issue discussed in the second half of this blog post). In the IF ... version it wouldn't have made any difference if it had as it did not short circuit. However when this combined predicate is put in a WHERE clause the plan changes and it does short circuit so that rearrangement could have been beneficial.

/*All tests are testing "If False And False"*/

IF EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
AND EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1)
PRINT 'Y'
/*
Table 'spt_values'. Scan count 1, logical reads 9
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

IF EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1) 
AND EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2) 
PRINT 'Y'
/*
Table 'spt_monitor'. Scan count 1, logical reads 1
Table 'spt_values'. Scan count 1, logical reads 9
*/

SELECT 1
WHERE  EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
AND EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1)
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

SELECT 1
WHERE  EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1) 
AND EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2) 
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_values'. Scan count 1, logical reads 9

*/

所有计划这些似乎很相似。 SELECT 1 WHERE ... 版本与 IF ... 版本之间行为差异的原因是对于前者，如果条件为假，则正确的行为是不返回任何结果，因此它只链接 OUTER SEMI JOINS ，如果一个为假，那么零行结转到下一个。

The plans for all these appear very similar. The reason for the difference in behaviour between the SELECT 1 WHERE ... version and the IF ... version is that for the former one if the condition is false then the correct behaviour is to return no result so it just chains the OUTER SEMI JOINS and if one is false then zero rows carry forward to the next one.

但是 IF 版本总是需要返回1或0的结果。此计划在其外部联接中使用探测列，如果未传递 EXISTS 测试（而不是简单地丢弃该行），则将其设置为false。这意味着总是有1行进入下一个Join并且它总是被执行。

However the IF version always needs to return a result of 1 or zero. This plan uses a probe column in its outer joins and sets this to false if the EXISTS test is not passed (rather than simply discarding the row). This means that there is always 1 row feeding into the next Join and it always gets executed.

CASE 版本有一个非常相似的计划，但它使用 PASSTHRU 谓词，如果前一个 THEN ，它用来跳过JOIN的执行条件没有达到。我不确定为什么合并 AND s不会使用相同的方法。

The CASE version has a very similar plan but it uses a PASSTHRU predicate which it uses to skip execution of the JOIN if the previous THEN condition was not met. I'm not sure why combined ANDs wouldn't use the same approach.

使用的 EXISTS OR EXISTS 版本连接（ UNION ALL ）运算符作为外半连接的内部输入。这种安排意味着它可以在第一个返回时立即停止从内侧请求行（即它可以有效地短路）所有4个查询最终都得到了相同的计划，其中首先评估了更便宜的谓词。

The EXISTS OR EXISTS version used a concatenation (UNION ALL) operator as the inner input to an outer semi join. This arrangement means that it can stop requesting rows from the inner side as soon as the first one is returned (i.e. it can effectively short circuit) All 4 queries ended up with the same plan where the cheaper predicate was evaluated first.

/*All tests are testing "If True Or True"*/

IF EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=1)  
OR EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)<>1)
PRINT 'Y'
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

IF EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)<>1) 
OR EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)= 1) 
PRINT 'Y'
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

SELECT 1
WHERE  EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)= 1)  
OR EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)<>1)
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

SELECT 1
WHERE  EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)<>1) 
OR EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=1) 
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

3。添加 ELSE

我尝试使用De Morgan定律转换 AND 到或，看看是否有任何区别。转换第一个查询给出

3. Adding an ELSE

It did occur to me to try De Morgan's law to convert AND to OR and see if that made any difference. Converting the first query gives

IF NOT ((NOT EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
OR NOT EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1)))
PRINT 'Y'
ELSE
PRINT 'N'
/*
Table 'spt_monitor'. Scan count 1, logical reads 1
Table 'spt_values'. Scan count 1, logical reads 9
*/

所以这仍然没有短路行为的差异。但是，如果您删除 NOT 并反转 IF ... ELSE 的顺序，它现在 短路！

So this still doesn't make any difference to the short circuiting behaviour. However if you remove the NOT and reverse the order of the IF ... ELSE conditions it now does short circuit!

IF (NOT EXISTS(SELECT COUNT(*) FROM master..spt_monitor HAVING COUNT(*)=2)  
OR NOT EXISTS (SELECT COUNT(*) FROM master..spt_values HAVING COUNT(*)=1))
PRINT 'N'
ELSE
PRINT 'Y'
/*
Table 'Worktable'. Scan count 0, logical reads 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

这篇关于SQL Server条件流的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！