How can I do a FULL OUTER JOIN in MySQL?

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 t1LEFT JOIN t2 ON t1.id = t2.idUNIONSELECT * FROM t1RIGHT 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 t1LEFT JOIN t2 ON t1.id = t2.idUNION ALLSELECT * FROM t1RIGHT JOIN t2 ON t1.id = t2.idWHERE t1.id IS NULL

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:

-- t1id  name1   Tim2   Marta-- t2id  name1   Tim3   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    Tim2 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  TimNULL 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    Tim2    Marta NULL NULLNULL 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`UNIONSELECT *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`UNIONSELECT *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.

Using a union query will remove duplicates, and this is different than the behavior of full outer join that never removes any duplicates:

[Table: t1]        [Table: t2]value              value-----------        -------1                  12                  24                  24                  5

This is the expected result of a full outer join:

value | value------+-------1     | 12     | 22     | 2Null  | 54     | Null4     | Null

This is the result of using left and right join with union:

value | value------+-------Null  | 51     | 12     | 24     | Null

SQL Fiddle

My suggested query is:

select    t1.value, t2.valuefrom t1left outer join t2  on t1.value = t2.valueunion all      -- Using `union all` instead of `union`select    t1.value, t2.valuefrom t2left outer join t1  on t1.value = t2.valuewhere    t1.value IS NULL

The result of the above query that is as the same as the expected result:

value | value------+-------1     | 12     | 22     | 24     | NULL4     | NULLNULL  | 5

SQL Fiddle

@Steve Chambers: ^{[From comments, with many thanks!]}

Note: This may be the best solution, both for efficiency and for generating the same results as a FULL OUTER JOIN. This blog post also explains it well - to quote from Method 2: "This handles duplicate rows correctly and doesn’t include anything it shouldn’t. It’s necessary to use UNION ALL instead of plain UNION, which would eliminate the duplicates I want to keep. This may be significantly more efficient on large result sets, since there’s no need to sort and remove duplicates."

I decided to add another solution that comes from full outer join visualization and math. It is not better than the above, but it is more readable:

Full outer join means (t1 ∪ t2): all in t1 or in t2(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only: all in both t1 and t2 plus all in t1 that aren't in t2 and plus all in t2 that aren't in t1:

-- (t1 ∩ t2): all in both t1 and t2select t1.value, t2.valuefrom t1 join t2 on t1.value = t2.valueunion all  -- And plus-- all in t1 that not exists in t2select t1.value, nullfrom t1where not exists( select 1 from t2 where t2.value = t1.value)union all  -- and plus-- all in t2 that not exists in t1select null, t2.valuefrom t2where not exists( select 1 from t1 where t2.value = t1.value)

SQL Fiddle