当前位置：文江博客话题详情

SQL Server 条件流

发布于 2024-10-30 01:29:38 字数 274 浏览 0 评论 0 原文

如果我在 IF EXISTS 条件中编写两个 SELECT 语句，并在这些选择查询之间使用 AND 子句，则两个查询都会执行，即使第一个 SELECT 返回 false？

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

END

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

谢谢克里什

原文

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

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

Thanks
Krish

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萌无敌 2024-11-06 01:29:38

我将重写测试，因为

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

这保证了短路如此处所述但这确实意味着您需要选择最便宜的一个来预先评估，而不是将其留给优化器。

时似乎成立。

在我极其有限的测试中，以下内容在测试1

EXISTS AND EXISTS EXISTS AND EXISTS 版本似乎是最有问题的。此将一些外部半连接链接在一起。在任何情况下，它都没有重新安排测试的顺序，以尝试先进行更便宜的测试（本博文后半部分讨论的问题）。在 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 或零的结果。该计划在其外连接中使用探测列，并在 EXISTS 测试未通过时将其设置为 false（而不是简单地丢弃该行）。这意味着总是有 1 行输入到下一个 Join 并且它总是被执行。

CASE 版本具有非常相似的计划，但它使用 PASSTHRU 谓词，如果之前的 THEN 条件满足，则使用该谓词跳过 JOIN 的执行没有遇见。我不确定为什么组合的 AND 不会使用相同的方法。

2. `EXISTS OR EXISTS`

EXISTS OR 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 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`

我确实想到尝试德摩根定律将 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 1
Table 'spt_values'. 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 0
Table 'spt_monitor'. Scan count 1, logical reads 1
*/

I would rewrite the test as

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

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 EXISTS`

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

*/

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.

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.

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.

2. `EXISTS OR EXISTS`

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

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

回复收藏 0 原文

纵性 2024-11-06 01:29:38

我相信您可以依赖大多数（如果不是全部）现代语言中 IF 语句的短路行为。您可以尝试通过首先放置一个真实条件并用 1/0 替换第二个条件来进行测试，如果不发生短路，这会给您带来除以零的错误，如下所示：

IF 1>0 OR 1/0 BEGIN
  PRINT 'Short Circuited'
END

如果您不这样做不相信这一点，您始终可以重写查询来执行此操作：

IF EXISTS(SELECT...) BEGIN
  IF EXISTS(SELECT...) BEGIN
    ...
  END
END

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/0 which would give you a divide by zero error if short circuiting doesn't occur, like so:

IF 1>0 OR 1/0 BEGIN
  PRINT 'Short Circuited'
END

If you don't trust that, you could always rewrite your query to do this:

IF EXISTS(SELECT...) BEGIN
  IF EXISTS(SELECT...) BEGIN
    ...
  END
END

回复收藏 0 原文

残花月 2024-11-06 01:29:38

如果我使用 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。

回复收藏 0 原文

只等公子 2024-11-06 01:29:38

我从 sqlteam 上的以下博客条目中引用了以下内容：

SQL Server 如何短路 WHERE 条件评估

当你感觉像这样的时候它就会发生，但不是以你立即想到的方式。

作为开发人员，您必须意识到SQL Server 不会像其他编程语言那样进行短路，并且您无法强制它这样做 >.

有关更多详细信息，请检查上述博客条目中的第一个链接，该链接指向另一个博客：

SQL Server是否短路？

最后的判决？好吧，我还没有真正拥有一个，但可以肯定地说，唯一可以确保特定短路的时间是当您在 CASE 表达式中表达多个 WHEN 条件时。标准布尔表达式，优化器将根据您正在查询的表、索引和数据来移动它认为合适的内容。

回复收藏 0 原文

圈圈圆圆圈圈 2024-11-06 01:29:38

有一个有趣的观察。我有两个表 tbla 和 tblb。 tbla 有一个主键（idvalue），在 tblb 中用作外键。两者都有 idvalue = 1 的行，但没有 idvalue 为 -1 的行。现在，下面的查询仅使用一张表

select 1
where exists
(select 1 from tbla where idvalue = -1)
and exists (select 1 from tblb where idvalue= 1)

给出

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbla'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

这很明显，因为优化器知道由于存在主键-外键关系，因此如果 tbla 中缺少该值，则它永远不会出现在 tblb 中。因此，优化器将决定不需要在 tblb 上查找的运行时。

但是，如果我编写查询，

select 1
where exists
(select 1 from tbla where idvalue = 1)
and exists (select 1 from tblb where idvalue= -1)

那么两个表都会被访问。这是非常明显的，因为优化器知道它必须检查两个地方以确保满足 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

select 1
where exists
(select 1 from tbla where idvalue = -1)
and exists (select 1 from tblb where idvalue= 1)

Gives

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbla'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

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

select 1
where exists
(select 1 from tbla where idvalue = 1)
and exists (select 1 from tblb where idvalue= -1)

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?

回复收藏 0 原文

梦行七里 2024-11-06 01:29:38

不。

我刚刚在 SQL Server 2008 中进行了测试，如果第一次评估失败，它会立即跳过 IF 块。

这很容易测试。

对于您的第一次评估，请执行类似 IF 1=0 的操作，而对于第二次评估，请执行任何操作，然后显示实际的执行计划。在我的例子中，它只进行常量扫描来评估这些常量。

回复收藏 0 原文

何以笙箫默 2024-11-06 01:29:38

您可以通过执行以下操作来防止第二次扫描：

declare @test bit
select @test = case when exists(select 1...) then 1 else 0 end
if @test = 1
begin
    --1st test passed
    select @test = case when exists(select 2...) then 1 else 0 end
end
if @test = 1
begin
    print 'both exists passed'
end

You can prevent the second scan by doing this:

declare @test bit
select @test = case when exists(select 1...) then 1 else 0 end
if @test = 1
begin
    --1st test passed
    select @test = case when exists(select 2...) then 1 else 0 end
end
if @test = 1
begin
    print 'both exists passed'
end

回复收藏 0 原文

~没有更多了~

关于作者

清浅ˋ旧时光

暂无简介

0 文章

0 评论

24 人气

关注发私信

友情链接

文江博客

SQL Server 条件流

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（7）

在我极其有限的测试中，以下内容在测试1

2. `EXISTS OR EXISTS`

3. 添加 `ELSE`

1. `EXISTS AND EXISTS`

2. `EXISTS OR EXISTS`

3. Adding an `ELSE`

关于作者

相关话题

热门标签

推荐作者

1CH1MKgiKxn9p

ゞ记忆︶ㄣ

JackDx

信远

yaoduoduo1995

霞映澄塘

友情链接

SQL Server 条件流

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（7）

在我极其有限的测试中，以下内容在测试1

2. EXISTS OR EXISTS

3. 添加 ELSE

1. EXISTS AND EXISTS

2. EXISTS OR EXISTS

3. Adding an ELSE

关于作者

相关话题

热门标签

推荐作者

1CH1MKgiKxn9p

ゞ记忆︶ㄣ

JackDx

信远

yaoduoduo1995

霞映澄塘

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

2. `EXISTS OR EXISTS`

3. 添加 `ELSE`

1. `EXISTS AND EXISTS`

2. `EXISTS OR EXISTS`

3. Adding an `ELSE`