Outer Joins

Sometimes while performing a join between two tables, we need to return all the rows from one table even when there are no corresponding rows in the other table. Consider the following two tables,

Oracle provides a special type of join to include rows from one table that don’t have matching rows from the other table. This type of join is known as Outer join.

The syntax of the outer join is a bit different from that of the inner join, because it includes a special operator called the outer join operator. The outer join operator is a plus sign enclosed in parentheses, i.e., (+). This operator is used in the join condition in the WHERE clause following a field name from the table that we wish to be considered the optional table. In our suppliers and parts example, the PART table doesn’t have information for one supplier. Therefore, we will simply add a (+) operator to the join condition on the side of the PART table. The query and the result set look as follows:

SELECT S.SUPPLIER_ID, S.NAME SUPPLIER_NAME, P.PART_NBR, P.NAME PART_NAME

FROM SUPPLIER S, PART P

 WHERE S.SUPPLIER_ID = P.SUPPLIER_ID (+);

Restrictions on outer join

•  The outer join operator can appear on only one side of an expression in the join condition.
•  If a join involves more than two tables, then one table can’t be outer joined with more than one other table in the query.An outer join condition containing the (+) operator may not use the IN operator
•  An outer join condition containing the OR operator may not be combined with another condition using the OR operator.
• A condition containing the (+) operator may not involve a subquery.