IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!

In Oracle SQL queries, IN and EXISTS are interchangeable. Many of us assume therefore, that NOT IN and NOT EXISTS are also interchangeable.

A big mistake.

See how NOT IN and NOT EXISTS behave differently in this small example.

Writing a query to find the number of employees in emp table who are not managers. The logic used is: get count where the employee id is not present in as mgr id in the same table.

The NOT IN version:

SQL> -- Using NOT IN
SQL> -- Count employees who
SQL> -- are not managers
SQL> select count(*)
  2  from emp e1
  3  where e1.empno not in
  4    (select e2.mgr
  5     from emp e2);

  COUNT(*)
----------
         0

OK, that says there are no such employees. Let’s try it with NOT EXISTS now.

The NOT EXISTS version:

SQL> -- Using NOT EXISTS
SQL> -- Count employees who
SQL> -- are not managers
SQL> select count(*)
  2  from emp e1
  3  where not exists
  4    (select 'Y'
  5     from emp e2
  6     where e2.mgr = e1.empno)

  COUNT(*)
----------
         8

Whoa! 8 such employees! Which answer is correct?

Let’s verify with the help of a flag against each employee – yes_mgr or no_mgr. We calculate this flag with help of the CASE statement.

SQL> select e.empno
  2       , e.mgr
  3       , case when
  4             (select 'Y' from emp m
  5              where m.mgr = e.empno
  6              and rownum = 1) is null
  7            then 'no_mgr'
  8            else 'yes_mgr'
  9         end is_mgr
 10  from emp e;

     EMPNO        MGR IS_MGR
---------- ---------- -------
      7369       7902 no_mgr
      7499       7698 no_mgr
      7521       7698 no_mgr
      7566       7839 yes_mgr
      7654       7698 no_mgr
      7698       7839 yes_mgr
      7782       7839 yes_mgr
      7788       7566 yes_mgr
      7839            yes_mgr
      7844       7698 no_mgr
      7876       7788 no_mgr
      7900       7698 no_mgr
      7902       7566 yes_mgr
      7934       7782 no_mgr

14 rows selected.

It turns out that there actually are eight employees who are flagged no_mgr, so the NOT EXISTS version is all right.

Why does NOT IN give a “wrong” result?!

Well, it isn’t a wrong result, that’s how it is supposed to work! The column ‘mgr’ in the emp table is nullable. As per Oracle’s treatment of NULLs:

• where 3 in (3, null) is TRUE as 3 is in (3).
• where 3 not in (1, null) is UNKNOWN as it is not known whether 3 is in null or not.

So, the NOT IN condition does not equate to true if the value being matched has nulls.

Conclusion

• NOT IN and NOT EXISTS are not identical if the subquery can potentially return nulls.
• Use NOT IN only if you are 100% certain that the subquery will not return nulls.