Linking multiple tables with an Oracle outer join

Question

Struggling with Oracle outer join syntax.

We have this query with inner and outer joins;

SELECT A.aa, B.bb, C.cc, D.dd
FROM
  TABLEA A, TABLEB B, TABLEC C, TABLED D
WHERE
  A.XX = B.XX AND
  B.YY = C.YY AND
  C.ZZ = D.WW (+)

The query works fine. A change is now it's possible that the link between table A and B (on XX) may not be present.

So we'd like to turn this into an outer join which returns data regardless of whether the existing joins are satisfied OR if there is no link between A and B (and the other tables).

How can you do this?

Answer 1

Say you have your tables like the following:

insert into tableA values (1);
insert into tableA values (2);
insert into tableB values ( 1, 10);
insert into tableB values ( -2, 20);
insert into tableC values ( 10, 100);
insert into tableC values ( 20, 200);
insert into tableD values ( 200);
insert into tableD values ( 999);

If I understand well, you need to use on outer join even on B and C, not only D; in the old Oracle syntax this is:

SELECT *
FROM
  TABLEA A, TABLEB B, TABLEC C, TABLED D
WHERE
  A.XX = B.XX(+) AND
  B.YY = C.YY(+) AND
  C.ZZ = D.WW (+)

And in (better) ANSI SQL:

select *
from tableA A
       left outer join
     tableB B on ( A.xx = B.xx)
       left outer join 
     tableC C on ( B.yy = C.yy)
       left outer join
     tableD D on ( C.zz = D.ww)

They both give:

        XX         XX         YY         YY         ZZ         WW
---------- ---------- ---------- ---------- ---------- ----------
         2
         1          1         10         10        100