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.

Join conditions

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. For more information, see joined-table.

Inner and outer joins

Db2 supports inner joins, outer joins, which include left outer joins, right outer joins, and full outer joins, and cross joins.

Inner join

An inner join result is the cross product of the tables, but it keeps only the rows where the join condition is true. The result of T1 INNER JOIN T2 consists of their paired rows. If a join operator is not specified, INNER is the default. The order in which a LEFT OUTER JOIN or RIGHT OUTER JOIN is performed can affect the result. For more information, see Inner joins.

Outer join

An outer join result includes the rows that are produced by the inner join, plus the missing rows, depending on whether a left outer, full outer, right outer or full out join is used. For more information, see Outer joins.

Left outer join: A left outer join result includes the rows from the left table that were missing from the inner join. The result of T1 LEFT OUTER JOIN T2 consists of their paired rows and, for each unpaired row of T1, the concatenation of that row with the null row of T2. All columns derived from T2 allow null values. For more information, see Left outer join.
Right outer join: A right outer join result includes the rows from the right table that were missing from the inner join. The result of T1 RIGHT OUTER JOIN T2 consists of their paired rows and, for each unpaired row of T2, the concatenation of that row with the null row of T1. All columns derived from T1 allow null values. For more information, see Right outer join.
Full outer join: A full outer join result includes the rows from both tables that were missing from the inner join. The result of T1 FULL OUTER JOIN T2 consists of their paired rows and, for each unpaired row of T1, the concatenation of that row with the null row of T2, and for each unpaired row of T2, the concatenation of that row with the null row in T1. All columns of the result table allow null values. For more information, see Full outer join.

Cross join

A cross join result includes the cross product of the tables, where each row of the left table is combined with every row of the right table. A cross join is also known as the Cartesian product.The result of T1 CROSS JOIN T2 consists of each row of T1 paired with each row of T2. A cross join can also be specified without the CROSS JOIN syntax, by listing the two tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.

Outer join examples

The following examples use two 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.

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

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

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

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.

Begin figure description. This figure shows the ways to combine the PARTS and PRODUCTS tables using outer join functions. End figure description. — Figure 3. Outer joins of two tables. The join-condition for each join is defined on the PROD# column.

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.