SQL Server 条件流
如果我在 IF EXISTS 条件中编写两个 SELECT 语句，并在这些选择查询之间使用 AND 子句，则两个查询都会执行，即使第一个 SELECT 返回 false？
IF EXISTS (SELECT....) AND EXISTS(SELECT ....)
BEGIN
END
在这种情况下，SQL Server 引擎是否执行这两个 SQL 语句？
谢谢 克里什
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我将重写测试，因为
这保证了短路如此处所述 但这确实意味着您需要选择最便宜的一个来预先评估，而不是将其留给优化器。
时似乎成立。
在我极其有限的测试中，以下内容在测试1
EXISTS AND EXISTSEXISTS AND EXISTS版本似乎是最有问题的。此将一些外部半连接链接在一起。在任何情况下，它都没有重新安排测试的顺序，以尝试先进行更便宜的测试（本博文后半部分讨论的问题）。在IF ...版本中，如果有的话也不会有任何区别，因为它没有短路。然而，当这个组合谓词放入WHERE子句中时，计划会发生变化，并且它确实短路，因此重新排列可能会有所帮助。所有这些计划看起来都非常相似。
SELECT 1 WHERE ...版本和IF ...版本之间行为存在差异的原因是，对于前一个版本，如果条件为 false，则正确的行为是不返回任何结果，因此它只是链接OUTER SEMI JOINS，如果其中一个为 false，则零行结转到下一行。但是，
IF版本始终需要返回 1 或零的结果。该计划在其外连接中使用探测列，并在 EXISTS 测试未通过时将其设置为 false（而不是简单地丢弃该行）。这意味着总是有 1 行输入到下一个 Join 并且它总是被执行。CASE版本具有非常相似的计划，但它使用PASSTHRU谓词，如果之前的THEN条件满足，则使用该谓词跳过 JOIN 的执行没有遇见。我不确定为什么组合的AND不会使用相同的方法。2.
EXISTS OR EXISTSEXISTS OR EXISTS版本使用串联 (UNION ALL) 运算符作为外部半连接的内部输入。这种安排意味着一旦返回第一个行，它就可以停止从内侧请求行（即，它可以有效地短路）。所有 4 个查询都以相同的计划结束，其中首先评估更便宜的谓词。3. 添加
ELSE我确实想到尝试德摩根定律将
AND转换为OR并看看这是否有任何区别。转换第一个查询给出因此，这对短路行为仍然没有任何影响。但是，如果您删除
NOT并颠倒IF ... ELSE条件的顺序，它现在会短路！I would rewrite the test as
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
1.
EXISTS AND EXISTSThe
EXISTS AND EXISTSversion 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 theIF ...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 aWHEREclause the plan changes and it does short circuit so that rearrangement could have been beneficial.The plans for all these appear very similar. The reason for the difference in behaviour between the
SELECT 1 WHERE ...version and theIF ...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 theOUTER SEMI JOINSand if one is false then zero rows carry forward to the next one.However the
IFversion 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 theEXISTStest 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.The
CASEversion has a very similar plan but it uses aPASSTHRUpredicate which it uses to skip execution of the JOIN if the previousTHENcondition was not met. I'm not sure why combinedANDs wouldn't use the same approach.2.
EXISTS OR EXISTSThe
EXISTS OR EXISTSversion 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.3. Adding an
ELSEIt did occur to me to try De Morgan's law to convert
ANDtoORand see if that made any difference. Converting the first query givesSo this still doesn't make any difference to the short circuiting behaviour. However if you remove the
NOTand reverse the order of theIF ... ELSEconditions it now does short circuit!我相信您可以依赖大多数（如果不是全部）现代语言中 IF 语句的短路行为。您可以尝试通过首先放置一个真实条件并用
1/0替换第二个条件来进行测试，如果不发生短路，这会给您带来除以零的错误，如下所示：如果您不这样做不相信这一点，您始终可以重写查询来执行此操作：
I believe you can rely on the short-circuiting behavior of IF statements in most, if not all, modern languages. You could try testing by putting a true condition first and replacing your second condition with
1/0which would give you a divide by zero error if short circuiting doesn't occur, like so:If you don't trust that, you could always rewrite your query to do this:
如果我使用 AND 执行查询，即使如此，两个表都会被访问
SET STATISTICS IO ON
IF EXISTS (SELECT * from master..spt_values where [name] = 'rpcc') 和 EXISTS(SELECT * from master..spt_monitor where pack_sent = 5235252) PRINT 'Y'
表 'spt_monitor'。扫描计数 1、逻辑读取 1、物理读取 0、预读读取 0、lob 逻辑读取 0、lob 物理读取 0、lob 预读读取 0。
表“spt_values”。扫描计数 1、逻辑读取 17、物理读取 0、预读读取 0、lob 逻辑读取 0、lob 物理读取 0、lob 预读读取 0。
If I execute the query with an AND, even then , both the tables are accessed
SET STATISTICS IO ON
IF EXISTS (SELECT * from master..spt_values where [name] = 'rpcc') and EXISTS(SELECT * from master..spt_monitor where pack_sent = 5235252) PRINT 'Y'
Table 'spt_monitor'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'spt_values'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
我从 sqlteam 上的以下博客条目中引用了以下内容：
SQL Server 如何短路 WHERE 条件评估
有关更多详细信息，请检查上述博客条目中的第一个链接，该链接指向另一个博客：
SQL Server是否短路？
I'm taking the following quotes from the following blog entry on sqlteam:
How SQL Server short-circuits WHERE condition evaluation
For further details check the first link in the above blog entry, which is leading to another blog:
Does SQL Server Short-Circuit?
有一个有趣的观察。我有两个表 tbla 和 tblb。 tbla 有一个主键（idvalue），在 tblb 中用作外键。两者都有 idvalue = 1 的行，但没有 idvalue 为 -1 的行。现在，下面的查询仅使用一张表
给出
这很明显，因为优化器知道由于存在主键-外键关系，因此如果 tbla 中缺少该值，则它永远不会出现在 tblb 中。因此，优化器将决定不需要在 tblb 上查找的运行时。
但是，如果我编写查询，
那么两个表都会被访问。这是非常明显的，因为优化器知道它必须检查两个地方以确保满足 AND 条件。
然而，在这两种情况下，实际执行计划都显示对 tbla 和 tblb 的查找。这对我来说似乎很奇怪。对此有什么想法吗？
Had an interesting observation. I have two tables tbla and tblb. tbla has a primary key (idvalue) which is used as a foreign key in tblb. Both has a row with idvalue = 1, but no row with idvalue of -1. Now, below query uses only one table
Gives
This is obvious because the optimizer knows that since there is a primary key-foreign key relationship, so if the value is missing in tbla, it can never be present in tblb. So, optimizer will decide on runtime that seek on tblb is not required.
However, if I write the query as
then both the tables are accessed. This is pretty obvious since here the optimizer knows that it has to check in both places to be sure that the AND condition is satisfied.
However, in both the cases, the actual execution plan shows seeks on both tbla and tblb. This seems strange to me. Any thoughts on this?
不。
我刚刚在 SQL Server 2008 中进行了测试，如果第一次评估失败，它会立即跳过
IF块。这很容易测试。
对于您的第一次评估，请执行类似
IF 1=0的操作，而对于第二次评估，请执行任何操作，然后显示实际的执行计划。在我的例子中，它只进行常量扫描来评估这些常量。Nope.
I just tested in SQL Server 2008 and if the first evaluation fails it immediately skips the
IFblock.This is very easy to test.
For your first evaluation do something like
IF 1=0and for your second do anything, then show actual exec plan. In mine it only does a Constant Scan to eval those constants.您可以通过执行以下操作来防止第二次扫描：
You can prevent the second scan by doing this: