NOT IN Vs. NOT Exists

What is the Difference Between Not IN and Not Exists Operator?

In Oracle, a NULL cannot be compared to any other value, not even another NULL. Therefore, a NOT IN operation would fail if the result set being probed returns a NULL. In such a case, the results of a NOT IN query is 0 rows while a NOT EXISTS query would still show the rows present in the one table but not in the other table.
“NOT IN” and “NOT EXISTS” clauses are not the same functionally or performance wise and, therefore, should be used appropriately.

NOT IN Operator	NOT EXISTS Operator
The Exception is where you have a NULL in the NOT IN sub query. This causes the NOT IN to always be false and will not return any rows. NOT IN does not have the ability to compare the NULL values.	Not Exists is recommended is such cases.
When using “NOT IN”, the query performs nested full table scans.	Whereas for “NOT EXISTS”, query can use an index within the sub-query.

Now Let’s understand this practically.

Consider below table data-

SQL> SELECT * From EMP;  
     ID NAME    MANAGER_ID  
 ---------- ---------- ----------  
     101 Ravi         105  
     102 Rajan        107  
     103 Pritesh      104  
     104 Manu         110  
     105 Gaurav       107  
     106 Subho        101  
     107 Akhil        105  
     108 Neeraj  
     109 Shiva        101  
     110 Aman         101  
     111 Nik          102  
     112 Sri  
     113 Shaan  
 13 rows selected.  

List the Employees who are not managers?

Using NOT IN

SQL> SELECT * From EMP  
     Where ID NOT IN(SELECT Manager_ID From EMP);  
 no rows selected  

So as per above query all Employees are mangers which is not true. Let’s try this with NOT Exists operator.

Using NOT Exists

SQL> SELECT * From EMP E1  
     Where NOT EXISTS(SELECT 1 From EMP E2 Where E2.Manager_ID=E1.ID);  
     ID NAME      MANAGER_ID  
 ---------- ---------- ----------  
     111 Nik        102  
     112 Sri  
     103 Pritesh    104  
     113 Shaan  
     108 Neeraj  
     109 Shiva       101  
     106 Subho       101  
 7 rows selected.  

Now above query returns 7 rows which was expected. So when we have NULL, we need to make sure we define sub query with where condition to exclude NULLs (NOT IN operation would fail if the result set being probed returns a NULL)

Modified NOT IN Query

SQL> SELECT * From EMP  
     Where ID NOT IN(SELECT MANAGER_ID From EMP Where MANAGER_ID IS NOT NULL);  
     ID NAME    MANAGER_ID  
 ---------- ---------- ----------  
     111 Nik        102  
     112 Sri  
     103 Pritesh      104  
     113 Shaan  
     108 Neeraj  
     109 Shiva       101  
     106 Subho       101  
 7 rows selected.  

NOTE- We don't have to care to use NOT IN or NOT EXISTS (when they are equivalent) as Oracle will rewrite it in the most efficient way. Also, an outer join is also a good option to not in and not exists. (it can be quicker as well converting FILTER conditions to hash joins).

Oracle will sometimes convert "not in" to hash joins automatically, but an outer join would help oracle make an easier decision.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.