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

About this task

To join more than two tables, specify join conditions that include columns from all of the relevant tables.

Example:

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   DSN8A10.EMP
   ,      DSN8A10.PROJ
   ,      DSN8A10.DEPT 
   WHERE  EMPNO    = RESPEMP
   AND    WORKDEPT = DSN8A10.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.

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.

Example:

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

   SELECT EMPNO
   ,      LASTNAME
   ,      DEPTNAME
   ,      PROJNO
   FROM   DSN8A10.EMP INNER JOIN DSN8A10.DEPT
          ON WORKDEPT = DSN8A10.DEPT.DEPTNO
          LEFT OUTER JOIN DSN8A10.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.