Even for the experienced iSeries guru, SQL's "exception join" syntax is rarely understood and even more rarely used.
Ironically, it's very similar to the (much more globally understood) "unmatched records with primary file" option in Query/400.
Consider the following two tables:
> select * from employee
EMPLOYEEID EMPLOYEENAME DEPARTMENTID
1 Greg Graffin 1
2 Dino Cazares 2
3 Burton Bell 2
4 Lee Ving 4
5 Mike Levine 4
6 Devin Townsend 6
7 Otep Shamaya 7
> select * from department
DEPARTMENTID DEPARTMENTNAME
1 HR
2 IS
3 QA
4 CR
To find employees with an invalid department:
> select
Employee.employeeName
from
Employee
exception join
Department
on
Employee.departmentID = Department.departmentID
EMPLOYEENAME
Devin Townsend
Otep Shamaya
To find departments that contain no employees:
> select
Department.departmentName,
Department.departmentID
from
Department
exception join
Employee
on
Employee.departmentID = Department.departmentID
DEPARTMENTNAME DEPARTMENTID
QA 3
The exception join is functionally equivalent to:
SELECT
Employee.employeeName
FROM
Employee
where
not exists
(select * from Department
where Employee.departmentID = Department.departmentID)
or
> select
Employee.employeeName
from
Employee
where
Employee.departmentID not in
(select departmentID from Department)
================================== MORE INFORMATION ON THIS TOPIC ==================================
The Best Web Links: tips, tutorials and more.
Ask your programming questions--or help out your peers by answering them--in our live discussion forums.
Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.
This was first published in June 2003