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.

 

Parent topic:

Joining data from more than one table