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