Q
Problem solve Get help with specific problems with your technologies, process and projects.

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>

This was last published in September 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

I need output like this using joins


Here A table having primary key,
In B table having A table primary key
And in C table contains A table primary key and B table primary Key.
If i use joins how to get output like this format
 
A1 B21 C7
A1 B21 C8
A1 B21 C9
A1 B22 C3
A2 B56 C2
A2 B57 --
A3 -- --
A4 B88 C5
A4 B88 C6
A4 -- C7

Means in C table not contain B table id then also i need result in the above format.
If possible Please give me answer
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close