DB2 Left Outer-join Example

Scenario - Using a LEFT OUTER JOIN, we can combine the EMPLOYEE and DEPARTMENT tables to create a result showing each employee's department name, including NULL where the employee has no department.

EMPLOYEE Table

EMP_ID	EMP_NAME	DEPT_ID
101	Alice	1
102	Bob	2
103	Charlie	NULL
104	David	1
105	Eva	3

DEPARTMENT Table

DEPT_ID	DEPT_NAME
1	Human Resources
2	Finance
3	IT

Query -

SELECT EMPLOYEE.EMP_ID, 
        EMPLOYEE.EMP_NAME, 
		DEPARTMENT.DEPT_NAME
   FROM EMPLOYEE
        LEFT OUTER JOIN DEPARTMENT 
	 ON EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID;

Result Table

EMP_ID	EMP_NAME	DEPT_NAME
101	Alice	Human Resources
102	Bob	Finance
103	Charlie	NULL
104	David	Human Resources
105	Eva	IT

Pratical Example

Scenario - Suppose we want to get each employee's name and their department name. If an employee does not belong to any department, the department name should appear as NULL.

Code -

   IDENTIFICATION DIVISION.
   PROGRAM-ID. EMPLOYEE-LEFTJOIN.

   DATA DIVISION.
   WORKING-STORAGE SECTION.
   ...
   EXEC SQL 
		INCLUDE SQLCA 
   END-EXEC.

   PROCEDURE DIVISION.
	   EXEC SQL
		   SELECT EMPLOYEE.EMP_NAME, 
		          DEPARTMENT.DEPT_NAME
		     INTO :EMP-NAME, :DEPT-NAME
		     FROM EMPLOYEE
		          LEFT OUTER JOIN DEPARTMENT
		       ON EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID
	   END-EXEC.

	   IF SQLCODE = 0
		   DISPLAY 'Employee Name: ' EMP-NAME
		   DISPLAY 'Department Name: ' DEPT-NAME
	   ELSE
			IF SQLCODE = +100
				DISPLAY "Row Not Found"
			ELSE
				DISPLAY "SQL Error"
			END-IF 
	   END-IF.
		   
	   STOP RUN.

Explaining Example -

• EMP-NAME and DEPT-NAME: Host variables to store employee and department names.
• SQLCODE: Used to determine if the SQL operation was successful or if any errors occurred.
• SELECT ... INTO: Maps the result of the SQL query into COBOL host variables.
• LEFT OUTER JOIN: Joins EMPLOYEE and DEPARTMENT tables on DEPT_ID. If an employee has no department, DEPT_NAME will be NULL.
• SQLCODE = 0: Successful execution, displaying employee and department names.
• SQLCODE = 100: No matching rows found (although unlikely in this scenario due to the LEFT JOIN).
• SQLCODE < 0: Indicates an SQL error.

---