How to do a FULL OUTER JOIN in MySQL?
Let's understand how to do a FULL OUTER JOIN in MySQL
Problem
How to do a FULL OUTER JOIN in MySQL?
Let’s create two example tables, employees and departments, and insert some data into them.
Input
select * from employees;| employee_id | employee_name | department_id |
|---|---|---|
| 1 | John | 1 |
| 2 | Jane | 2 |
| 3 | Bob | |
| 4 | Alice | 1 |
select * from departments;| department_id | department_name |
|---|---|
| 1 | HR |
| 2 | Finance |
| 3 | Marketing |
Try Hands-On: Fiddle
Create Input Table: Gist
Desired Output
| employee_id | employee_name | emp_department_id | department_id | department_name |
|---|---|---|---|---|
| 1 | John | 1 | 1 | HR |
| 2 | Jane | 2 | 2 | Finance |
| 3 | Bob | |||
| 4 | Alice | 4 | ||
| 3 | Marketing |
Solution:
Using UNION ALL()
To perform a FULL OUTER JOIN in MySQL, you can use a combination of LEFT JOIN and UNION with a RIGHT JOIN. Here’s the SQL query to achieve this:
SELECT
e.employee_id,
e.employee_name,
e.department_id AS emp_department_id,
d.department_id,
d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT
e.employee_id,
e.employee_name,
e.department_id AS emp_department_id,
d.department_id,
d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_idartment_id IS NULL;Explanation:
This query first performs a LEFT JOIN between employees and departments, and then a RIGHT JOIN between departments and employees.
The UNION combines the results of these two joins.
Finally, the WHERE clause is used to exclude rows that have matches in both tables, effectively creating a FULL OUTER JOIN.