SQL Server IN 与 EXISTS 性能

2021-12-02 00:00:00 sql-server-2005 sql-server exists sql-in query-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)

对比

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.

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

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

相关文章