How can I do a FULL OUTER JOIN in MySQL?

Question

I want to do a full outer join in MySQL. Is this possible? Is a full outer join supported by MySQL?

Answer 1

The answer that Pablo Santa Cruz gave is correct; however, in case anybody stumbled on this page and wants more clarification, here is a detailed breakdown.

Example Tables

Suppose we have the following tables:

-- t1 id  name 1   Tim 2   Marta  -- t2 id  name 1   Tim 3   Katarina 

Inner Joins

An inner join, like this:

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

Would get us only records that appear in both tables, like this:

1 Tim  1 Tim 

Inner joins don't have a direction (like left or right) because they are explicitly bidirectional - we require a match on both sides.

Outer Joins

Outer joins, on the other hand, are for finding records that may not have a match in the other table. As such, you have to specify which side of the join is allowed to have a missing record.

LEFT JOIN and RIGHT JOIN are shorthand for LEFT OUTER JOIN and RIGHT OUTER JOIN; I will use their full names below to reinforce the concept of outer joins vs inner joins.

Left Outer Join

A left outer join, like this:

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

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

1 Tim   1    Tim 2 Marta NULL NULL 

Right Outer Join

A right outer join, like this:

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

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

1    Tim   1  Tim NULL NULL  3  Katarina 

Full Outer Join

A full outer join would give us all records from both tables, whether or not they have a match in the other table, with NULLs on both sides where there is no match. The result would look like this:

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

However, as Pablo Santa Cruz pointed out, MySQL doesn't support this. We can emulate it by doing 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 a UNION as meaning "run both of these queries, then stack the results on top of each other"; some of the rows will come from the first query and some from the second.

It should be noted that a UNION in MySQL will eliminate exact duplicates: Tim would appear in both of the queries here, but the result of the UNION only lists him once. My database guru colleague feels that this behavior should not be relied upon. So to be more explicit about it, we could 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 you wanted to see duplicates for some reason, you could use UNION ALL.

Answer 2

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

For a code sample transcribed from this Stack Overflow question you have:

With two tables t1, 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 query above works for special cases where a full outer join operation would not produce any duplicate rows. The query above depends on the UNION set operator to remove duplicate rows introduced by the query pattern. We can avoid introducing duplicate rows by using an anti-join pattern for the second query, and then use a UNION ALL set operator to combine the two sets. In the more general case, where a full outer join would 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