Joining more than two tables

Joins are not limited to two tables. You can join more than two tables in a single SQL statement.

Procedure

To join more than two tables:

Specify join conditions that include columns from all of the relevant tables.

Example

Example: Joining three tables
Suppose that you want a result table that shows employees who have projects that they are responsible for, their projects, and their department names. You need to join three tables to get all the information. You can use the following SELECT statement: 
SELECT EMPNO, LASTNAME, DEPTNAME, PROJNO
  FROM DSN8C10.EMP, DSN8C10.PROJ, DSN8C10.DEPT 
       WHERE EMPNO = RESPEMP
      AND WORKDEPT = DSN8C10.DEPT.DEPTNO;
The result table looks similar to the following output: 
EMPNO   LASTNAME         DEPTNAME                       PROJNO
======  =========        ===========================    ======
000010  HAAS             SPIFFY COMPUTER SERVICE DIV    AD3100
000010  HAAS             SPIFFY COMPUTER SERVICE DIV    MA2100
000020  THOMPSON         PLANNING                       PL2100
000030  KWAN             INFORMATION CENTER             IF1000
000030  KWAN             INFORMATION CENTER             IF2000
000050  GEYER            SUPPORT SERVICES               OP1000
000050  GEYER            SUPPORT SERVICES               OP2000
000060  STERN            MANUFACTURING SYSTEMS          MA2110
000070  PULASKI          ADMINISTRATION SYSTEMS         AD3110
000090  HENDERSON        OPERATIONS                     OP1010
000100  SPENSER          SOFTWARE SUPPORT               OP2010
000150  ADAMSON          MANUFACTURING SYSTEMS          MA2112
000160  PIANKA           MANUFACTURING SYSTEMS          MA2113
000220  LUTZ             MANUFACTURING SYSTEMS          MA2111
000230  JEFFERSON        ADMINISTRATION SYSTEMS         AD3111
000250  SMITH            ADMINISTRATION SYSTEMS         AD3112
000270  PEREZ            ADMINISTRATION SYSTEMS         AD3113
000320  MEHTA            SOFTWARE SUPPORT               OP2011
000330  LEE              SOFTWARE SUPPORT               OP2012
000340  GOUNOT           SOFTWARE SUPPORT               OP2013

Db2 determines the intermediate and final results of the previous query by performing the following logical steps:

  1. Join the employee and project tables on the employee number, dropping the rows with no matching employee number in the project table.
  2. Join the intermediate result table with the department table on matching department numbers.
  3. Process the select list in the final result table, leaving only four columns.
Example: Joining more than two tables by using more than one join type

When joining more than two tables, you do not have to use the same join type for every join.

To join tables by using more than one join type, specify the join types in the FROM clause.

Suppose that you want a result table that shows the following items:
  • Employees whose last name begins with 'S' or a letter that comes after 'S' in the alphabet
  • The department names for the these employees
  • Any projects that these employees are responsible for

You can use the following SELECT statement: 

SELECT EMPNO, LASTNAME, DEPTNAME, PROJNO
  FROM DSN8C10.EMP INNER JOIN DSN8C10.DEPT
       ON WORKDEPT = DSN8C10.DEPT.DEPTNO
    LEFT OUTER JOIN DSN8C10.PROJ 
       ON EMPNO = RESPEMP
  WHERE LASTNAME > 'S';

The result table looks like similar to the following output: 

EMPNO   LASTNAME         DEPTNAME                  PROJNO
======  =========        ======================    ======
000020  THOMPSON         PLANNING                  PL2100
000060  STERN            MANUFACTURING SYSTEMS     MA2110
000100  SPENSER          SOFTWARE SUPPORT          OP2010
000170  YOSHIMURA        MANUFACTURING SYSTEMS     ------
000180  SCOUTTEN         MANUFACTURING SYSTEMS     ------
000190  WALKER           MANUFACTURING SYSTEMS     ------
000250  SMITH            ADMINISTRATION SYSTEMS    AD3112
000280  SCHNEIDER        OPERATIONS                ------
000300  SMITH            OPERATIONS                ------
000310  SETRIGHT         OPERATIONS                ------
200170  YAMAMOTO         MANUFACTURING SYSTEMS     ------
200280  SCHWARTZ         OPERATIONS                ------
200310  SPRINGER         OPERATIONS                ------
200330  WONG             SOFTWARE SUPPORT          ------

Db2

determines the intermediate and final results of the previous query by performing the following logical steps:
  1. Join the employee and department tables on matching department numbers, dropping the rows where the last name begins with a letter before 'S in the alphabet'.
  2. Join the intermediate result table with the project table on the employee number, keeping the rows for which no matching employee number exists in the project table.
  3. Process the select list in the final result table, leaving only four columns.