SQL EXCEPTION JOIN in DB2 for i SQL

SQL EXCEPTION JOIN in DB2 for i SQL

EXCEPTION JOIN

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

Syntax

SELECT column_name(s)
FROM table1
EXCEPTION JOIN table2
ON table1.column_name = table2.column_name

Reference Tables for understanding EXCEPTION JOIN

Student Table

Course Table

Example using SQL EXCEPTION JOIN

Perform EXCEPTION JOIN on Table STUDENT and COURSE based on field ROLLNO

SELECT S.ROLLNO, S.NAME, S.GENDER, C.COURSEID, C.COURSENAME FROM 
student s 
EXCEPTION JOIN course c ON s.rollno = c.rollno        

Result:

ROLLNO   NAME                  GENDER  COURSEID  COURSENAME
     3   HEMANT                  M        -      -         
     4   ANNAMALAI               M        -      -         
     5   KESHAV                  M        -      -         

RollNo 3, 4, 5 are not present in table2 i.e. COURSE table w.r.t the first table STUDENT and is returned and displayed. However, there is no course id assigned to roll no 3, 4, 5 in COURSE table so their value is returned as NULL.

EXCEPTION JOIN using NOT EXISTS

An Exception join can also be written as a subquery using the NOT EXISTS predicate. The only difference in this query is that it cannot return values from thesecond table i.e. COURSE table.

SELECT S.ROLLNO, S.NAME, S.GENDER  FROM             
student s  where NOT EXISTS (                       
SELECT * FROm course c where c.rollno = s.rollno)   

Result:

ROLLNO   NAME                  GENDER
     3   HEMANT                  M   
     4   ANNAMALAI               M   
     5   KESHAV                  M