Categories
full-outer-join join mysql outer-join sql

How can I do a FULL OUTER JOIN in MySQL?

807

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

6

  • 2

    possible duplicate of MySQL Full Outer Join Syntax Error

    Jan 25, 2011 at 17:39

  • 4

    This question have better answers

    Nov 3, 2014 at 20:25

  • 3

    Beware of the answers here. 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. Most answers here are wrong (see the comments) & the ones that aren’t wrong don’t handle the general case. Even though there are many (unjustified) upvotes. (See my answer.)

    – philipxy

    Aug 11, 2018 at 22:28

  • 1

    @JairoLozano Constraints are not needed to query. Although when constraints hold extra queries return the desired answer that otherwise wouldn’t. Constraints don’t affect what full join on returns for given arguments. The problem you describe is that the query you wrote is the wrong query. (Presumably the common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations.)

    – philipxy

    Feb 7, 2020 at 5:57


  • 2

    all the answers doing UNION instead of UNION ALL are incorrect. all answers with subqueries or 3 unioned selects are inefficient. correct answers will do a union all of a left join with a select from the second table with a where not exists on the first table (or the equivalent outer join + where =NULL condition)

    – ysth

    Aug 16, 2020 at 6:33

829

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

23

  • 38

    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.

    Mar 19, 2013 at 18:41


  • 176

    This is the correct example: (SELECT ... FROM tbl1 LEFT JOIN tbl2 ...) UNION ALL (SELECT ... FROM tbl1 RIGHT JOIN tbl2 ... WHERE tbl1.col IS NULL)

    Mar 19, 2013 at 18:45


  • 8

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

    Mar 19, 2013 at 18:49

  • 5

    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?

    – ysth

    Mar 31, 2014 at 21:09

  • 14

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

    May 7, 2015 at 14:54


421

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.

10

  • 4

    For MySQL you really want to avoid using UNION instead of UNION ALL if there is no overlap (see Pavle’s comment above). If you could add some more info to that effect in your answer here, I think it’d be the preferred answer for this question as it’s more thorough.

    – Garen

    Feb 11, 2014 at 21:47

  • 3

    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.

    May 7, 2015 at 15:18

  • 1

    @IstiaqueAhmed: the goal is to emulate a FULL OUTER JOIN operation. We need that condition in the second query so it returns only rows that don’t have a match (an anti-join pattern.). Without that condition, the query is an outer join… it returns rows that match as well as those without a match. And the rows that match were already returned by the first query. If the second query returns those same rows (again), we’ve duplicated rows and our result will not be equivalent to a FULL OUTER JOIN.

    Nov 6, 2017 at 14:12

  • 1

    @IstiaqueAhmed: It is true that a UNION operation will remove those duplicates; but it also removes ALL duplicate rows, including duplicate rows that would be in the returned by a FULL OUTER JOIN. To emulate a FULL JOIN b, the correct pattern is (a LEFT JOIN b) UNION ALL (b ANTI JOIN a).

    Nov 6, 2017 at 14:16


  • 4

    Very concise answer with a great explanation. Thanks for this.

    – Najeeb

    Feb 22, 2019 at 6:32

45

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

6

  • 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 ?:

    Oct 13, 2015 at 7:57

  • I suggest you to use some temporary tables ;).

    – shA.t

    Oct 13, 2015 at 9:20

  • 6

    This method seems to 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.”

    Jul 22, 2016 at 7:44


  • 2

    @SteveChambers it’s too late, but thanks for your comment. I added your comment to then answer to highlighted more, If you are not agree please roll it back ;).

    – shA.t

    Sep 7, 2017 at 14:31

  • No problem @shA.t – IMO this should really have more upvotes and/or be the accepted answer.

    Sep 7, 2017 at 15:05