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

这样可以保证as described here的短路,但这意味着您需要选择最便宜的来评估前端,而不是将其置于优化器.

在我非常有限的测试中,以下似乎在测试中成立

现在和现在

现在和现在的版本似乎最有问题.这个chains together some outer semi joins.在没有一个例子中,它重新排列了测试的顺序,尝试先做便宜的(an issue discussed in the second half of this blog post).在IF …版本中,如果没有短路,它将不会有任何差异.然而,当这个组合谓词放在一个WHERE子句中时,计划发生变化,并且它发生短路,从而重新排列可能是有益的.

/*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 …版本之间的行为差​​异的原因是,对于前者,如果条件为false,那么正确的行为是不返回任何结果,因此它只是链接OUTER SEMI JOINS,如果一个是false,那么零行会继续下一个.

然而,IF版本始终需要返回1或0的结果.此计划在外连接中使用探测列,如果EXISTS测试未被传递(而不是简单地丢弃该行),则将其设置为false.这意味着总是有1行进入下一个Join,它总是被执行.

CASE版本有一个非常相似的计划,但它使用一个PASSTHRU谓词,如果前一个THEN条件不满足,它将跳过执行JOIN.我不知道为什么组合ANDs不会使用相同的方法.

现在或现在

EXISTS或EXISTS版本使用连接(UNION ALL)运算符作为外部半连接的内部输入.这种安排意味着一旦第一个返回(即它可以有效地短路),它就可以从内侧停止请求行.所有4个查询以相同的计划结束,首先评估便宜的谓词.

/*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 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 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 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 1
*/

3.添加ELSE

事实上,我想要尝试德摩根的法律将AND转换为OR,看看是否有所作为.转换第一个查询

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 9
*/

所以这对短路行为来说还是没有任何区别的.但是,如果您删除NOT并反转IF … ELSE条件的顺序,它现在会短路！

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 1
*/