Ways to join data from more than one table

When you want to see information from multiple tables, you can use a SELECT statement. SELECT statements can retrieve and join column values from two or more tables into a single row. The retrieval is based on a specified condition, typically of matching column values.

The main ingredient of a join is, typically, matching column values in rows of each table that participates in the join. The result of a join associates rows from one table with rows from another table. Depending on the type of join operation, some rows might be formed that contain column values in one table that do not match column values in another table.

A joined-table specifies an intermediate result table that is the result of either an inner join or an outer join. The table is derived by applying one of the join operators—INNER, FULL OUTER, LEFT OUTER, or RIGHT OUTER—to its operands.

DB2® supports inner joins and outer joins (left, right, and full).

DB2 supports inner joins and outer joins (left, right, and full).

Inner join
Combines each row of the left table with each row of the right table, keeping only the rows in which the join condition is true.
Outer join
Includes the rows that are produced by the inner join, plus the missing rows, depending on the type of outer join:
Left outer join
Includes the rows from the left table that were missing from the inner join.
Right outer join
Includes the rows from the right table that were missing from the inner join.
Full outer join
Includes the rows from both tables that were missing from the inner join.

The majority of examples in this topic use two example tables: the parts table (PARTS) and the products table (PRODUCTS), which consist of hardware supplies.

The following figure shows that each row in the PARTS table contains data for a single part: the part name, the part number, and the supplier of the part.

Figure 1. Example PARTS table
Begin figure description. An example PARTS table. End figure description.

The following figure shows that each row in the PRODUCTS table contains data for a single product: the product number, name, and price.

Figure 2. Example PRODUCTS table
Begin figure description. An example PRODUCTS table. End figure description.

The following figure shows the ways to combine the PARTS and PRODUCTS tables by using outer join functions. The illustration is based on a subset of columns in each table.

Figure 3. Outer joins of two tables
. Each join is on column PROD#.
Begin figure description. This figure shows the ways to combine the PARTS and PRODUCTS tables using outer join functions. End figure description.

An inner join consists of rows that are formed from the PARTS and PRODUCTS tables, based on matching the equality of column values between the PROD# column in the PARTS table and the PROD# column in the PRODUCTS table. The inner join does not contain any rows that are formed from unmatched columns when the PROD# columns are not equal.

You can specify joins in the FROM clause of a query. Data from the rows that satisfy the search conditions are joined from all the tables to form the result table.

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) to build a column of the result, that column does not have a name unless you use the AS clause in the SELECT list.