Transact SQL NOT IN versus NOT EXISTS
12/7/2007 12:41:34 PM
It's been awhile since I've posted anything to the SQL category. Yesterday I was working on some SQL queries and found something interesting. One of my queries had a subquery as part of a NOT IN. As the query logic became more complex, the time to execute the query increased as well. After some Google'ing on the subject I came across many blog entries mentioning how using this syntax is ill-advised. The suggestions varied, but the common denominator seemed to advise use EXISTS.
EXISTS simply returns true or false depending on the results of a subquery.
Here's a query using NOT IN. We only want to select fields where our PK isn't included in the subquery.
SELECT F FROM T WHERE PK NOT IN (SELECT PK FROM T2 WHERE F = 'bar')
Here is that very same query written utilizing NOT EXISTS.
SELECT F FROM T WHERE NOT EXISTS (SELECT PK FROM T2 WHERE F = 'bar' AND PK = T.PK)
The original article that inspired the change. One of the contributors mentioning EXISTS being more efficient only when the result set is larger than 12 rows. I'd like to see some test results. I will say that after implementing this change the response time of the query was almost instantaneous. We're talking about a database with 1000's of records.
SQL
