SQL JOINS

DB2 SQL JOINS : EXCEPTION JOIN


Here is how SQL EXCEPTION JOIN works.

  • It returns rows from PRIMARY table that don’t have a match in any of SECONDARY table.
  • Columns that come from SECONDARY┬átable(s) will always have null value.


[SAMPLE DATA]
DB2 SQL supports 2 types of EXCEPTION JOINs:

  1. Left Exception Join

    • Table on left side of JOIN keyword will be considered as PRIMARY table
    • Table(s) on right side of JOIN keyword will be considered as SECONDARY table.
    SELECT* FROM SQLTEST1  a LEFT EXCEPTION JOIN SQLTEST2 b on a.keyf= b.keyf 

    In this case:

    • SQLTEST1 is PRIMARY table.
    • SQLTEST2 is SECONDARY table.
    — From SQLTEST1 — — From SQLTEST2 —
    FILE_KEY FILE_DOB FILE_KEY FILE_NAME
    03 11/11/01 -NULL- -NULL-
    06 12/12/16 -NULL- -NULL-
  2. Right Exception Join

    • Table on right side of JOIN keyword will be considered as PRIMARY table.
    • Table(s) on left side of JOIN keyword will be considered as SECONDARY table.
    SELECT* FROM SQLTEST1  a RIGHT EXCEPTION JOIN SQLTEST2 b on a.keyf= b.keyf 

    In this case:

    • SQLTEST2 is PRIMARY table.
    • SQLTEST1 is SECONDARY table.

IBM i developer.

View Comments
There are currently no comments.