Performing FULL Joins with SQL
When working with relational databases, it's common to retrieve data from multiple related tables. SQL joins allow you to combine rows from two or more tables based on a related column between them. SQL supports different types of joins, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. In this chapter, we'll focus on FULL JOIN (also known as FULL OUTER JOIN), which allows you to retrieve all records when there is a match in either table or no match at all.
What is a FULL JOIN?
A FULL JOIN (or FULL OUTER JOIN) in SQL combines the results of both a LEFT JOIN and a RIGHT JOIN. It returns all the records from both tables:
- Matching records where the join condition is satisfied.
- Unmatched records from both tables where no related record exists in the other table. In these cases, the missing side is filled with
NULLvalues.
In simpler terms, a FULL JOIN allows you to find all rows in two tables, regardless of whether or not they have corresponding matches.
Syntax of SQL FULL JOIN
The basic syntax of a FULL JOIN is:
SELECT column_list
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;
- column_list: The columns you want to retrieve.
- table1 and table2: The two tables involved in the join.
- column_name: The column in each table that is used to match records.
Example Data Setup
To demonstrate FULL JOIN, let’s create two simple tables, employees and departments, which are related by a department_id column.
employees Table
| employee_id | name | department_id |
|---|---|---|
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | NULL |
| 4 | David | 30 |
departments Table
| department_id | department_name |
|---|---|
| 10 | Sales |
| 20 | HR |
| 30 | Engineering |
| 40 | Marketing |
Basic Example of FULL JOIN
Let’s perform a FULL JOIN to retrieve all employees and departments, even if some employees don’t belong to a department or some departments don’t have employees.
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.department_id;
Explanation:
- We are selecting the employee’s name from the
employeestable and the department’s name from thedepartmentstable. - We are using
FULL JOINon thedepartment_idcolumn, which exists in both tables.
Output:
| name | department_name |
|---|---|
| Alice | Sales |
| Bob | HR |
| David | Engineering |
| NULL | Marketing |
| Charlie | NULL |
Analysis of Output:
Alice,Bob, andDavidhave matching records in both tables, so their names and department names are shown.Charliedoes not belong to any department (department_idisNULL), so the department name for Charlie isNULL.- The
Marketingdepartment has no employees, so the employee name for this department isNULL.
Deep Dive: Full Join with Complex Data
Now, let’s consider more complex data. Suppose you have a third table projects, where employees are assigned to various projects:
projects Table
| project_id | project_name | employee_id |
|---|---|---|
| 101 | Project Alpha | 1 |
| 102 | Project Beta | 2 |
| 103 | Project Gamma | NULL |
| 104 | Project Delta | 4 |
If we want to combine all the data from employees, departments, and projects, we can use multiple joins. To make sure all data from each table is returned, we can use a combination of FULL JOIN statements:
SELECT employees.name, departments.department_name, projects.project_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id
FULL JOIN projects ON employees.employee_id = projects.employee_id;
Output:
| name | department_name | project_name |
|---|---|---|
| Alice | Sales | Project Alpha |
| Bob | HR | Project Beta |
| David | Engineering | Project Delta |
| NULL | Marketing | NULL |
| Charlie | NULL | NULL |
| NULL | NULL | Project Gamma |
Analysis of Output:
Alice,Bob, andDavidhave corresponding departments and projects, so all their details are displayed.Charliedoes not belong to any department and is not assigned to a project, so the department and project fields areNULL.- The
Marketingdepartment has no employees, so the employee and project fields for this department areNULL. Project Gammais not assigned to any employee, so the employee and department fields areNULL.
Filtering Results with FULL JOIN
You can add additional filters to a FULL JOIN using the WHERE clause. For example, if you want to see only the departments that have no employees or the employees that belong to no departments, you could filter on NULL values:
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id
WHERE employees.name IS NULL OR departments.department_name IS NULL;
Output:
| name | department_name |
|---|---|
| NULL | Marketing |
| Charlie | NULL |
Analysis of Output:
- This query returns only the unmatched rows.
Charliedoes not have a department, and theMarketingdepartment does not have any employees.
Common Use Cases for FULL JOIN
Identify Unmatched Records:
FULL JOINis particularly useful when you need to identify records that exist in one table but not in another. For example, if you want to find which departments don’t have employees or which employees are not assigned to a department.Union of Two Tables:
FULL JOINacts like a union between two tables, returning all possible records from both sides. This is ideal for generating reports or for scenarios where you need to account for missing data in either table.Handling Missing Data: When merging data from multiple sources or dealing with optional relationships between tables,
FULL JOINis effective in ensuring you don’t miss any records, even if they’re incomplete.
In this chapter, we explored the concept of FULL JOIN in SQL. This type of join allows you to retrieve all records from both tables involved, regardless of whether they have matching records in the other table. It is extremely useful for scenarios where you need to capture all data, including the gaps or mismatches between two tables. Happy coding !❤️