johan.lindell (5/24/2010)
Good comparison, David, thanks.From a performance perspective, using NOT IN is "always" slower than using EXISTS. If you try running the below queries
-- INTERSECT
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID=10
AND NOT EXISTS (
SELECT CustomerID
FROM Sales.SalesOrderHeader
WHERE OrderDate>='2004-07-01'
AND Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID
)
-- EXCEPT
SELECT CustomerID
FROM Sales.Customer
WHERE TerritoryID=10
AND NOT EXISTS (
SELECT CustomerID
FROM Sales.SalesOrderHeader
WHERE OrderDate>='2004-07-01'
AND Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID
)
instead of the NOT IN, you'll get a reduction of query time by 75 % (on my computer). The bigger the tables and the "result" in the NOT IN-query is the bigger the difference. I always try to avoid NOT IN in a case like this.
/Johan
I disagree. NOT IN *could* give you the exact same execution plan given the right indexes and stats. The optimizer in many cases knows best and chooses one plan or another depending on the data!
* Noel