SQL JOINS

DB2 SQL JOINS : OUTER JOIN


SQL OUTER Join gives

  • All data from PRIMARY table with matching date from SECONDARY table(s) appended to it.
  • If no matching data found in SECONDARY table(s) for join condition NULL value will be used to fill that space.


[SAMPLE DATA]
There are 3 types of OUTER JOIN

  1. LEFT OUTER JOIN or LEFT JOIN (Yes we can omit word OUTER)

    In LEFT OUTER JOIN table on left most side of JOIN keyword is consider as PRIMARY table and table(s) on right side of JOIN keyword is consider as SECONDARY table(s).

    SELECT * FROM SQLTEST1 a LEFT JOIN SQLTEST2 b ON a.keyf= b.keyf
    • ON keyword specify the join condition
    • All data from SQLTEST1(LEFT Table) with matching date from SQLTEST2(RIGHT table) appended to it.
    • If no matching data found in SQLTEST2 table for join condition NULL value will be used to fill that space.
    — From SQLTEST1 — — From SQLTEST2 —
    FILE_KEY  FILE_DOB  FILE_KEY FILE_NAME
    01 10/10/99 01 SUMIT
    02 10/10/00 02 AMIT
    03 11/11/01  -NULL- -NULL-
    06 12/12/16  -NULL- -NULL-

    When there are more than two tables in OUTER JOIN, technically the ON keyword define which table is primary to which table

    SELECT * FROM SQLTEST1 a    
    	LEFT JOIN SQLTEST2 b ON a.keyf = b.keyf                   
            LEFT JOIN SQLTEST3 c ON a.keyf = c.keyf  

    There are 2 LEFT JOINS in this query.

    1. Join 1 > Primary Table : SQLTEST1 Secondary Table : SQLTEST2 based on “ON a.keyf = b.keyf”
    2. Join 2 > Primary Table : SQLTEST1 Secondary Table : SQLTEST3 based on “ON a.keyf = c.keyf”

    So output will be

    — From SQLTEST1 — — From SQLTEST2 — — From SQLTEST3 —
    FILE_KEY FILE_DOB FILE_KEY FILE_NAME FILE_KEY FILE_LAST
    01 10/10/99 01 SUMIT 01 last
    02 10/10/00 02 AMIT -NULL- -NULL-
    03 11/11/01 -NULL- -NULL- -NULL- -NULL-
    06 12/12/16 -NULL- -NULL- 06 Gupta

    Now change last ON condition from “ON a.keyf = c.keyf” to “ON b.keyf = c.keyf”

    SELECT * FROM SQLTEST1 a    
    	LEFT JOIN SQLTEST2 b ON a.keyf = b.keyf                   
            LEFT JOIN SQLTEST3 c ON b.keyf = c.keyf  

    Again there are 2 LEFT JOINS in this query.

    1. Join 1 > Primary Table : SQLTEST1 Secondary Table : SQLTEST2 based on “ON a.keyf = b.keyf”
    2. Join 2 > Primary Table : SQLTEST2 Secondary Table : SQLTEST3 based on “ON b.keyf = c.keyf”. Now SQL will try to match table SQLTEST2(not table SQLTEST1) and table SQLTEST3.

    Now output will be changed to

    — From SQLTEST1 — — From SQLTEST2 — — From SQLTEST3 —
    FILE_KEY FILE_DOB FILE_KEY FILE_NAME FILE_KEY FILE_LAST
    01 10/10/99 01 SUMIT 01 last
    02 10/10/00 02 AMIT -NULL- -NULL-
    03 11/11/01 -NULL- -NULL- -NULL- -NULL-
    06 12/12/16 -NULL- -NULL- -NULL- -NULL-
  2. RIGHT OUTER JOIN or RIGHT JOIN

    Concept of the RIGHT OUTER JOIN is same as of LEFT OUTER JOIN. Only difference is in case of RIGHT OUTER JOIN right most table of JOIN keyword is consider as PRIMARY TABLE.

    SELECT * FROM SQLTEST1 a RIGHT JOIN SQLTEST2 b ON a.keyf= b.keyf
    • ON keyword specify the join condition
    • All data from SQLTEST2 (RIGHT Table) with matching date from SQLTEST1 (LEFT table) appended to it.
    • If no matching data found in SQLTEST1 table for join condition NULL value will be used to fill that space.
  3. FULL OUTER JOIN or FULL JOIN

    FULL JOIN is combination of LEFT JOIN and RIGHT JOIN. It will get you all records from all table based on join condition.

    SELECT * FROM SQLTEST1  a FULL JOIN SQLTEST2 b on a.keyf= b.keyf
    — From SQLTEST1 — — From SQLTEST2 —
    FILE_KEY FILE_DOB FILE_KEY FILE_NAME
    1 10-10-1999 1 SUMIT LEFT JOIN
    2 10-10-2000 2 AMIT
    3 11-11-2001 -NULL- -NULL-
    6 12-12-2016 -NULL- -NULL-
    -NULL- -NULL- 4 AMAN RIGHT JOIN
    -NULL- -NULL- 5 RAM

IBM i developer.

View Comments
There are currently no comments.