SQL Server IN vs. EXISTS Performance
exists, query-performance, sql-in, sql-server, sql-server-2005
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.
Problem
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]) ```