DB2: Joining Data from More Than One Table

DB2 JOINS

Sometimes the information you want to see is not in a single table. To form a row of the result table, you might want to retrieve some column values from one table and some column values from another table. You can use a SELECT statement to retrieve and join column values from two or more tables into a single row.

DB2 supports these types of joins: inner join, left outer join, right outer join, and full outer join.

You can specify joins in the FROM clause of a query: Figure 8 below shows the ways to combine tables using outer join functions.

The result table contains data 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. But, if you use a function (such as COALESCE or VALUE) to build a column of the result, then that column does not have a name unless you use the AS clause in the SELECT list.

To distinguish the different types of joins, the examples in this section use the following two tables:

The PARTS table             The PRODUCTS table

PART    PROD# SUPPLIER      PROD# PRODUCT     PRICE

======= ===== ============  ===== =========== =====

WIRE    10    ACWF          505   SCREWDRIVER  3.70

OIL     160   WESTERN_CHEM  30    RELAY        7.55

MAGNETS 10    BATEMAN       205   SAW         18.90

PLASTIC 30    PLASTIK_CORP  10    GENERATOR   45.75

BLADES  205   ACE_STEEL

Inner Join

You can request an inner join in two ways. You can join the example tables on the PROD# column to get a table of parts with their suppliers and the products that use the parts.

Either one of these examples:

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT

FROM PARTS, PRODUCTS

WHERE PARTS.PROD# = PRODUCTS.PROD#;

or

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT

FROM PARTS INNER JOIN PRODUCTS

ON PARTS.PROD# = PRODUCTS.PROD#;

gives this result:

PART    SUPPLIER      PROD# PRODUCT

======= ============  ===== ==========

WIRE     ACWF         10    GENERATOR

MAGNETS  BATEMAN      10    GENERATOR

PLASTIC  PLASTIK_CORP 30    RELAY

BLADES   ACE_STEEL    205   SAW

Notice two things about the example:

There is a part in the parts table (OIL) whose product (#160) is not in the products table. There is a product (SCREWDRIVER, #505) that has no parts listed in the parts table. Neither OIL nor SCREWDRIVER appears in the result of the join.

An outer join, however, includes rows where the values in the joined columns do not match.

There is an explicit syntax to express that this join is not an outer join but an inner join. You can use INNER JOIN in the FROM clause instead of the comma. Use ON to specify the join condition (rather than WHERE) when you explicitly join tables in the FROM clause.

If you do not specify a condition in the WHERE or ON clauses, the result table contains all possible combinations of rows for the tables identified in the FROM clause. If this happens, the number of rows in the result table is the product of the number of rows in each table.

Full Outer Join

With the same PARTS and PRODUCTS tables, this example:

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT

FROM PARTS FULL OUTER JOIN PRODUCTS

ON PARTS.PROD# = PRODUCTS.PROD#;

gives this result:

PART    SUPPLIER     PROD# PRODUCT

======= ============ ===== ==========

WIRE    ACWF         10    GENERATOR

MAGNETS BATEMAN      10    GENERATOR

PLASTIC PLASTIK_CORP 30    RELAY

BLADES  ACE_STEEL    205   SAW

OIL     WESTERN_CHEM 160   -----------

------- ------------ ---   SCREWDRIVER

The clause FULL OUTER JOIN includes unmatched rows from both tables. Missing values in a row of the result table contain nulls.

Left Outer Join

With the same PARTS and PRODUCTS tables, this example:

SELECT PART, SUPPLIER, PARTS.PROD#, PRODUCT

FROM PARTS LEFT OUTER JOIN PRODUCTS

ON PARTS.PROD# = PRODUCTS.PROD#;

gives this result:

PART    SUPPLIER     PROD# PRODUCT

======= ============ ===== ==========

WIRE    ACWF         10    GENERATOR

MAGNETS BATEMAN      10    GENERATOR

PLASTIC PLASTIK_CORP 30    RELAY

BLADES  ACE_STEEL    205   SAW

OIL     WESTERN_CHEM 160   -----------

The clause LEFT OUTER JOIN includes rows from the table named before it where the values in the joined columns are not matched by values in the joined columns of the table named after it.

Right Outer Join

With the same PARTS and PRODUCTS tables, this example:

SELECT PART, SUPPLIER, PRODUCTS.PROD#, PRODUCT

FROM PARTS RIGHT OUTER JOIN PRODUCTS

ON PARTS.PROD# = PRODUCTS.PROD#;

gives this result:

PART    SUPPLIER     PROD# PRODUCT

======= ============ ===== ===========

WIRE    ACWF         10    GENERATOR

MAGNETS BATEMAN      10    GENERATOR

PLASTIC PLASTIK_CORP 30    RELAY

BLADES  ACE_STEEL    205   SAW

------- ------------ 505   SCREWDRIVER

The clause RIGHT OUTER JOIN includes rows from the table named after it where the values in the joined columns are not matched by values in the joined columns of the table named before it.