A FULL OUTER JOIN is used to retrieve all rows from both tables, displaying rows with matching values in both tables where possible. When there's no match, the result will still show the row from the respective table, with NULL values for columns from the non-matching table. This join type is helpful for getting a comprehensive list that includes all records from both tables, regardless of matching conditions.

For example, if we want a list of all employees and departments, with NULL values for departments where an employee isn't assigned or for employees where no department is listed, a FULL OUTER JOIN is suitable.

Syntax -

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

• columns: Specifies the columns to retrieve from each table.
• table1: The first table in the FULL OUTER JOIN.
• table2: The second table in the FULL OUTER JOIN.
• ON: Defines the join condition, specifying which columns to match between the tables.
• WHERE: Optional condition to filter rows further after joining.

Examples -

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

EMPLOYEE Table

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

DEPARTMENT Table

DEPT_ID	DEPT_NAME
1	Human Resources
2	Finance
4	Marketing

Query -

SELECT EMPLOYEE.EMP_ID, 
       EMPLOYEE.EMP_NAME, 
	   DEPARTMENT.DEPT_NAME
  FROM EMPLOYEE
       FULL 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	NULL
NULL	NULL	Marketing

Pratical Example

Scenario - Suppose we want to get each employee's name and their department name. If an employee has no department, the department name should appear as NULL, and if a department has no employee, employee details should be NULL.

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
		          FULL 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 
	       ...
	   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.
• FULL OUTER JOIN: Joins EMPLOYEE and DEPARTMENT tables on DEPT_ID. If there's no match, NULL values are shown.
• SQLCODE = 0: Successful execution, displaying employee and department names.
• SQLCODE = 100: No matching rows found (unlikely in this scenario due to the FULL OUTER JOIN).
• SQLCODE < 0: Indicates an SQL error.

---