The following full join diagram illustrates only section A and C are returned in the result of the full join.

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

If MySQL supported Full Join, the correct full join syntax to get section A and C 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
where t1.name is null or t2.name is null;

Note the difference here to Part 1 is that the syntax in Part 1 does not have the clause for WHERE condition.

Because MySQL does not support Full Join, we'll have to work out section A and C separately, then vertically append them together by using UNION or 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 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, t2.food as food2, t1.food as food1 
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 + C) by using UNION or UNION ALL

UNION or UNION ALL can be used and they make no differences because section A and C are mutually exclusive.

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
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 - 5 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 1: return both matched and unmatched rows

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