LEFT OUTER JOIN on more than two tables

Is it possible to perform LEFT OUTER JOIN in more than two tables? If possible, please give me details.

Is it possible to perform LEFT OUTER JOIN in more than two tables? If possible, please give me details.

Yes, it is possible.

Suppose we have three tables, tableA, tableB, and tableC. Each A has zero or more B's, and each B has zero or more C's. Like a hierarchy. We could also say each C belongs to only one B, and each B belongs to only one A. So a typical instance of the database might be:

A1 
  B21 
     C7
     C8
     C9
  B22
     C3
A2 
  B56 
     C2
  B57 
A3 
A4 
  B88 
     C5
     C6

We would use a query with two LEFT OUTER JOINs to retrieve the hierarchy. The relationships were "zero or more" and it's the zero that tips us off to the need for an OUTER join.

select A, B, C
  from tableA
left outer
  join tableB
    on tableA.id = tableB.Aid  
left outer
  join tableC
    on tableB.id = tableC.Bid  

Because it is an OUTER join, this query will return a result set with nulls in the columns of any table for which no matching row was found:

A1 B21 C7
A1 B21 C8
A1 B21 C9
A1 B22 C3
A2 B56 C2
A2 B57 --
A3 --  --
A4 B88 C5
A4 B88 C6

Some people think that the result set they get back from this query contains "duplicates," and may need time to understand why it appears that way but actually isn't. <grin>

Dig Deeper on Oracle development languages

Data Management
Business Analytics
SearchSAP
TheServerSide.com
Data Center
Content Management
HRSoftware
Close