Thursday, 2 August 2012

Replace NOT IN with JOIN

Example of how to replace NOT IN clause with JOIN


/*NOT IN query*/

USE AdventureWorks;
GO
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;

2 comments: