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.

According to MSFT'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?

Save me from confusion :)

share|improve this question
add comment

1 Answer

up vote 15 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 http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server

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
2  
+1 For the DISTINCT - I have cleaned up so many queries by removing DISTINCT and changing to an EXISTS check it's not even funny. –  JNK Jul 21 '11 at 19:37
1  
+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
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.