https://community.oracle.com/thread/2255258?start=0&tstart=0

A traditional outer join

A "normal" join finds values of two tables that are in a relation to each other. In most cases, this relation is equality (=), but it can also be all sorts of operations that either return true or false. The important thing is that a "normal" join only returns rows of both tables of which the compared columns return true.

Of course, a row whose column-value is not found in the other table's joined column is not returned at all.

However, sometimes, there is a requirment to show these rows as well.

Consider the following example. It consists of two tables l (being the left table) and r (being the right table). The left table consists of five rows, which assigns english names to the numbers 1 through 5.

create table l (
  i number primary key,
  v varchar2(20)
);
insert into l values (1, 'one'  );
insert into l values (2, 'two'  );
insert into l values (3, 'three');
insert into l values (4, 'four' );
insert into l values (5, 'five' );

The right table contains translations of these numbers in different languages ('de' for german, 'fr' for french and 'es' for spanish). However, not every number is translated, the number 4 is only translated into french (=quattre) and the number 5 only into spanish (=cinco). And more importantly, the number 1 is not translated at all.

create table r (
  i number references l,
  v varchar2(20),
  l char(2),
  primary key (i,l)
);

insert into r values (2, 'zwei','de');
insert into r values (2, 'deux','fr');
insert into r values (2, 'dos' ,'es');

insert into r values (3, 'drei','de');

insert into r values (4, 'quattre','fr');
insert into r values (4, 'cuatro','es');

insert into r values (5, 'cinco','es');

Querying the german translations

Now, we want to query the german translations of the numbers. A wrong approach would be to use a normal join:

select 
  l.v  "English", 
  r.v  "German" 
from 
  r,l
where 
  l.i = r.i and
  r.l = 'de';

This approach is wrong because not each number has a german counterpart in the right table, resulting in the following result set:

English              German
-------------------- --------------------
two                  zwei
three                drei

This might be what one want or it might not. Assuming that we want to return all numbers, even if the german translation is missing, we need an outer join. An outer join uses a (+) on the side of the operator (which in this case happens to be the equality operator) where we want to have nulls returned if no value matches:

select 
  l.v "English", 
  r.v "German"
from 
  r,l
where 
  l.i     =  r.i (+)   and
  r.l(+)  =  'de';

And this returns a row for each english word, even if there is no german translation:

English              German
-------------------- --------------------
one
two                  zwei
three                drei
four
five

The following example does more or less the same, except that it select french and german translations:

select 
  l.v            "English", 
  nvl(r.v,'--')  "Translation", 
  nvl(r.l,'--')  "Language"
from 
  l, 
  (select 
    i,v,l from r 
   where 
    r.l= 'de' or
    r.l= 'fr'
   ) r 
where 
l.i=r.i(+);

Here's what it returns:

English              Translation          La
-------------------- -------------------- --
one                  --                   --
two                  zwei                 de
two                  deux                 fr
three                drei                 de
four                 quattre              fr
five                 --                   --

Housekeeping

Cleaning up the mess...

drop table r;
drop table l;

A 'left right' join

create table table1 (
  key     number (1),
  value1  number (4)
);

create table table2 (
  key     number (1),
  field   char   (1),
  value2  number (4)
);

insert into table1 values (1, 1000);
insert into table1 values (2, 1000);

insert into table2 values (1, 'A',  200);
insert into table2 values (1, 'B',  300);
insert into table2 values (1, 'C',   50);
insert into table2 values (3, 'A',   60);

Doing the 'left right' select statement:

select distinct * from (
  select
    t1.key, t2.field, t1.value1, t2.value2 
  from
    table1 t1 left join table2 t2 on t1.key = t2.key
union
  select
    t1.key, t2.field, t1.value1, t2.value2 
  from
    table1 t1 right join table2 t2 on t1.key = t2.key);

This results in:

       KEY F     VALUE1     VALUE2
---------- - ---------- ----------
         1 A       1000        200
         1 B       1000        300
         1 C       1000         50
         2         1000
           A                    60

http://www.adp-gmbh.ch/ora/sql/outer_join.html