T-SQL 中的 IF EXISTS
如果我们在 IF EXISTS 中有一个 SELECT 语句，那么执行是否会在表中找到记录后立即停止？例如：
IF EXISTS(SELECT * FROM table1 WHERE Name='John' )
return 1
else
return 0
如果表中存在名为 John 的行，它会停止执行并返回 1 还是遍历整个表寻找更多匹配项？
If we have a SELECT statement inside an IF EXISTS, does the execution stop as soon as it finds a record in the table? For example:
IF EXISTS(SELECT * FROM table1 WHERE Name='John' )
return 1
else
return 0
If a row exists in the table with the name = John, does it stops execution and returns 1 or does it traverses through the entire table looking for more matches?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
是的，它会停止执行，因此这通常比 HAVING COUNT(*) > 更可取。 0 但通常不会。
使用
EXISTS，如果您查看执行计划，您将看到从table1出来的实际行数不会超过1，无论匹配记录的数量是多少。在某些情况下，SQL Server 可以在简化阶段将
COUNT查询的树转换为与EXISTS相同的树（使用半连接且看不到聚合运算符） ) <一href="https://web.archive.org/web/20190319012047/http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends .aspx" rel="noreferrer">这里的评论中讨论了一个示例。然而，对于比问题中显示的更复杂的子树，您有时可能会发现
COUNT的性能比EXISTS更好。因为半连接只需要从子树中检索一行，所以这可能会鼓励针对树的该部分使用嵌套循环的计划 - 这在实践中可能无法达到最佳效果。Yes it stops execution so this is generally preferable to
HAVING COUNT(*) > 0which often won't.With
EXISTSif you look at the execution plan you will see that the actual number of rows coming out oftable1will not be more than 1 irrespective of number of matching records.In some circumstances SQL Server can convert the tree for the
COUNTquery to the same as the one forEXISTSduring the simplification phase (with a semi join and no aggregate operator in sight) an example of that is discussed in the comments here.For more complicated sub trees than shown in the question you may occasionally find the
COUNTperforms better thanEXISTShowever. Because the semi join needs only retrieve one row from the sub tree this can encourage a plan with nested loops for that part of the tree - which may not work out optimal in practice.在这种情况下不需要“else”：
There's no need for "else" in this case: