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 and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading