Monday, October 31, 2011

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>

No comments: