SQL Server Performance

NOT IN versus EXISTS

Discussion in 'T-SQL Performance Tuning for Developers' started by sql777, Nov 2, 2002.

  1. sql777 New Member

    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?
  2. bradmcgehee New Member

    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)http://www.sql-server-performance.com/sql_performance_tuning_review.asp, 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
    Webmaster
    SQL-Server-Performance.Com

Share This Page