DB2 Right Outer-join Example

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

EMPLOYEE Table

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

DEPARTMENT Table

DEPT_ID	DEPT_NAME
1	Human Resources
2	Finance
3	IT

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

Result Table

EMP_ID	EMP_NAME	DEPT_NAME
101	Alice	Human Resources
104	David	Human Resources
102	Bob	Finance
NULL	NULL	IT

Pratical Example

Scenario - Suppose we want to get each department’s name along with its employees. If a department has no employees, it should show NULL for employee details.

Code -

IDENTIFICATION DIVISION.
...

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
	   RIGHT 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
   ...
   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.
• RIGHT OUTER JOIN: Joins EMPLOYEE and DEPARTMENT tables on DEPT_ID. If a department has no employees, EMP_NAME will be NULL.
• SQLCODE = 0: Successful execution, displaying employee and department names.
• SQLCODE = 100: No matching rows found (unlikely in this scenario due to the RIGHT JOIN).
• SQLCODE < 0: Indicates an SQL error.

---