NOT IN versus EXISTS | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive


What are the key differences in using either: NOT IN or NOT EXISTS Are these one and the same or is there a query plan difference?
NOT IN is used to determine if a given value does not match any value in a subquery or a list of specified values. NOT EXISTS is used to specify a subquery to test for the non-existence of rows. They are both very similar, although NOT IN allows you to provide a list of data, while NOT EXISTS does not, but both allow you to compare against a subquery. If you are using them to compare against a subquery, the execution plan of each variation may very well be different, and depending on the circumstances, one variation may be faster than another. According to the book, SQL Performance Tuning, by Peter Gulutzman and Trudy Pelzer (Addison Wesley), here are some suggestions on when to use NOT IN and when to use NOT EXISTS: –If table 1 has many rows, and table 2 has few rows, then you should use NOT IN. –If the outer query has an additional restrictive expression, use EXISTS. –If the outer query is WHERE NOT, use NOT EXISTS. In chapter 6 of this book, which is on subqueries, you will find much more information on this topic. Ultimately, you may want to try both variations and see how they perform against each other, using Query Analyzer to view the execution plans of each.
Brad M. McGehee