FULL JOIN

The FULL JOIN is often described as combining the LEFT JOIN and RIGHT JOIN operations. It returns all records from the left and right tables. Those records not satisfying the JOIN criteria are represented by NULLS. This join can be useful in instances where it is important to understand membership in both directions. In other words, to determine which records are in the left table but not in the right table, which records are in the right table but not in the left table, and which records are in both. You may be thinking, “when will I possibly need this?” That’s a very good question. Let’s examine a real-world problem and then explore why, when using this join type to retrieve data and subsequently report on it, extra caution is required.

The data used throughout the examples on this page and captured in the two tables below can be found at livesql.oracle.com.

Employees
The employees table contains one row per employee.

Departments
The departments table contains one row per department.

Real-world Problem
Suppose you work at a company and you’ve been requested to generate a report for management with a focus on understanding each department and the employees within them. A key requirement of this report is that every department and employee are represented in the report. As a result, management will be able to review records of employees currently working in a department, records of employees not currently assigned to a department, and those departments without any employees (i.e., a vacant department). This is clearly a job for the FULL JOIN.

Below is the code required to generate this report and the corresponding result set or content of the report. For the purposes of this example, only a subset of attributes from the employees and departments tables have been used.

-- Generate report for management containing all employees and departments.
SELECT
    hr.employees.employee_id,
    hr.employees.hire_date,
    hr.employees.department_id,
    hr.departments.department_name,
    hr.employees.job_id,
    hr.employees.salary,
    hr.employees.commission_pct,
    hr.employees.manager_id
FROM
    hr.employees
    FULL JOIN
    hr.departments
        ON hr.employees.department_id = hr.departments.department_id
ORDER BY
    hr.employees.employee_id;

Notice, within the first 107 records, 106 employees are actively assigned to a department, as indicated by the department attributes being populated. Employee 178 is not currently assigned to a department. Following those 107 records, there exists 16 records representing the vacant departments (e.g., Recruiting, Treasuring, etc.). These facts are the result of the ON clause where the condition is to evaluate whether the department ID populated in the employee record can be found within the departments table and/or the department ID populated in the department record can be found within the employees table. With the output of the query, the reporting requirements have been fulfilled.

A Tale of Caution
Developing reports and/or dashboards using result sets generated, in whole or in part, by a FULL JOIN require additional attention to detail. For example, using the result set above, how many employees are there? The correct answer is 107; however, a typically safe method of computing this value will return an incorrect value of 123. See the query below where the importance of understanding the difference between COUNT(*) and COUNT(schema.table.column_name) is critical to reporting accuracy.

-- The query returns 123 but the correct number of employees is 107.
WITH report AS (
    SELECT
    hr.employees.employee_id,
    hr.employees.hire_date,
    hr.employees.department_id,
    hr.departments.department_name,
    hr.employees.job_id,
    hr.employees.salary,
    COALESCE(hr.employees.commission_pct, 0) AS commission_percentage,
    hr.employees.manager_id
FROM
    hr.employees
    FULL JOIN
    hr.departments
        ON hr.employees.department_id = hr.departments.department_id
)
SELECT
	COUNT(*) AS employee_count
FROM
	report;

Reporting and visualization tools, such as Tableau and Power BI can make the same error without additional input from the user specifying to ignore those records with unpopulated employee ID values.

The potential issues with reporting on data generated by a FULL JOIN do not only include instances where counts are computed. It’s potentially an issue when computing any type of aggregation. Imagine that all employees should have a commission percentage value populated and you’ve taken the extra step of replacing missing employee commission percentage values with zero for the users of your report. Subsequently, you or a user of your report computes an average of the commission percentage values using the query below or a popular reporting tool, such as Tableau or Power BI. The average commission percentage will be dragged down due to the rows in the report representing vacant departments. Instead of returning a value of 0.22, the value returned will be 0.06.

-- Compute the average employee commission percentage across all employees.
WITH report AS (
    SELECT
    hr.employees.employee_id,
    hr.employees.hire_date,
    hr.employees.department_id,
    hr.departments.department_name,
    hr.employees.job_id,
    hr.employees.salary,
    COALESCE(hr.employees.commission_pct, 0) AS commission_percentage,
    hr.employees.manager_id
FROM
    hr.employees
    FULL JOIN
    hr.departments
        ON hr.employees.department_id = hr.departments.department_id
)
SELECT
	ROUND(
    	AVG(report.commission_percentage),
    	2
    ) AS commission_percentage
FROM
    report;

The examples and ideas captured above are not intended to convey an anti-FULL JOIN position or stance, but serve to simply educate on things that can go wrong, resulting in inaccurate data reported, when we are not careful.

Knowledge Check
If you’d like to confirm your understanding on JOINS and other fundamental concepts, consider solving a handful of my practice problems here.