Replace NOT IN with JOIN

SQLTechnet

Vinoth is currently a Senior SQL Server DBA and 10 years of experience as SQL Server DBA. Started my career as a DBA in SQL 6.5/7 has worked in all subsequent SQL Server version. Vinoth has worked in some of the largest SQL server environments in the world in various domains ranging from Finance, Retail, Manufacturing, Consulting, Web etc. Vinoth has Engineering Degree in Computer Science and has certified in MCITP - Database Adminstrator in 2008/2005, MCDBA and ITIL Foundation V3.

Posted on 2 August 2012
Comments
Briefcase
Print

Example of how to replace NOT IN clause with JOIN

/*NOT IN query*/

USE AdventureWorks;

SELECT ProductID

FROM Production.Product

WHERE ProductID

NOT IN (

SELECT ProductID

FROM Production.WorkOrder);

/*JOIN Query*/:-

SELECT p.ProductID

FROM Production.Product p

LEFT JOIN Production.WorkOrder w ON p.ProductID = w.ProductID

WHERE w.ProductID IS NULL;

Comments

Leave a comment on the original post [www.sqltechnet.com, opens in a new window]

Loading comments...

Get Your Own Blog

Contact the author

All Blogs

Feeds

Archives for this blog

SQLTechnet

Replace NOT IN with JOIN

Comments