Please note the following guidelines when using Full Join.

1. Full Join and Full Outer Join are used interchangeably.
2. Generally speaking, full join is a combination of the results from left join, right join, and inner join.
3. Full Join also returns duplicates if there are duplicates in the join key.
4. In the real world, Full Join can potentially return large dataset, so properly testing your query in a Dev and Test environment is essential.
5. Result of Full Join contains duplicates, you need to find ways to remove them if duplicated rows are not wanted in your dataset.
6. MySQL does not support full join but it can be simulated by combining left join, right join, and inner join with UNION ALL clause.
7. For our MySQL query, to remove duplicates in the result, we can use UNION instead of UNION ALL.

In the Full Join diagram below, we can see three sections (A, B, C) form the result of a full join.

We'll re-use our Pizza and Pasta example from our Outer join tutorial.

If MySQL supported Full Join, the correct full join syntax to get section A, B, and C into the result set would be as below.

-- Note: the following syntax does not work in MySQL
select t1.name, t1.food as food1, t2.food as food2 
from pizza as t1
full join pasta as t2 on t1.name=t2.name;

Because MySQL does not support Full Join, we'll have to work out section A, B, and C separately, then vertically append them together by using UNION ALL.

Section A

Section A contains rows that are found only in Table1. Query below achieves this effect by using LEFT join with WHERE clause specifying that a column in Table2 is null.

select t1.name, t1.food as food1, t2.food as food2 
from pizza as t1
left join pasta as t2 on t1.name=t2.name
where t2.name is null;

Query result set - 2 rows returned:

Section B

Section B contains rows that are found in both Table1 and Table2. Query below achieves this effect by using INNER join.

select t1.name, t1.food as food1, t2.food as food2
from pizza as t1
inner join pasta as t2 on t1.name=t2.name;

Query result set - 2 rows returned:

Section C

Section C contains rows that are found only in Table2. Query below achieves this effect by using RIGHT join with WHERE clause specifying that a column in Table1 is null.

select t2.name, t1.food as food1, t2.food as food2
from pizza as t1
right join pasta as t2 on t1.name=t2.name
where t1.name is null;

Query result set - 3 rows returned:

Append vertically (A + B + C) by using UNION ALL

UNION ALL is used in the last step of Full Join simulation. We have used Union ALL because FULL JOIN keeps duplicate rows in the result if any, this is by definition of SQL FULL JOIN, not a side-effect.

select t1.name, t1.food as food1, t2.food as food2 
from pizza as t1
left join pasta as t2 on t1.name=t2.name
where t2.name is null
union all
select t1.name, t1.food as food1, t2.food as food2
from pizza as t1
inner join pasta as t2 on t1.name=t2.name
union all
select t2.name, t1.food as food1, t2.food as food2
from pizza as t1
right join pasta as t2 on t1.name=t2.name
where t1.name is null;

Query result set - 7 rows returned:

Happy Coding!

Other tutorials in this category

1. Using Inner Joins to Combine Data from Two Tables

2. Using Outer Joins to Combine Data from Two Tables

3. Another Example for Outer Joins (three tables)

4. Using Self Joins to Combine Data from the Same Table

5. SQL Set Operators - a Visual Guide to UNION, UNION ALL, MIMUS/EXCEPT, INTERSECT

6. Using UNION to Append Result Sets

7. Create working tables to demonstrate how to mimic set operators MINUS, EXCEPT, INTERSECT in MySQL

8. How to do MINUS/EXCEPT and INTERSECT in MySQL

9. How to Simulate Full Join in MySQL - Part 2: return unmatched rows only

10. How to Simulate Full Join in MySQL - Part 3: use UNION to simulate FULL JOIN

11. Use Cross Join to Combine Data in MySQL

12. Differences Between Join and Union

Back to Tutorial Index Page