Rectangle 27 1

sql How to do a FULL OUTER JOIN in MySQL?


(SELECT ... FROM tbl1 LEFT JOIN tbl2 ...) UNION ALL (SELECT ... FROM tbl1 RIGHT JOIN tbl2 ... WHERE tbl1.col IS NULL)
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

@ypercube: If there no duplicate rows in t1 and t2, the query in this answer does return a resultset that emulates FULL OUTER JOIN. But in the more general case, for example, the SELECT list doesn't contain sufficient columns/expressions to make the returned rows unique, then this query pattern is insufficient to reproduce the set that would be produced by a FULL OUTER JOIN. To get a more faithful emulation, we'd need a UNION ALL set operator, and one of the queries would need an anti-join pattern. The comment from Pavle Lekic (above) gives the correct query pattern.

Actually the thing you wrote is not correct. Because when you do a UNION you will remove duplicates, and sometimes when you join two different tables there should be duplicates.

For a code SAMPLE transcribed from this SO question you have:

So the difference is that I am doing a left inclusive join and then right exclusive using UNION ALL

You don't have FULL JOINS on MySQL, but you can sure emulate them.

and I see now that you say that yourself, sorry. Perhaps you could update your answer, given there is this case that it gets wrong and that the UNION ALL is always going to be more efficient?

with two tables t1, t2:

Note
Rectangle 27 1

sql How to do a FULL OUTER JOIN in MySQL?


-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina
1    Tim   1    Tim
2    Marta NULL NULL
NULL NULL  3    Katarina
1    Tim   1  Tim
NULL NULL  3  Katarina
1 Tim   1    Tim
2 Marta NULL NULL
1 Tim  1 Tim
SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
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;
SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
UNION ALL

...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:

@NathanLong your post here is old but man it is an awesome little piece of clarification and instruction. well written and examplefied! Awesome.

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:

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:

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

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:

Jeff Atwood's post does a really good job of visually explaining join types with Venn diagrams. And, he gives an example of the anti-join... returning rows from A that are not in B. And we can visually see how this set is distinct from the set of all rows in B along with matching rows in A.

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.

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.

Suppose we have the following tables:

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.

The recommendation from the "database guru colleague" is correct. In terms of the relational model (all the theoretical work done by Ted Codd and Chris Date), a query of the last form emulates a FULL OUTER JOIN, because it combines two distinct sets, The second query doesn't introduce "duplicates" (rows already returned by the first query) that would not be produced by a FULL OUTER JOIN. There's nothing wrong with doing queries that way, and using UNION to remove those duplicates. But to really replicate a FULL OUTER JOIN, we need one of the queries to be an anti-join.

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

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.

Note
Rectangle 27 1

sql How to do a FULL OUTER JOIN in MySQL?


SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 
ON 1=1;

No, this is a cross join. It will match every row in t1 to every row in t2, yielding the set of all possible combinations, with select (select count(*) from t1) * (select count(*) from t2)) rows in the result set.

Note
Rectangle 27 1

sql How to do a FULL OUTER JOIN in MySQL?


-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina
1    Tim   1    Tim
2    Marta NULL NULL
NULL NULL  3    Katarina
1    Tim   1  Tim
NULL NULL  3  Katarina
1 Tim   1    Tim
2 Marta NULL NULL
1 Tim  1 Tim
SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
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;
SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;
UNION ALL

...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:

@NathanLong your post here is old but man it is an awesome little piece of clarification and instruction. well written and examplefied! Awesome.

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:

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:

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

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:

Jeff Atwood's post does a really good job of visually explaining join types with Venn diagrams. And, he gives an example of the anti-join... returning rows from A that are not in B. And we can visually see how this set is distinct from the set of all rows in B along with matching rows in A.

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.

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.

Suppose we have the following tables:

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.

The recommendation from the "database guru colleague" is correct. In terms of the relational model (all the theoretical work done by Ted Codd and Chris Date), a query of the last form emulates a FULL OUTER JOIN, because it combines two distinct sets, The second query doesn't introduce "duplicates" (rows already returned by the first query) that would not be produced by a FULL OUTER JOIN. There's nothing wrong with doing queries that way, and using UNION to remove those duplicates. But to really replicate a FULL OUTER JOIN, we need one of the queries to be an anti-join.

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

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.

Note
Rectangle 27 1

sql How to do a FULL OUTER JOIN in MySQL?


-- (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)
FULL OUTER JOIN
UNION
UNION ALL
[SQL Fiddle]
[Table: t1]                            [Table: t2]
value                                  value
-------                                -------
1                                      1
2                                      2
4                                      2
4                                      5
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
value | value
------+-------
1     | 1
2     | 2
2     | 2
4     | NULL
4     | NULL
NULL  | 5
value | value
------+-------
Null  | 5 
1     | 1
2     | 2
4     | Null

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:

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

Result of above query that is as same as expected result:

This is the expected result of full outer join:

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

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

We are doing same task tow times , If there are sub query for t1 and t2 then mysql have to do same task more times, is not it ? Can we remove this using alias in this situation ?:

Note
Rectangle 27 1

sql How to do a FULL OUTER JOIN in MySQL?


-- (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)
FULL OUTER JOIN
UNION
UNION ALL
[SQL Fiddle]
[Table: t1]                            [Table: t2]
value                                  value
-------                                -------
1                                      1
2                                      2
4                                      2
4                                      5
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
value | value
------+-------
1     | 1
2     | 2
2     | 2
4     | NULL
4     | NULL
NULL  | 5
value | value
------+-------
Null  | 5 
1     | 1
2     | 2
4     | Null

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:

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

Result of above query that is as same as expected result:

This is the expected result of full outer join:

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

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

We are doing same task tow times , If there are sub query for t1 and t2 then mysql have to do same task more times, is not it ? Can we remove this using alias in this situation ?:

Note
Rectangle 27 1

sql How to do a FULL OUTER JOIN in MySQL?


-- 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 t2.value IS NULL
Note
Rectangle 27 1

sql How to do a FULL OUTER JOIN in MySQL?


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
(SELECT ... FROM tbl1 LEFT JOIN tbl2 ...) UNION ALL (SELECT ... FROM tbl1 RIGHT JOIN tbl2 ... WHERE tbl1.col IS NULL)
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

@ypercube: If there no duplicate rows in t1 and t2, the query in this answer does return a resultset that emulates FULL OUTER JOIN. But in the more general case, for example, the SELECT list doesn't contain sufficient columns/expressions to make the returned rows unique, then this query pattern is insufficient to reproduce the set that would be produced by a FULL OUTER JOIN. To get a more faithful emulation, we'd need a UNION ALL set operator, and one of the queries would need an anti-join pattern. The comment from Pavle Lekic (above) gives the correct query pattern.

Actually the thing you wrote is not correct. Because when you do a UNION you will remove duplicates, and sometimes when you join two different tables there should be duplicates.

For a code SAMPLE transcribed from this SO question you have:

So the difference is that I am doing a left inclusive join and then right exclusive using UNION ALL

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:

You don't have FULL JOINS on MySQL, but you can sure emulate them.

and I see now that you say that yourself, sorry. Perhaps you could update your answer, given there is this case that it gets wrong and that the UNION ALL is always going to be more efficient?

with two tables t1, t2:

Note
Rectangle 27 1

sql How to do a FULL OUTER JOIN in MySQL?


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

Can we use it ? like as: SELECT * FROM leftTable lt LEFT JOIN rightTable rt ON lt.id = rt.lrid UNION SELECT lt.*, rl.* -- To match column set FROM leftTable lt RIGHT JOIN rightTable rt ON lt.id = rt.lrid;

yes but SQLite doesn't support right joins but yes in MYSQL yes

Note
Rectangle 27 1

sql How to do a FULL OUTER JOIN in MySQL?


-- 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 t2.value IS NULL
Note