noeld (5/24/2010)

---

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!