Joining more than two 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:

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';

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          ------