Among some SQL Server developers, it's a widely held belief that NOT IN is terribly slow, and queries should be rewritten so that they return the same result but do not use the "evil" keywords. (example).

Is there any truth to that?

Is there, for example, some known bug in SQL Server (which version?) that causes queries using NOT IN to have a worse execution plan than an equivalent query that uses

  • a LEFT JOIN combined with a NULL check or
  • (SELECT COUNT(*) ...) = 0 in the WHERE clause ?
  • 7
    That article is highly inaccurate though. "In" does not "have to run the same query over and over again for each row in TableOne". The poster there seems to believe that IN/NOT IN will always be implemented with nested loops. And I have no idea what stops SQL Server from creating a ‘plan’ is supposed to mean. – Martin Smith Nov 6 '13 at 11:06
  • 5
    @Heinzi That article you link to, should die in fire, it's full of nonsense. Like: "To replace IN, we use an INNER JOIN. They are effectively the same thing." Problem is, they are not the same thing. I wouldn't trust someone that doesn't know basic SQL, i.e. the difference between a join and a semi-join, to analyze anything about SQL-Server behaviour. – ypercubeᵀᴹ Nov 6 '13 at 12:46

I don't think it has anything to do with being terribly slow; it has to do with being potentially inaccurate. For example, given the following data - orders that could be placed either by an individual customer, or a B2B partner:

DECLARE @Customers TABLE(CustomerID INT);

INSERT @Customers VALUES(1),(2);

DECLARE @Orders TABLE(OrderID INT, CustomerID INT, CompanyID INT);

INSERT @Orders VALUES(10,1,NULL),(11,NULL,5);

Let's say I want to find all of the customers who have never placed an order. Given the data, there's only one: customer #2. Here are three ways I might go about writing a query to find that information (there are others):

SELECT [NOT IN] = CustomerID FROM @Customers 
  WHERE CustomerID NOT IN (SELECT CustomerID FROM @Orders);

SELECT [NOT EXISTS] = CustomerID FROM @Customers AS c 
  WHERE o.CustomerID = c.CustomerID);

SELECT [EXCEPT] = CustomerID FROM @Customers


                 -- <-- no results. Is that what you expected?



Now, there are some performance issues as well, and I talk about them in this blog post. Depending on the data and indexes, NOT EXISTS will usually outperform NOT IN, and I don't know if it could ever perform worse. You should also note that EXCEPT can introduce a distinct sort operation, so you may end up with different data (again, depending on the source). And that the popular LEFT OUTER JOIN ... WHERE right.column IS NULL pattern is always the worst performer.

Martin Smith has a lot of good supporting information in his answer on SO, too.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.