How can I do a FULL OUTER JOIN in MySQL?

MySQL does not have FULL-OUTER-JOIN syntax. You have to emulate it by doing both LEFT JOIN and RIGHT JOIN as follows:

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

But MySQL also does not have a RIGHT JOIN syntax. According to MySQL's [outer join simplification][1], the right join is converted to the equivalent left join by switching the t1 and t2 in the `FROM` and `ON` clause in the query. Thus, the MySQL query optimizer translates the original query into the following -

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

Now, there is no harm in writing the original query as is, but say if you have predicates like the WHERE clause, which is a [**before-join**][2] predicate or an AND predicate on the `ON` clause, which is a [**during-join**][3] predicate, then you might want to take a look at the devil; which is in details.

The MySQL query optimizer routinely checks the predicates if they are *null-rejected*.

[![Null-Rejected Definition and Examples][4]][4]

Now, if you have done the RIGHT JOIN, but with WHERE predicate on the column from t1, then you might be at a risk of running into a *null-rejected* scenario.

For example, the query

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'

gets translated to the following by the query optimizer:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id
WHERE t1.col1 = 'someValue'

So the order of tables has changed, but the predicate is still applied to t1, but t1 is now in the 'ON' clause. If t1.col1 is defined as `NOT NULL`
column, then this query will be *null-rejected*.

Any outer-join (left, right, full) that is *null-rejected* is converted to an inner-join by MySQL.

Thus the results you might be expecting might be completely different from what the MySQL is returning. You might think its a bug with MySQL's RIGHT JOIN, but that’s not right. Its just how the MySQL query optimizer works. So the developer in charge has to pay attention to these nuances when he/she is constructing the query.

[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

The SQL standard says `full join on` is `inner join on` rows `union all` unmatched left table rows extended by nulls `union all` right table rows extended by nulls. Ie `inner join on` rows `union all` rows in `left join on` but not `inner join on` `union all` rows in `right join on` but not `inner join on`.

Ie `left join on` rows `union all` `right join on` rows not in `inner join on`. Or if you know your `inner join on` result can't have null in a particular right table column then "`right join on` rows not in `inner join on`" are rows in `right join on` with the `on` condition extended by `and` that column `is null`.

Ie similarly `right join on` `union all` appropriate `left join on` rows.

From [What is the difference between “INNER JOIN” and “OUTER JOIN”?](

[To see links please register here]

):

> (SQL Standard 2006 SQL/Foundation 7.7 Syntax Rules 1, General Rules 1 b, 3 c & d, 5 b.)

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

For a code *sample* transcribed from [this Stack Overflow question][2] 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

[1]:

[To see links please register here]

[2]:

[To see links please register here]

The answer that [Pablo Santa Cruz][1] 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:

```lang-none
-- 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:

```lang-none
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:

```lang-none
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:

```lang-none
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:

```lang-none
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`.

[1]:

[To see links please register here]

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

```lang-none
[Table: t1] [Table: t2]
value value
----------- -------
1 1
2 2
4 2
4 5
```

This is the expected result of a *full outer join*:

```lang-none
value | value
------+-------
1 | 1
2 | 2
2 | 2
Null | 5
4 | Null
4 | Null
```

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

```lang-none
value | value
------+-------
Null | 5
1 | 1
2 | 2
4 | Null
```

[SQL Fiddle][1]

My suggested query is:

select
t1.value, t2.value
from t1
left outer join t2
on t1.value = t2.value
union all -- Using `union all` instead of `union`
select
t1.value, t2.value
from t2
left outer join t1
on t1.value = t2.value
where
t1.value IS NULL

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

```lang-none
value | value
------+-------
1 | 1
2 | 2
2 | 2
4 | NULL
4 | NULL
NULL | 5
```

[SQL Fiddle][2]

---
> [@Steve Chambers][3]: <sup>[From comments, with many thanks!]</sup>

**Note:** This may be the best solution, both for efficiency and for generating the same results as a `FULL OUTER JOIN`. [This blog post][4] 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 t2
select t1.value, t2.value
from t1 join t2 on t1.value = t2.value
union all -- And plus
-- all in t1 that not exists in t2
select t1.value, null
from t1
where not exists( select 1 from t2 where t2.value = t1.value)
union all -- and plus
-- all in t2 that not exists in t1
select null, t2.value
from t2
where not exists( select 1 from t1 where t2.value = t1.value)

[SQL Fiddle][5]

[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]

[5]:

[To see links please register here]

Use:

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

It can be recreated as follows:

SELECT t1.*, t2.*
FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
LEFT JOIN t1 ON t1.id = tmp.id
LEFT JOIN t2 ON t2.id = tmp.id;

Using a UNION or UNION ALL answer does not cover the edge case where the base tables have duplicated entries.

**Explanation:**

There is an edge case that a UNION or UNION ALL cannot cover. We cannot test this on MySQL as it doesn't support full outer joins, but we can illustrate this on a database that does support it:

WITH cte_t1 AS
(
   SELECT 1 AS id1
   UNION ALL SELECT 2
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 6
),
cte_t2 AS
(
     SELECT 3 AS id2
   UNION ALL SELECT 4
   UNION ALL SELECT 5
   UNION ALL SELECT 6
   UNION ALL SELECT 6
)
SELECT  * FROM  cte_t1 t1 FULL OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2;

This gives us this answer:

```lang-none
id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6
```

The UNION solution:

SELECT  * FROM  cte_t1 t1 LEFT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
UNION    
SELECT  * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

Gives an incorrect answer:

```lang-none
id1  id2
NULL  3
NULL  4
1  NULL
2  NULL
5  5
6  6
```

The UNION ALL solution:

SELECT  * FROM cte_t1 t1 LEFT OUTER join cte_t2 t2 ON t1.id1 = t2.id2
UNION ALL
SELECT  * FROM  cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

Is also incorrect.

```lang-none
id1  id2
1  NULL
2  NULL
5  5
6  6
6  6
6  6
6  6
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6
```

Whereas this query:

SELECT t1.*, t2.*
FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
LEFT JOIN t1 ON t1.id = tmp.id
LEFT JOIN t2 ON t2.id = tmp.id;

Gives the following:

```lang-none
id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6
```

The order is different, but otherwise matches the correct answer.