The JOIN statement is an SQL statement used to combine rows from two or more tables based on related columns. This is important in relational databases, where data is spread across different tables. By using JOINs, we can retrieve data across tables in a single query.

In COBOL programs, JOINs allow for consolidated data retrieval directly within the mainframe application. For instance, we might join an EMPLOYEE table and a DEPARTMENT table to get employee details along with their department name.

Syntax -

SELECT columns
FROM table1
JOIN_TYPE table2 ON table1.column = table2.column
WHERE condition;

• columns: Specifies which columns to retrieve from each table.
• table1: The first table in the JOIN operation.
• table2: The second table in the JOIN operation.
• JOIN_TYPE: Specifies the type of join (INNER JOIN, LEFT JOIN, etc.).
• ON: Defines the join condition, linking related columns between tables.
• WHERE: An optional clause to filter rows further after joining.

Examples - Basic JOIN Statement

SELECT EMPLOYEE.EMP_NAME, 
       DEPARTMENT.DEPT_NAME
  FROM EMPLOYEE
       INNER JOIN DEPARTMENT 
    ON EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID;

• Joins EMPLOYEE and DEPARTMENT tables.
• Retrieves EMP_NAME from EMPLOYEE and DEPT_NAME from DEPARTMENT.
• The join condition ON EMPLOYEE.DEPT_ID = DEPARTMENT.DEPT_ID links both tables by the DEPT_ID column.

Types of JOIN Statements

INNER JOIN:

An INNER JOIN retrieves only rows that have matching values in both tables. Example -

SELECT columns
  FROM table1
       INNER JOIN table2 
  ON table1.column = table2.column;

LEFT JOIN (LEFT OUTER JOIN):

A LEFT JOIN retrieves all rows from the left table, and matching rows from the right table. If there is no match, columns from the right table will show NULL. Example -

SELECT columns
  FROM table1
       LEFT JOIN table2 
	ON table1.column = table2.column;

RIGHT JOIN (RIGHT OUTER JOIN):

A RIGHT JOIN retrieves all rows from the right table, and matching rows from the left table. Columns from the left table will be NULL if there's no match. Example -

SELECT columns
	FROM table1
		 RIGHT JOIN table2 
	  ON table1.column = table2.column;

FULL JOIN (FULL OUTER JOIN):

A FULL JOIN retrieves all rows from both tables, and NULL is returned for columns that don't match in either table. Example -

SELECT columns
   FROM table1
        FULL JOIN table2 
	 ON table1.column = table2.column;

Using JOIN Statements in a COBOL Program

In COBOL programs with embedded SQL (DB2), the JOIN statement is written inside EXEC SQL ... END-EXEC. The query result is stored in host variables defined in COBOL.

Examples - Embedding a JOIN Statement in COBOL

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.

• EXEC SQL and END-EXEC enclose the SQL command in COBOL.
• SELECT ... INTO: Maps the result columns to COBOL host variables :EMP-NAME, :DEPT-NAME.

Steps to Code a JOIN Statement in a COBOL Program

1. Declare Host Variables in the WORKING-STORAGE Section: Define host variables to store data from the JOIN result set, matching the data types of the columns in the JOIN query.
2. Write the Embedded SQL JOIN Statement: Embed the JOIN statement with the required conditions in the PROCEDURE DIVISION.
3. Check for SQL Errors: After executing the SQL statement, check SQLCODE in the SQLCA to determine if the operation was successful.

Error Handling for the JOIN Statement

DB2 provides SQLCODE and SQLSTATE values to indicate SQL operation results. The SQLCA (SQL Communication Area) holds this information, allowing for error management in COBOL programs.

Common SQLCODE Values for JOIN:

• 0: Successful execution.
• 100: No rows found (useful when no matching rows exist).
• Negative Values: Represent different SQL errors (e.g., -805 for package not found, -530 for referential integrity constraint violation).

Examples -

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 = 100
	DISPLAY 'No matching data found for the join'
ELSE 
	IF SQLCODE < 0
		DISPLAY 'SQL Error: ' SQLCODE
	END-IF 
END-IF.

• SQLCODE = 100: Indicates no rows matched the JOIN condition.
• SQLCODE < 0: Catches other SQL errors, displaying the SQLCODE for troubleshooting. The STOP RUN halts the program for critical errors.

---