When to use IN operator

Using WHERE IN and WHERE NOT IN clauses in T-SQL code can produce an execution plan involving one or more nested loops. This increases the number of comparisons SQL Server must perform exponentially. Use the WHERE IN clause only if you have a short list of values you need to evaluate:

SELECT *
FROM Customers
WHERE CustomerID NOT IN
   (SELECT CustomerID FROM Orders)

Replace the WHERE IN clause with OUTER JOIN if you're using a subquery to generate a potentially large list. Doing so you can improve performance significantly:

SELECT c.*
FROM Customers c
LEFT OUTER JOIN Orders o
ON o.CustomerID = c.CustomerID
WHERE o.CustomerID IS NULL

In this case, the second query uses LEFT OUTER JOIN, producing an execution plan that lets it run about three times faster than the first query.

Is this useful?