Exception join

A left exception join returns only the rows from the first table that do not have a match in the second table.

Using the same tables as before, return those employees that are not responsible for any projects.

  SELECT EMPNO, LASTNAME, PROJNO
    FROM CORPDATA.EMPLOYEE EXCEPTION JOIN CORPDATA.PROJECT
          ON EMPNO = RESPEMP
    WHERE LASTNAME > 'S'

This join returns the following output.

EMPNO	LASTNAME	PROJNO
000170	YOSHIMURA	-
000180	SCOUTTEN	-
000190	WALKER	-
000280	SCHNEIDER	-
000300	SMITH	-
000310	SETRIGHT	-
200170	YAMAMOTO	-
200280	SCHWARTZ	-
200310	SPRINGER	-
200330	WONG	-

An exception join can also be written as a subquery using the NOT EXISTS predicate. The previous query can be rewritten in the following way:

  SELECT EMPNO, LASTNAME
    FROM CORPDATA.EMPLOYEE
    WHERE LASTNAME > 'S'
      AND NOT EXISTS
         (SELECT * FROM CORPDATA.PROJECT
            WHERE EMPNO = RESPEMP)

The only difference in this query is that it cannot return values from the PROJECT table.

There is a right exception join, too, that works just like a left exception join but with the tables reversed.