DB2 Inner Join Example

Scenario - Using an INNER JOIN, we can combine the EMPLOYEE and DEPARTMENT tables to create a result showing each employee's department name.

EMPLOYEE Table

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

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
        INNER 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	IT
104	David	Human Resources
105	Eva	Finance

Pratical Example

Scenario - Assuming the EMPLOYEE table contains employee information, including EMP_NAME and DEPT_ID, and the DEPARTMENT table contains department names with corresponding DEPT_ID, the goal is to retrieve each employee's name along with their department name.

Code -

       IDENTIFICATION 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
                      INNER JOIN DEPARTMENT
                   ON EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID
           END-EXEC.

           IF SQLCODE = 0
               ....
		   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 the employee and department names.
• SQLCODE: Used to check the result of the SQL operation.
• SELECT ... INTO: Maps the result of the SQL query into COBOL host variables.
• INNER JOIN: Joins EMPLOYEE and DEPARTMENT tables on DEPT_ID, retrieving rows with matching department IDs.
• SQLCODE = 0: Successful retrieval, displaying the employee and department names.
• SQLCODE = 100: Indicates no matching rows were found.
• SQLCODE < 0: Indicates an SQL error.

---