How to perform FULL OUTER JOIN in MySQL?

Question

I want to perform a full outer join in MySQL. is it possible? Does MySQL support full outer joins?

Answer 1

The answer given by

Pablo Santa Cruz is correct; however, if anyone stumbles upon this page and needs more clarification, here's a detailed breakdown.

Example table

Suppose we have the following table:

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

Inner join

Internal joins, as shown below:

SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

will only allow us to see records that appear in both tables, as shown below:

1 Tim  1 Tim

Inner joins have no direction (e.g. left or right) because they are explicitly bidirectional - we need both sides to match.

OUTER JOIN

On the other hand, outer joins are used to find records that may not match in another table. Therefore, you must specify which side of the connection is allowed to have missing records.

LEFT JOIN and RIGHT JOIN are shorthand for LEFT OUTER JOIN and RIGHT OUTER JOIN; I will use theirs below Full name to reinforce the concept of outer joins and inner joins.

Left Outer Join

Left outer join, as shown below:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

... will get all records from the left table regardless of whether they have a match in the right table, like this:

1 Tim   1    Tim
2 Marta NULL NULL

Right outer join

Right outer join, as shown below:

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

...will get all records from the right table regardless of whether they have a match in the left table, like this:

1    Tim   1  Tim
NULL NULL  3  Katarina

Full outer join

A complete outer join will give us all records from both tables regardless of whether they have a match in the other table, or NULL on both sides if not. The result is as follows:

1    Tim   1    Tim
2    Marta NULL NULL
NULL NULL  3    Katarina

However, as Pablo Santa Cruz pointed out, MySQL does not support this. We can emulate it with a UNION of a left join and a right join, like this:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

You can think of

UNION as "run these two queries and then stack the results together"; some rows will come from the first query and some from the second.

It is important to note that

UNION in MySQL will eliminate exact duplicates: Tim will appear in both queries here, but the results for UNION will only be listed Him once. My fellow database experts believe this behavior should not be relied upon. So, to make this more explicit, we can add a WHERE clause to the second query:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`id` IS NULL;

On the other hand, if for some reason you

want to see duplicates, you can use UNION ALL.

Answer 2

You don't have full connections in MySQL, but you can sure emulate them.

For code transcribed from this Stack Overflow question Example You have:

There are two tables t1 and t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

---

The above query is suitable for the special case where complete outer join operation will not produce any duplicate rows. The above query relies on the UNION set operator to remove duplicate rows introduced by the query pattern. We can avoid introducing duplicate rows by using the anti-join pattern on the second query, and then using the UNION ALL set operator to combine the two sets. In the more general case where a full outer join will return duplicate rows, we can do this:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL