SQL “EXCEPTION JOIN”

You can use EXCEPTION JOIN to select records in a table that do not have matching records in another table. For example:

Table DCT14 has records:

RETRN         RETDS            
   10         Adv Rec Lvl Sec  
   50         Adv APP          
   20         APP 1            
   30         APP 2            
   40         APP 3         

Table DCT16 has records:

RETRN      SEQNO      RECRD 
    10        1       ORDERH
    30        1       ABC   

SELECT * FROM dct14 a EXCEPTION JOIN dct16 b USING (retrn) returns:

RETRN         RETDS    
    50        Adv APP  
    20        APP 1    
    40        APP 3      
 

SELECT * FROM dct14 a JOIN dct16 b USING (retrn) returns:

RETRN         RETDS            
   10         Adv Rec Lvl Sec  
   30         APP 2