How to do a FULL OUTER JOIN in MySQL?
Let’s create two example tables, employees and departments, and insert some data into them.
select * from employees;
select * from departments;
Try Hands-On: Fiddle
Create Input Table: Gist
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;
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.