Outer joins
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.
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.