SQL Server IN与EXISTS的性能 [英] SQL Server IN vs. EXISTS Performance

问题描述

我很好奇下面的哪一个会更有效?

I'm curious which of the following below would be more efficient?

我一直对使用IN持谨慎态度，因为我相信SQL Server会将结果集转换为大的IF语句.对于较大的结果集，这可能会导致性能下降.对于较小的结果集，我不确定哪一个都更好.对于较大的结果集，EXISTS会更有效吗?

I've always been a bit cautious about using IN because I believe SQL Server turns the result set into a big IF statement. For a large result set, this could result in poor performance. For small result sets, I'm not sure either is preferable. For large result sets, wouldn't EXISTS be more efficient?

WHERE EXISTS (SELECT * FROM Base WHERE bx.BoxID = Base.BoxID AND [Rank] = 2)

vs.

WHERE bx.BoxID IN (SELECT BoxID FROM Base WHERE [Rank = 2])

推荐答案

EXISTS会更快，因为一旦引擎找到了命中点，它就会退出，因为条件已经证明是正确的.

EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true.

使用IN，它将在进一步处理之前从子查询中收集所有结果.

With IN, it will collect all the results from the sub-query before further processing.

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