SQL FULL OUTER JOIN

---

In SQL, the FULL OUTER JOIN is one of the most versatile join operations. It combines the results of both the LEFT JOIN and RIGHT JOIN to return all records from both the left and right tables. If there’s no match between the two tables, it fills the result with NULL values for the missing side.

---

1. What is SQL FULL OUTER JOIN?

A FULL OUTER JOIN returns all rows when there is a match in either the left or right table. It combines the results of both LEFT JOIN and RIGHT JOIN.

• If there is a match between the left and right tables, the result will contain data from both.
• If there is no match, the result will contain NULL for the missing side.

In simpler terms:

• All rows from the left table and all rows from the right table are included.
• If a record in the left table has no matching record in the right table, the result will show NULL for the right table’s columns, and vice versa.

---

2. Syntax of SQL FULL OUTER JOIN

The syntax of the FULL OUTER JOIN is similar to other joins:

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

• SELECT: Specifies the columns to retrieve.
• FROM table1: Specifies the left table (the first table in the query).
• FULL OUTER JOIN table2: Specifies the right table (the second table).
• ON table1.column = table2.column: The condition that defines how the two tables are related.

---

3. How FULL OUTER JOIN Works

Let’s understand how a FULL OUTER JOIN works with a real example. We’ll use two tables: employees and departments.

-- employees table
+-------------+-----------------+---------------+
| employee_id | employee_name   | department_id |
+-------------+-----------------+---------------+
| 1           | Alice           | 1             |
| 2           | Bob             | 2             |
| 3           | Charlie         | NULL          |
+-------------+-----------------+---------------+

-- departments table
+---------------+--------------------+
| department_id | department_name    |
+---------------+--------------------+
| 1             | HR                 |
| 2             | Finance            |
| 3             | Engineering        |
+---------------+--------------------+

FULL OUTER JOIN Example:

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id;

Explanation:

• This query retrieves the employee_id, employee_name, and department_name.
• It performs a FULL OUTER JOIN between the employees table and the departments table using the department_id column.
• All employees and departments will be listed, and where there is no match, the missing side will show NULL.

Result:

employee_id	employee_name	department_name
1	Alice	HR
2	Bob	Finance
3	Charlie	NULL
NULL	NULL	Engineering

Explanation of Result:

• Alice and Bob are assigned to departments, so their employee_name and department_name values are displayed.
• Charlie does not belong to a department, so the department_name is NULL.
• Engineering is a department without an employee, so employee_id and employee_name are NULL.

---

4. Using FULL OUTER JOIN to Filter Data

You can also use a FULL OUTER JOIN to filter specific data. For example, let’s say we want to find out which departments do not have any employees and which employees are not assigned to any department.

Example: Finding Employees Without a Department

SELECT employees.employee_id, employees.employee_name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id
WHERE departments.department_name IS NULL;

Explanation:

• This query retrieves employees who are not assigned to any department by checking for NULL in the department_name column.
• The WHERE clause filters out rows where a department is assigned to the employee.

Result:

employee_id	employee_name
3	Charlie

---

Example: Finding Departments Without Employees

SELECT departments.department_name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id
WHERE employees.employee_name IS NULL;

Explanation:

• This query retrieves departments that do not have any employees assigned to them by checking for NULL in the employee_name column.
• The WHERE clause filters out rows where an employee is assigned to the department.

Result:

department_name
Engineering

---

5. FULL OUTER JOIN vs. LEFT JOIN and RIGHT JOIN

You might wonder how a FULL OUTER JOIN differs from LEFT JOIN and RIGHT JOIN.

• LEFT JOIN: Returns all records from the left table, and the matching rows from the right table. If there’s no match, NULL is returned for columns from the right table.
• RIGHT JOIN: Returns all records from the right table, and the matching rows from the left table. If there’s no match, NULL is returned for columns from the left table.
• FULL OUTER JOIN: Returns all records from both tables. If there is no match, NULL is returned for the missing side.

Example of LEFT JOIN and RIGHT JOIN:

Let’s see how LEFT JOIN and RIGHT JOIN compare:

• LEFT JOIN:

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

Result for LEFT JOIN:

employee_id	employee_name	department_name
1	Alice	HR
2	Bob	Finance
3	Charlie	NULL

• RIGHT JOIN:

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

Result for RIGHT JOIN:

employee_id	employee_name	department_name
1	Alice	HR
2	Bob	Finance
NULL	NULL	Engineering

Key Differences:

• The LEFT JOIN returns all employees and the matching departments.
• The RIGHT JOIN returns all departments and the matching employees.
• The FULL OUTER JOIN combines both results, showing all employees and all departments, with NULL where there are no matches.

---

---