CODEWITHSUNDEEP
sqloracle-databaseouter-join
Marcus Leon
Marcus Leon

Reputation: 56699

Linking multiple tables with an Oracle outer join

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?

Upvotes: 0

Views: 1185

Answers (1)

Aleksej
Aleksej

Reputation: 22969

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

Upvotes: 3

Related Questions