Not Exists and Not In difference

 

• NULLs

  NOT IN will not match any rows when the subquery returns even one null. NOT EXISTS can handle the NULL value.
• Complexity

  NOT IN is simpler and tests for the presence of an element in a set. NOT EXISTS can handle more complicated queries, including grouping, results with multiple conditions, and can take advantage of indexes.
• Performance

  NOT EXISTS is significantly faster than NOT IN, especially when the subquery result is very large.
• Nested or Hash full table scans

  NOT IN performs nested full table scans. NOT EXISTS can use an index within the sub-query.