SQL SERVER – Better Performance – LEFT JOIN or NOT IN?

http://blog.sqlauthority.com/2008/04/22/sql-server-better-performance-left-join-or-not-in/

First of all answer this question : Which method of T-SQL is better for performance LEFT JOIN or NOT IN when writing query? Answer is : It depends! It all depends on what kind of data is and what kind query it is etc. In that case just for fun guess one option LEFT JOIN or NOT IN. If you need to refer the query which demonstrates the mentioned clauses, review following two queries.

USE AdventureWorks;GOSELECT ProductIDFROM Production.ProductWHERE ProductIDNOT IN (SELECT ProductIDFROM Production.WorkOrder);GOSELECT p.ProductIDFROM Production.Product pLEFT JOIN Production.WorkOrder w ON p.ProductID = w.ProductIDWHERE w.ProductID IS NULL;GO
Now let us examine the actual execution plan of both the queries. Click on image to see larger image.

You can clearly observe that first query with NOT IN takes 20% resources of execution plan and LEFT JOIN takes 80% resources of execution plan. It is better to use NOT IN clause over LEFT JOIN in this particular example. Please note that this is not generic conclusion and applies to this example only. Your results may vary on many factors. Let me know your comments if you have guessed this correct or not.