SQL Tutorial › Joins & Set Operators › FULL OUTER JOIN

SQL FULL OUTER JOIN

Q: How do I do a FULL OUTER JOIN in MySQL?

Write a LEFT JOIN and a RIGHT JOIN with the same columns and combine them with UNION. For large tables, use UNION ALL with WHERE left.id IS NULL on the right-join half.

IntermediateLesson 35 of 699 min read5 queries run on MySQL 8.4

A FULL OUTER JOIN keeps every row from both tables: matched rows are combined, and rows without a partner appear with NULLs on the other side. PostgreSQL, SQL Server and Oracle have the keyword; MySQL does not, but you can build exactly the same result from a left join, a right join and UNION.

In this lesson

What a full outer join returns
Emulate it in MySQL with UNION
Return only the unmatched rows of both sides
Compare two tables to find differences

The tables

employees · 12 rows (some columns)

emp_id	first_name	dept_id
100	Meera	NULL
101	Asha	1
102	Rahul	1
103	Priya	1
104	Karan	1
105	Vikram	2
106	Neha	2
107	Arjun	2
108	Kavya	3
109	Rohan	3
110	Sneha	4
111	Amit	1

departments · 5 rows

dept_id	dept_name	location
1	Engineering	Bengaluru
2	Sales	Mumbai
3	Marketing	Pune
4	HR	Delhi
5	Finance	Mumbai

What a FULL OUTER JOIN should return

every employee with a department, combined with it (11 rows);
Meera, who has no department, with NULL department columns;
Finance, which has no employees, with NULL employee columns.

In PostgreSQL you would write FROM employees e FULL OUTER JOIN departments d ON d.dept_id = e.dept_id. In MySQL that is a syntax error:

MySQL

SELECT e.first_name, d.dept_nameFROM employees eFULL OUTER JOIN departments d ON d.dept_id = e.dept_id;

ResultMySQL 8.4

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN departments d ON d.dept_id = e.dept_id' at line 3

Emulate it with UNION

MySQL

SELECT e.emp_id, e.first_name, d.dept_id, d.dept_nameFROM employees eLEFT JOIN departments d ON d.dept_id = e.dept_idUNIONSELECT e.emp_id, e.first_name, d.dept_id, d.dept_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idORDER BY dept_id, emp_id;

ResultMySQL 8.4

emp_id	first_name	dept_id	dept_name
100	Meera	NULL	NULL
101	Asha	1	Engineering
102	Rahul	1	Engineering
103	Priya	1	Engineering
104	Karan	1	Engineering
111	Amit	1	Engineering
105	Vikram	2	Sales
106	Neha	2	Sales
107	Arjun	2	Sales
108	Kavya	3	Marketing
109	Rohan	3	Marketing
110	Sneha	4	HR
NULL	NULL	5	Finance

13 rows in set

How this query works, step by step

The LEFT JOIN returns all 12 employees, Meera with NULL department columns.
The RIGHT JOIN returns all departments: the 11 matched employee rows plus Finance with NULL employee columns.
UNION stacks both results and removes exact duplicates, so each matched row appears once: 11 + 1 + 1 = 13 rows.
ORDER BY at the end sorts the combined result; NULL dept_id (Meera) comes first.

Watch out

Use UNION, not UNION ALL, here; otherwise every matched row would appear twice. If the tables themselves contain duplicate rows you need to keep, use the UNION ALL + anti-join version below instead.

A version that keeps genuine duplicates

MySQL

SELECT e.emp_id, e.first_name, d.dept_id, d.dept_nameFROM employees eLEFT JOIN departments d ON d.dept_id = e.dept_idUNION ALLSELECT e.emp_id, e.first_name, d.dept_id, d.dept_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idWHERE e.emp_id IS NULLORDER BY dept_id, emp_id;

ResultMySQL 8.4

emp_id	first_name	dept_id	dept_name
100	Meera	NULL	NULL
101	Asha	1	Engineering
102	Rahul	1	Engineering
103	Priya	1	Engineering
104	Karan	1	Engineering
111	Amit	1	Engineering
105	Vikram	2	Sales
106	Neha	2	Sales
107	Arjun	2	Sales
108	Kavya	3	Marketing
109	Rohan	3	Marketing
110	Sneha	4	HR
NULL	NULL	5	Finance

13 rows in set

How this query works, step by step

The first part returns every employee, matched or not.
The second part returns only departments without employees (WHERE e.emp_id IS NULL).
UNION ALL just appends them, so no duplicate check is needed: faster on large tables.

Only the unmatched rows

Rows that exist on one side only, from either table:

SQL

SELECT e.emp_id, e.first_name, d.dept_nameFROM employees eLEFT JOIN departments d ON d.dept_id = e.dept_idWHERE d.dept_id IS NULLUNION ALLSELECT e.emp_id, e.first_name, d.dept_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idWHERE e.emp_id IS NULL;

ResultMySQL 8.4

emp_id	first_name	dept_name
100	Meera	NULL
NULL	NULL	Finance

2 rows in set

Practical use: compare two tables

A full outer join is the classic way to reconcile two lists, for example the stock in the system against a warehouse count:

MySQL

CREATE TABLE warehouse_count (product_id INT PRIMARY KEY, counted INT);INSERT INTO warehouse_count VALUES (1, 120), (2, 44), (4, 12), (10, 7); SELECT p.product_id, p.stock, w.counted, 'only in system' AS issueFROM products p LEFT JOIN warehouse_count w ON w.product_id = p.product_idWHERE w.product_id IS NULL AND p.stock > 0UNION ALLSELECT w.product_id, p.stock, w.counted, 'only in warehouse'FROM products p RIGHT JOIN warehouse_count w ON w.product_id = p.product_idWHERE p.product_id IS NULLUNION ALLSELECT p.product_id, p.stock, w.counted, 'count differs'FROM products p JOIN warehouse_count w ON w.product_id = p.product_idWHERE p.stock <> w.countedORDER BY product_id;

ResultMySQL 8.4

mysql> CREATE TABLE warehouse_count (product_id INT PRIMARY KEY, counted INT)

Query OK, 0 rows affected

mysql> INSERT INTO warehouse_count VALUES (1, 120), (2, 44), (4, 12), (10, 7)

Query OK, 4 rows affected

mysql> SELECT p.product_id, p.stock, w.counted, 'only in system' AS issue

product_id	stock	counted	issue
2	45	44	count differs
5	5	NULL	only in system
6	300	NULL	only in system
7	250	NULL	only in system
8	30	NULL	only in system
9	18	NULL	only in system
10	NULL	7	only in warehouse

7 rows in set

How this query works, step by step

The first part lists in-stock products the warehouse did not count.
The second part lists counted items unknown to the system (product 10).
The third part lists products where both exist but the numbers differ (product 2: 45 vs 44).
Products 1 and 4 match exactly and are not reported.

Quick quiz

1. Does MySQL 8.4 support the FULL OUTER JOIN keyword?

2. Why use UNION rather than UNION ALL in the simple emulation?

FULL OUTER JOIN FAQ

How do I do a FULL OUTER JOIN in MySQL?

Write a LEFT JOIN and a RIGHT JOIN with the same columns and combine them with UNION. For large tables, use UNION ALL with WHERE left.id IS NULL on the right-join half.

What is the difference between FULL OUTER JOIN and UNION?

A full outer join combines columns from two tables side by side, matching rows by a key. UNION stacks the rows of two queries with the same columns on top of each other.

When do I need a full outer join?

When you must see all rows from both sides, for example to reconcile two lists and find what is missing or different on each side.