SQL Exception Join

I know, for some of you gurus this is old news.

However, for me this is brand new and exciting!

The iSeries has a special join called an “exception join”. It’s awesome for those pain the butt, I want to know all the values that are in this table but not in this one, type of questions.

IBM SQL Documentation

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.

Author

Stewart Schatz

Career: Principal CNC Consultant for Syntax Systems Limited specializing Oracle JD Edwards EnterpriseOne and the technology that supports it. Side Hustle: Owner/Operator of E1Tips.com Location: Lancaster, PA USA  What I like to do: Invest in Family, Explore Technology, Lead Teams, Share Knowledge/Experience, Hunt, Hike, etc.

stewart@9rains.com

http://stewartschatz.com