René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

November 16, 2005: On mixing outer joins with inner joins

I create three simple tables: customers, orders and items_in_stock.
The customer table just stores customers' names along with an id: 
create table customers (
  id   number primary key,
  name varchar2(20)
);
Each customer can have orders. cust_id identifies the order's customer while id identifies the order: 
create table orders (
  id       number   primary key,
  cust_id  not null references customers
);
Finally, the ordered items are stored in items_in_stock. There are two things to note. First: each ordered item must be in stock. Second: There can be items in stock that have not been ordered. This is possible because the foreign key of items_in_stock (order_id) can be null: 
create table items_in_stock (
  order_id null references orders,
  name     varchar2(20)
);
The tables are filled with a few values. I forgo the insert statements here, rather, I simply show the tables' content.
There are three customers... 
select * from customers;

        ID NAME
---------- --------------------
         1 Frank Jones
         2 Sarah Mc Donald
         3 Joe Doe
... who have placed five orders: 
select * from orders;

        ID    CUST_ID
---------- ----------
         1          1
         2          2
         3          1
         4          2
         5          3
There are two items that haven't been ordered by anyone (order_id is null): The laptop and the screw driver. 
select * from items_in_stock;

  ORDER_ID NAME
---------- --------------------
         1 Radio
         1 Television
           Laptop
         2 Apple
         2 Pear
         2 Orange
           Screw driver
         3 Scissors
         5 Pen
         5 Ruler
Now, I want to select all items in stock along with, if the item has been ordered by someone, the customer's name: 
select i.name,
       c.name
  from           items_in_stock i 
  left outer join orders         o on i.order_id = o.id
             join customers      c on o.cust_id  = c.id;

NAME                 NAME
-------------------- --------------------
Radio                Frank Jones
Television           Frank Jones
Apple                Sarah Mc Donald
Pear                 Sarah Mc Donald
Orange               Sarah Mc Donald
Scissors             Frank Jones
Pen                  Joe Doe
Ruler                Joe Doe
However, this SQL statement does not what was intended. The problem is that the statement first outer joins items_in_stock with orders and then joins customers, thus loosing all items that cannot be connected to a costumer via an order.
So, the query is rewritten. This time, the costumers are first inner joined with orders, and then the outer join takes place. Because items_in_stock are to the right of orders (reading the SQL from left to right, so to speak), items_in_stock is right outer joined rather than left outer joined: 
select i.name,
       c.name
  from             customers      c 
  left        join orders         o on o.cust_id  = c.id
  right outer join items_in_stock i on i.order_id = o.id;
This looks now better: 
NAME                 NAME
-------------------- --------------------
Television           Frank Jones
Radio                Frank Jones
Orange               Sarah Mc Donald
Pear                 Sarah Mc Donald
Apple                Sarah Mc Donald
Scissors             Frank Jones
Ruler                Joe Doe
Pen                  Joe Doe
Screw driver
Laptop
So far so good. But to make things a bit more interesting, I want to exclude those items from the result set that have been ordered by Frank Jones. I try this by adding the where condition c.name <> 'Frank Jones': 
select i.name,
       c.name
  from             customers      c 
  left        join orders         o on o.cust_id  = c.id
  right outer join items_in_stock i on i.order_id = o.id
where
  c.name <> 'Frank Jones';
Yet, again, this does not what I intended: 
NAME                 NAME
-------------------- --------------------
Apple                Sarah Mc Donald
Pear                 Sarah Mc Donald
Orange               Sarah Mc Donald
Pen                  Joe Doe
Ruler                Joe Doe
That's because c.name is null for those rows that are not ordered by anyone. Therefore, I also have to also check for c.name is null in order to get the missing rows: 
select i.name,
       c.name
  from             customers      c 
  left        join orders         o on o.cust_id  = c.id
  right outer join items_in_stock i on i.order_id = o.id
where
  c.name <> 'Frank Jones' or c.name is null;
And yes, that looks good: 
NAME                 NAME
-------------------- --------------------
Orange               Sarah Mc Donald
Pear                 Sarah Mc Donald
Apple                Sarah Mc Donald
Ruler                Joe Doe
Pen                  Joe Doe
Screw driver
Laptop
One could be tempted to to exclude Frank Jones' items by excluding them in the on part of the join: 
select i.name,
       c.name
  from             customers      c 
  left        join orders         o on o.cust_id  = c.id and c.name <> 'Frank Jones'
  right outer join items_in_stock i on i.order_id = o.id;
And again, I get a different result set: 
NAME                 NAME
-------------------- --------------------
Orange               Sarah Mc Donald
Pear                 Sarah Mc Donald
Apple                Sarah Mc Donald
Ruler                Joe Doe
Pen                  Joe Doe
Screw driver
Laptop
Scissors
Television
Radio
Through the power of the left join, I get all items, but I loose the name Frank Jones (as requested by the on condition).

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.