An outer join is a method of combining two or more tables so that the result includes unmatched rows of one of the tables, or of both tables.
The matching is based on the join condition.

DB2® supports three types of outer joins:

full outer join

Includes unmatched rows from both tables.
If any column of the result table does not have a value, that column has the null value in the result table.

left outer join

Includes rows from the table that is specified before LEFT OUTER JOIN that have no matching values in the table that is specified after LEFT OUTER JOIN.

right outer join

Includes rows from the table that is specified after RIGHT OUTER JOIN that have no matching values in the table that is specified before RIGHT OUTER JOIN.

The following table illustrates how the PARTS and PRODUCTS tables in Sample data for joins can be combined using the three outer join functions.

Figure 1. Three outer joins from the PARTS and PRODUCTS tables

The result table contains data that is joined from all of the tables, for rows that satisfy the search conditions.

The result columns of a join have names if the outermost SELECT list refers to base columns.
However, if you use a function (such as COALESCE or VALUE) to build a column of the result, that column does not have a name unless you use the AS clause in the SELECT list.