NOT EXISTS v NOT IN

This example, tested on Oracle 10, shows how NOT EXISTS may be more efficient than NOT IN.
 

First, create an emp table for employees E1 through E9999:

SQL> create table emp
  2  (empno  varchar2(5),
  3   deptno varchar2(5))
  4  /

Table created.

SQL>

Next, create a dept table for departments D1 through D9999:

SQL> create table dept
  2  (deptno varchar2(5))
  3  /

Table created.

SQL>

Put employee E1 in department D1, employee E2 in department D2 etc:

SQL> declare
  2  begin
  3  for ctr in 1..9999 loop
  4  insert into emp values ('E'||ctr,'D'||ctr);
  5  insert into dept values ('D'||ctr);
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> 

Create an index and gather statistics on the emp table:

SQL> create index deptno_index on emp(deptno)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats -
> (ownname => 'SYSTEM', tabname => 'EMP');

PL/SQL procedure successfully completed.

SQL>

Then delete employee E5000. This will leave department D5000 empty:

SQL> delete from emp
  2  where empno = 'E5000';

1 row deleted.

SQL>

Finally, find the empty department using 2 different SQL statements:
 
SQL> set timing on
SQL> alter session
  2  set timed_statistics = true
  3  /

Session altered.

Elapsed: 00:00:00.05
SQL> alter session
  2  set sql_trace = true
  3  /

Session altered.

Elapsed: 00:00:00.05
SQL>

First, use NOT EXISTS. This runs in a fraction of a second:

SQL> select deptno from dept
  2  where not exists
  3  (select deptno from emp
  4  where emp.deptno = dept.deptno)
  5  /

DEPTN
-----
D5000

Elapsed: 00:00:00.09
SQL>

For the second version, use NOT IN. This takes around 7 seconds:

 
SQL> select deptno from dept
  2  where deptno not in
  3  (select deptno from emp)
  4  /

DEPTN
-----
D5000

Elapsed: 00:00:07.10
SQL> alter session
  2  set sql_trace = false
  3  /

Session altered.

Elapsed: 00:00:00.04
SQL>

By running the trace file through tkprof, you can see why NOT EXISTS is faster. It uses the index whereas NOT IN does not. As usual, click on the screen prints to display them at their original size and bring them into focus: