Oracle Precedence

This is a hypothetical example and, contrary to what I say below, I have nothing against French or German cars. I wanted to buy a new car so I made up a shortlist of manufacturers and put them in a table:

SQL> create table short_list
  2  (manufacturer varchar2(12))
  3  /

Table created.

SQL> insert into short_list values ('BMW')
  2  /

1 row created.

SQL> insert into short_list values ('Renault')
  2  /

1 row created.

SQL> insert into short_list values ('Subaru')
  2  /

1 row created.

SQL>

Then I decided I did not want to buy a French car so I made a list of French manufacturers and put them in a second table: 
 
SQL> create table french
  2  (manufacturer varchar2(12))
  3  /

Table created.

SQL> insert into french values ('Citroen')
  2  /

1 row created.

SQL> insert into french values ('Renault')
  2  /

1 row created.

SQL>

Then I decided I did not want to buy a German car either so I made a list of German manufacturers and put them in a third table:

SQL> create table german
  2  (manufacturer varchar2(12))
  3  /

Table created.

SQL> insert into german values ('BMW')
  2  /

1 row created.

SQL> insert into german values ('Mercedes')
  2  /

1 row created.

SQL>

Then I decided to see which manufacturers were still in my short list. My first attempt did not produce the result I expected. I think Oracle evaluated the not in before the union so the German manufacturers were still in the list:

SQL> select manufacturer from short_list
  2  where manufacturer not in
  3  (select manufacturer from french)
  4  union
  5  (select manufacturer from german)
  6  /

MANUFACTURER
------------
BMW
Mercedes
Subaru
 
SQL>

Swapping the select statements before and after the union confirmed that the problem was caused by Oracle's precedence rules. This time, the French manufacturers were still in the list:

SQL> select manufacturer from short_list
  2  where manufacturer not in
  3  (select manufacturer from german)
  4  union
  5  (select manufacturer from french)
  6  /

MANUFACTURER
------------
Citroen
Renault
Subaru

SQL>

To get the correct result I had to surround the select statements before and after the union with an extra set of brackets. This told Oracle to evaluate the union first:

SQL> select manufacturer from short_list
  2  where manufacturer not in
  3  ((select manufacturer from french)
  4    union
  5  (select manufacturer from german))
  6  /

MANUFACTURER
------------
Subaru

SQL>

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: