Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Among 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 ?
share|improve this question
3  
Related NOT IN vs NOT EXISTS –  Martin Smith Nov 6 '13 at 11:04
5  
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
3  
The author of that post in the question lost me at "avoided at all costs".. Martin's answer linked in his comment has facts, experiments and science. That post has confusion, FUD and inaccurate assumptions. –  Mike Walsh Nov 6 '13 at 11:48
4  
@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
add comment

1 Answer 1

up vote 11 down vote accepted

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 NOT EXISTS (SELECT 1 FROM @Orders AS o
  WHERE o.CustomerID = c.CustomerID);

SELECT [EXCEPT] = CustomerID FROM @Customers
EXCEPT SELECT CustomerID FROM @Orders;

Results:

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

NOT EXISTS
----------
2

EXCEPT
------
2

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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