How to do a FULL OUTER JOIN in MySQL- Is it Supported?

Surely, MySQL supports full outer joins… right? Right?

Well, the answer is not as straightforward as it should be. It is yes… but only kind of.

What is a Full Outer Join?

There are a few joins that are ubiquitous to all SQL dialects. These include left joins, right joins, inner joins, and outer joins with the most commonly used join being left or inner joins. Hopefully, you already know what these common joins are.

But what is a full outer join?

A full outer join refers to joining two tables or sets of data where everything is included in the resulting data set regardless if there was a match on the joining variable(s) or not. In simple terms, a full outer join means that no data is lost during the join. This differs from what might occur in a left join, right join, or inner join.

In all honesty, full outer joins are not often used in practice unless you are working with a rather complex database or complex query logic. However, when the time comes to use them, it is nice to know what they are and how to use them.

If you’re new to SQL and want to learn a bit more, check out my beginner’s guide to foreign keys with MySQL here.

Is a Full Outer Join Supported with MySQL?

This seems like a boneheaded question to ask, but surprisingly, MySQL doesn’t actually have a native operator for a FULL OUTER JOIN.

MySQL doesn’t natively support a FULL OUTER JOIN, however, you can simulate one by using the UNION operator, a LEFT JOIN, and a RIGHT JOIN. Effectively, this means you will write the same join twice but the LEFT JOIN loses data from the right table and the RIGHT JOIN will lose data from the left table. The UNION operator combines these two datasets and removes any duplicates from the dataset, thus simulating a full outer join.

Also note that a FULL OUTER JOIN and an OUTER JOIN are the same things, but with slightly differing terminology.

How to do a Full Join with MySQL

Let’s look at an example to illustrate the idea that we need to simulate a full outer join with the UNION operator.

The following query will not work in MySQL. You will get a syntax error.

SELECT * from [TABLE 1] OUTER JOIN [TABLE 2] on [TABLE 1].id = [TABLE 2].table_one_id;

To convert this to a full outer join that is compatible with native MySQL operators, we will need to write the following:

SELECT * from [TABLE 1] LEFT JOIN [TABLE 2] on [TABLE 1].id = [TABLE 2].table_one_id
UNION
SELECT * from [TABLE 1] RIGHT JOIN [TABLE 2] on [TABLE 1].id = [TABLE 2].table_one_id;

In this new query, we simply use the UNION operator with a LEFT JOIN and a RIGHT JOIN. The UNION operator will automatically remove any duplicates.

Now just in case replacing the table names with placeholders like [TABLE 1] confused you, let’s look at an example query with fake table names.

SELECT * from users LEFT JOIN user_contacts on users.id = user_contacts.user_id
UNION
SELECT * from users RIGHT JOIN user_contacts on users.id = user_contacts.user_id;

There we go. It might not be as efficient as if there was a true OUTER JOIN operator, but at least it does the job.

How Does UNION in MySQL Handle Duplicates?

By default, the UNION operator removes duplicates from your results. If you want to keep duplicates, use the UNION ALL operator.

I already answered this earlier in the article, but for the sake of someone trying to search for this exact problem, I also made this a header. That way Google can easily find the answer.

The UNION operator automatically removes duplicates by default. This is useful when trying to simulate an OUTER JOIN in MySQL. If you want to not remove duplicates, you will need to further specify this by using the UNION ALL operator.

If you want to read up on the other requirements for using the UNION operator, you can check out the official documentation here.

Check out how to connect your Google Cloud Database with MySQL Workbench with my visual guide here. MySQL Workbench is arguably the best free beginner tool to start working with MySQL out there.

Conclusion

Thanks for reading and look out for more content from RTL Coding. Hope this article was useful and if you would like to support this blog and this content feel free to donate via Paypal to help support more helpful content.