Books on DB2 and SQL DB2 and SQL Manuals Examples of Joins. DB2/SQL Version 7 and later This shows the difference between a normal join, a LEFT OUTER JOIN, a RIGHT OUTER JOIN, and a FULL OUTER JOIN. Take a look at the data in the EMP and STAFF tables first. SELECT NAME, LASTNAME FROM EMP JOIN STAFF ON NAME = LASTNAME ; -- shows matching columns ---------+---------+------ NAME LASTNAME JONES JONES SCOUTTEN SCOUTTEN SMITH SMITH SMITH SMITH ---------+---------+------ SELECT NAME, LASTNAME FROM STAFF LEFT OUTER JOIN EMP ON NAME = LASTNAME ; --shows matching columns, plus no-matches as nulls. No-matches means STAFF.NAME not matching EMP.LASTNAME ---------+---------+---------+--- NAME LASTNAME ABRAHAMS --------------- BURKE --------------- DANIELS --------------- DAVIS --------------- EDWARDS --------------- FRAYE --------------- GAFNEY --------------- GONZALES --------------- GRAHAM --------------- HANES --------------- JAMES --------------- JONES JONES KERMISCH --------------- KOONITZ --------------- LEA --------------- LU --------------- LUNDQUIST --------------- MARENGHI --------------- MOLINARE --------------- NAUGHTON --------------- NGAN --------------- O'BRIEN --------------- PERNAL --------------- PLOTZ --------------- QUIGLEY --------------- QUILL --------------- ROTHMAN --------------- SANDERS --------------- SCOUTTEN SCOUTTEN SMITH SMITH SMITH SMITH SNEIDER --------------- WHEELER --------------- WILLIAMS --------------- WILSON --------------- YAMAGUCHI ---------------  ---------+---------+------ SELECT NAME, LASTNAME FROM STAFF RIGHT OUTER JOIN EMP ON NAME = LASTNAME ; --shows matching columns, plus no-matches as nulls. -- same as LEFT but switches left and right ---------+---------+- NAME LASTNAME --------- ADAMSON --------- BROWN --------- GEYER --------- GOUNOT --------- HAAS --------- HENDERSON --------- JEFFERSON --------- JOHNSON JONES JONES --------- KWAN --------- LEE --------- LUCCHESI --------- LUTZ --------- MARINO --------- MEHTA --------- NICHOLLS --------- O'CONNELL --------- PARKER --------- PEREZ --------- PIANKA --------- PULASKI --------- QUINTANA --------- SCHNEIDER SCOUTTEN SCOUTTEN --------- SETRIGHT SMITH SMITH SMITH SMITH --------- SPENSER --------- STERN --------- THOMPSON --------- WALKER --------- YOSHIMURA  ---------+---------+------ SELECT NAME, LASTNAME FROM STAFF FULL OUTER JOIN EMP ON NAME = LASTNAME ; --shows matching columns, plus no-matches as nulls. -- combines the LEFT and RIGHT ---------+---------+---------+- NAME LASTNAME ABRAHAMS --------------- --------- ADAMSON --------- BROWN BURKE --------------- DANIELS --------------- DAVIS --------------- EDWARDS --------------- FRAYE --------------- GAFNEY --------------- --------- GEYER GONZALES --------------- --------- GOUNOT GRAHAM --------------- --------- HAAS HANES --------------- --------- HENDERSON JAMES --------------- --------- JEFFERSON --------- JOHNSON JONES JONES KERMISCH --------------- KOONITZ --------------- --------- KWAN LEA --------------- --------- LEE LU --------------- --------- LUCCHESI LUNDQUIST --------------- --------- LUTZ MARENGHI --------------- --------- MARINO --------- MEHTA MOLINARE --------------- NAUGHTON --------------- NGAN --------------- --------- NICHOLLS O'BRIEN --------------- --------- O'CONNELL --------- PARKER --------- PEREZ PERNAL --------------- --------- PIANKA PLOTZ --------------- --------- PULASKI QUIGLEY --------------- QUILL --------------- --------- QUINTANA ROTHMAN --------------- SANDERS --------------- --------- SCHNEIDER SCOUTTEN SCOUTTEN --------- SETRIGHT SMITH SMITH SMITH SMITH SNEIDER --------------- --------- SPENSER --------- STERN --------- THOMPSON NAME LASTNAME ---------+---------+-------- --------- WALKER WHEELER --------------- WILLIAMS --------------- WILSON --------------- YAMAGUCHI --------------- --------- YOSHIMURA Top of Page

List of books on JCL and other mainframe topics