Search code examples
sql-serversql-server-2005existsquery-performancesql-in

SQL Server IN vs. EXISTS Performance

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

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])
Solution

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

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