If you’re familiar with databases and come from the Microsoft SQL Server world, you must love the ability to perform full outer joins when putting a dataset together.

Conversely, if you’re from the MySQL world, you must have no idea what I’m talking about since you cannot use full joins in MySQL.

Some Context

Let’s first understand the benefits of a full outer join to see why this would be valuable. Feel free to look at the video below to grasp an understanding of the intended output: Full Outer Join: SQL Tutorial with Example

How to Emulate a Full Outer Join

Now, you may be interested and want to replicate the full outer join in MySQL, but since it doesn’t offer support for this type of join, you will need to develop a workaround yourself.

Luckily, here is a straight forward approach to accomplishing this, using a combination of a left join, right join and union.

SELECT A.VALUE_A, B.VALUE_B

FROM A

LEFT JOIN B

ON A.VALUE_A= B.VALUE_B

UNION

SELECT A.VALUE_A, B.VALUE_B

FROM A

RIGHT JOIN B

ON A.VALUE_A= B.VALUE_B

;

The way this approach works, obviously, is it first performs a left or right join as a dataset, and then its counterpart (right or left join, respectively) as another dataset using the same query. This will give us “everything on the first table, even if the second table doesn’t have matching records” and also “everything on the second table, even if the first table doesn’t have matching records” as a second dataset. Then, by performing a union all, we stack these 2 datasets together and effectively combine them into one comprehensive dataset to give us “everything on the first and second table, regardless of whether or not they have matching values on their counterpart table”.

From an efficiency standpoint, yes, you are in fact querying twice as opposed to once which may not be ideal, however for the right use-case, this may be the right solution.

It’s really that simple!