Popular Misconception

There is a popular misconception that NOT IN and NOT EXISTS are two ways to filter out rows present in one table and not in another table, with both methods being usable interchangeably. Popular wisdom is that the difference between the two is only in terms of performance (being on the basis of whether the larger table is the "outer" or the "inner" in the query) but that the results are the same.

However, we must remember that such an operation being an Anti-Join fails when NULLs are involved. 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.

Here is a simple demonstration :-

-- create the two test tables
SQL> create table results_table as select owner,object_name,object_type from dba_objects where owner in ('HEMANT','DBSNMP','OUTLN');

Table created.

SQL> create table query_table as select owner,object_name,object_type from dba_objects where owner in ('DBSNMP','OUTLN');

Table created.

-- A NOT IN query
SQL> SELECT r.owner, r.object_name 
                   FROM results_table r
                WHERE r.owner NOT IN (SELECT q.owner FROM query_table q)
                ORDER by 1,2;

OWNER OBJECT_NAME  
------------------------------ ------------------------------  
HEMANT DUPDB  
HEMANT MY_T_A  
HEMANT RESULTS_TABLE  
HEMANT SOURCE_TABLE  
HEMANT TEST_APPEND  
HEMANT TRACE_USER_SESSIONS  
HEMANT TRACE_USER_SESSIONS_BEGIN  
HEMANT TRACE_USER_SESSIONS_END  

8 rows selected.

-- A NOT EXISTS query

SQL> SELECT r.owner, r.object_name 
                FROM results_table r
               WHERE NOT EXISTS (SELECT '1' 
                                                           FROM query_table q
                                                         WHERE r.owner=q.owner)
               ORDER by 1,2;

OWNER OBJECT_NAME  
------------------------------ ------------------------------  
HEMANT DUPDB  
HEMANT MY_T_A  
HEMANT RESULTS_TABLE  
HEMANT SOURCE_TABLE  
HEMANT TEST_APPEND  
HEMANT TRACE_USER_SESSIONS  
HEMANT TRACE_USER_SESSIONS_BEGIN  
HEMANT TRACE_USER_SESSIONS_END  

8 rows selected.

SQL> REM So far, NOT IN and NOT EXISTS have presented the same results
SQL> REM What happens if there is a row with a NULL value ?
SQL> INSERT INTO query_table VALUES (NULL,'ABCDEFGH','TABLE');

1 row created.

SQL> -- Retry the NOT IN query
SQL> SELECT r.owner, r.object_name 
                   FROM results_table r
                 WHERE r.owner NOT IN (SELECT q.owner FROM query_table q)
                  ORDER by 1,2;

no rows selected

SQL> -- retry the NOT EXISTS query
SQL> SELECT r.owner, r.object_name 
                 FROM results_table r
               WHERE NOT EXISTS (SELECT '1' 
                                                           FROM query_table q
                                                        WHERE r.owner=q.owner)
             ORDER by 1,2;
OWNER OBJECT_NAME  
------------------------------ ------------------------------  
HEMANT DUPDB  
HEMANT MY_T_A  
HEMANT RESULTS_TABLE  
HEMANT SOURCE_TABLE  
HEMANT TEST_APPEND  
HEMANT TRACE_USER_SESSIONS  
HEMANT TRACE_USER_SESSIONS_BEGIN  
HEMANT TRACE_USER_SESSIONS_END  

8 rows selected.

SQL> REM Why ? Because of the presence of a NULL in the query_table !
SQL> REM So a "NOT IN" anti-join fails because a NULL returned cannot be compared !
SQL> REM One "workaround" is to filter out rows which contain NULLs
SQL> REM but think carefully before you do so. Are you sure you want to exclude them ?
SQL>
SQL> REM In the ideal world, such columns should be defined as NOT NULL columns !
SQL> REM That would be the right schema design !
SQL> -- test the suggested workaround
SQL> SELECT r.owner, r.object_name 
                  FROM results_table r
                WHERE r.owner NOT IN (SELECT q.owner 
                                                                 FROM query_table q
                                                              WHERE OWNER IS NOT NULL)
                ORDER by 1,2;

OWNER OBJECT_NAME  

------------------------------ ------------------------------  
HEMANT DUPDB  
HEMANT MY_T_A  
HEMANT RESULTS_TABLE  
HEMANT SOURCE_TABLE  
HEMANT TEST_APPEND  
HEMANT TRACE_USER_SESSIONS  
HEMANT TRACE_USER_SESSIONS_BEGIN  
HEMANT TRACE_USER_SESSIONS_END  

8 rows selected.


You can see that if there is a single row with a NULL value on the query predicate (the OWNER column in QUERY_TABLE), the NOT IN query actually fails.

Tom Kyte has explained this in the section on Anti-Joins in his "Effective Oracle By Design" book. And its posted by Hemant K Chitale in his http://hemantoracledba.blogspot.com./


I conclude that NOT IN will behave like <>ALL and NOT EXISTS will behave like <>ANY.