EXISTS vs. IN

I must admit, I am guilty of using IN a lot in the WHERE clause of my queries and I am not proud of it. Replacing IN with EXISTS has significantly improved the performance of my queries and I doubt i will be going back to using IN.

SELECT * FROM Customers
WHERE CustomerID IN(SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) <10  )

SELECT * FROM Customers as c
WHERE EXISTS (SELECT 1 FROM Orders GROUP BY CustomerID HAVING COUNT(OrderID) <10 AND CustomerID=c.CustomerID)

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 subquery before further processing. The IN statement requires SQL Server to generate a complete result set before making a comparison.