This SQL tutorial focuses on the Oracle Outer Join statement, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.
This tutorial is a part of several posts describing how to use the JOIN statement in Oracle. To read additional posts regarding this subject, please use the following links:
- Oracle Inner JOIN – Joining data items from tables, based on values common to both tables.
- Outer JOIN– Joining data items from tables, based on values common to both tables, while displaying all data from one table regardless of if there is a match on the second table.
- Oracle Self JOIN– Join a table to itself.
OUTER JOIN
While the Oracle Inner JOIN statement allows us to retrieve rows from both tables only if they match the join condition, Oracle Outer JOIN statement retrieves all rows from at least one of the tables, regardless of whether there is a match on the second table.
The illustration above shows the query result of using the Oracle Inner JOIN in order to join Suppliers and Regions tables.
Note that Tim does not appear, and neither does Asia region. The reason for this is the way those tables were compared. The comparison between these two tables was performed by using the following condition:
WHERE sup.region_id = reg.region_id
That means that as long as the values of the column Region Number in the Suppliers table are equal to the values of the column Region Number in the Regions table, the row will appear in the query result. If a row does not satisfy a join condition, the row will not appear in the query result.
- Tim does not appear because his region’s number is NULL, and NULL cannot be compared to any value.
- The Asia region does not appear because its number is 5, and this value cannot be compared to any value in the column Region Number within the Suppliers table.
To display all of the data items that are found on one table (either left or right), including values that have no comparable data on the second table, use either Oracle Left Outer Join or Oracle Right Outer Join.
Oracle Left OUTER JOIN
If the Suppliers table, as described in the illustration above, appears on the left side of the Oracle JOIN condition.
WHERE sup.supplier_id = reg.supplier_id
(left) (right)
To display all of the Suppliers who exist on the table, including those who do not belong to any region, use the Oracle Outer Join operator. This operator, a plus sign enclosed withing parentheses (+), will be placed on the side of the join condition that is deficient of information – the right side.
SELECT sup.last_name , sup.salary , reg.region_name FROM Suppliers sup, Regions reg WHERE sup.region_id = reg.region_id (+)
Joining the tables by using Left Outer Join results in displaying all of the Suppliers, including the Suppliers who do not belong to any region (5 rows in total):
Right OUTER JOIN
If the Regions table, as described in the illustration above, appears on the right side of the Oracle JOIN statement.
WHERE sup.region_id = reg.region_id
(left) (right)
To display all of the Regions that exist on the table, including those without any Suppliers, use the Oracle Outer Join operator. This operator, a plus sign enclosed withing parentheses (+), will be placed on the side of the join condition that is deficient of information – the left side.
SELECT sup.last_name , sup.salary , reg.region_name FROM Suppliers sup, Regions reg WHERE sup.region_id (+) = reg.region_id
Joining the tables by using the Right Outer JOIN results in displaying all of the Regions, including the Regions without any Suppliers (5 rows in total):
