Multiple join types in one statement

If you want to return all the employees, their department names, and the projects they are responsible for, if any, you need to join the EMPLOYEE table, the DEPARTMENT table, and the PROJECT table to get the information. The following example shows the query and the results:

  SELECT EMPNO, LASTNAME, DEPTNAME, PROJNO
    FROM CORPDATA.EMPLOYEE INNER JOIN CORPDATA.DEPARTMENT
          ON WORKDEPT = DEPTNO
        LEFT OUTER JOIN CORPDATA.PROJECT
          ON EMPNO = RESPEMP
    WHERE LASTNAME > 'S'