[USflag] The American Programmer [USflag]


Home
Books on Mainframe Programming
Mainframe Manuals and Tutorials
System Abend codes, Sqlcodes, VSAM/QSAM codes
Everything about the IBM AS/400 Midrange Computer
Everything about CICS
Everything about COBOL
Everything about DB2 and SQL
  DB2/SQL Singleton Select embedded in a COBOL program
  DB2/SQL Add Change Delete program
  DB2/SQL program to read table with cursor
  DB2/SQL load table program
  DB2/SQL program to read a table randomly
  DB2/SQL bare bones skeleton program
  DB2/SQL program to insert a row into a table
  DB2/SQL program to update a row
  DB2/SQL program for handling variable length column
  DB2/SQL program for handling column with null indicator
  Embedded SQL. Short tutorial.
  Examples of DCLGEN. Short tutorial.
  Examples of SQL Joins. Short tutorial.
  Examples of DB2 View. Short tutorial.
  Just Enough SQL/QMF/SPUFI To Be Dangerous
  Sample tables STAFF, ORG, EMP and DEPT
  QMF Procedures Written in REXX
  Using REXX Subroutines with the QMF Calc panel
  Using QMF to Execute Your SQL
  QMF Cheat Sheet. Edit codes. QMF Commands, Text Variables, Usage Codes
  Executing SQL in a batch job
  Using Spufi to Execute Your SQL
  Books on DB2 and SQL
  DB2 and SQL, Structured Query Language manuals
  SQLCODES and Their Causes
Everything about IMS
Everything about Java and JavaScript
Everything about JCL and JES
Everything about REXX
Everything about zOS, VSAM, Tivoli, Assembler
Everything about TSO, ISPF, Spufi
Site Map and Site Search

           Home   > DB2   > Examples of DB2/SQL Joins

A join combines fields in two or more tables by matching values. If a specified value in one table matches the corresponding value in another table, each row is combined and selected.
With an OUTER join, there does not have to be a match. Non-matching values are shown as a NULL.

Description of the example tables STAFF, ORG, EMP and DEPT

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 DB2 and other mainframe topics

[Books Computer]

Home Books for Computer Professionals Privacy Terms |
Site Map and Site Search Programming Manuals and Tutorials The REXX Files Top of Page |