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.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functions – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to concatenate text from multiple rows into a single text string in MySQL?
2. How to get the rows which have the max value for a column for each group in another column in SQL?
3. How to select first row in each GROUP BY group?