How can I do a FULL OUTER JOIN in MySQL?

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.

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                  1
2                  2
4                  2
4                  5

This is the expected result of a full outer join:

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:

value | value
------+-------
Null  | 5
1     | 1
2     | 2
4     | Null

SQL Fiddle

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:

value | value
------+-------
1     | 1
2     | 2
2     | 2
4     | NULL
4     | NULL
NULL  | 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 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

None of the previous answers are actually correct, because they do not follow the semantics when there are duplicated values.

For a query such as (from this duplicate):

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

The correct equivalent is:

SELECT t1.*, t2.*
FROM (SELECT name FROM t1 UNION  -- This is intentionally UNION to remove duplicates
      SELECT name FROM t2
     ) n LEFT JOIN
     t1
     ON t1.name = n.name LEFT JOIN
     t2
     ON t2.name = n.name;

If you need this to work with NULL values (which may also be necessary), then use the NULL-safe comparison operator, <=> rather than =.

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, 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 predicate or an AND predicate on the ON clause, which is a during-join 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.

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.

I modified shA.t's query for more clarity:

-- t1 left join t2
SELECT t1.value, t2.value
FROM t1 LEFT JOIN t2 ON t1.value = t2.value   

    UNION ALL -- include duplicates

-- t1 right exclude join t2 (records found only in t2)
SELECT t1.value, t2.value
FROM t1 RIGHT JOIN t2 ON t1.value = t2.value
WHERE t1.value IS NULL 

In SQLite you should do this:

SELECT * 
FROM leftTable lt 
LEFT JOIN rightTable rt ON lt.id = rt.lrid 
UNION
SELECT lt.*, rl.*  -- To match column set
FROM rightTable rt 
LEFT JOIN  leftTable lt ON lt.id = rt.lrid

You can just convert a full outer join, e.g.

SELECT fields
FROM firsttable
FULL OUTER JOIN secondtable ON joincondition

into:

SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields -- replacing any fields from firsttable with NULL
FROM secondtable
WHERE NOT EXISTS (SELECT 1 FROM firsttable WHERE joincondition)

Or if you have at least one column, say foo, in firsttable that is NOT NULL, you can do:

SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields
FROM firsttable
RIGHT JOIN secondtable ON joincondition
WHERE firsttable.foo IS NULL

You can do the following:

(SELECT 
    *
FROM
    table1 t1
        LEFT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t2.id IS NULL)
UNION ALL
 (SELECT 
    *
FROM
    table1 t1
        RIGHT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t1.id IS NULL);

Full outer join between table L and R consists of:

1. rows from L x R where on predicate matches, as l<col1, col2, ...>, r<col1, col2, ...>
2. remaining rows from L if any, as l<col1, col2, ...>, r<null, null, ...>
3. remaining rows from R if any, as l<null, null, ...>, r<col1, col2, ...>

To emulate this behavior:

• Set #1 and #2 combined could be achieved with a left join
• Set #3 could be achieved with a not exists
• Combine both sets using union all

query

select l.*, r.*
from employee as l
left join department as r on l.fk = r.id

union all

select null, null, null, r.*
from department as r
where not exists (
  select 1
  from employee as l
  where l.fk = r.id
)

sample data and result

create table department (
  id varchar(100) not null primary key,
  name varchar(100)
)
insert into department (id, name) values
('d1', 'programming'),
('d2', 'designing'),
('d3', 'accounting')

create table employee (
  id varchar(100) not null primary key,
  name varchar(100),
  fk varchar(100)
)
insert into employee (id, name, fk) values
('e1', 'john', 'd1'),
('e2', 'jane', 'd2'),
('e4', 'zack', null)

| id   | name | fk   | id   | name        |
|------|------|------|------|-------------|
| e1   | john | d1   | d1   | programming |
| e2   | jane | d2   | d2   | designing   |
| e4   | zack | null | null | null        |
| null | null | null | d3   | accounting  |

DB<>Fiddle

You can use union all instead of full join. I'd do something like this:

select
    name, sum(num1) num1, sum(num2) num2
from
(
select name, num1, 0 num2 from table1
union all
select name, 0 num1, num2 from table2
) t
group by name

Here, the "name" column is equivalent to the column to join. The advantage of this is you don't need to join twice, you only need to group by once.

SELECT
    a.name,
    b.title
FROM
    author AS a
LEFT JOIN
    book AS b
    ON a.id = b.author_id
UNION
SELECT
    a.name,
    b.title
FROM
    author AS a
RIGHT JOIN
    book AS b
    ON a.id = b.author_id