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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

According to Microsoft's book on database development Exam 70-433: Microsoft SQL Server 2008 Database Development:

Neither leading wildcard characters not NOT logic allow the query optimizer to use indexes to optimize the search. For optimal performance, you should avoid using the NOT keyword and leading wildcard symbols.

So I took that to be NOT IN, NOT EXISTS etc

Now with regards to this SO question, I thought that the chosen solution by @GBN would violate the statement given above.

Apparently, it does not.

So my question is: Why?

share|improve this question
up vote 19 down vote accepted
  • NOT IN (SELECT ...) and NOT EXISTS (SELECT .. WHERE correlation..) are "Anti Semi Joins". That is, recognised set based operations

  • WHERE NOT (MyColumn = 1) is a filter that requires all rows to be looked at

For more info, see:

Edit: for completeness

LEFT JOINs often perform worse. See

This same site notes that in MySQL, NOT EXISTS isn't optimised like other RDBMS and LEFT JOIN is better

In SQL Server, I know from experience that LEFT JOIN doesn't run as well as NOT EXISTS. You also often need DISTINCT to get the same results which another processing step.

share|improve this answer
+1 But: NOT (Column = x) is SARGable, though a scan is frequently estimated as cheaper than the seek operation(s). SELECT * FROM AdventureWorks.Production.Product WHERE NOT (ProductID = 1); produces a seek plan in all SQL Server versions from 2000 to 2012 inclusive. I don't have an earlier version than 2000 to hand for testing. – Paul White Dec 15 '12 at 6:27

Your Answer


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.