How to do a FULL OUTER JOIN in MySQL?

I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?

 Answers

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

For a code SAMPLE transcribed from this SO 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

---

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

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

This is the expected result of 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 

Result of above query that is as same as 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 that above but 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]

---

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

---

MySql does not have FULL-OUTER-JOIN syntax. You have to emulate 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.

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 following 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 thats not right. Its just how the MySql query-optimizer works. So the developer-in-charge has to pay attention to these nuances when he is constructing the query.

---

what'd you say about this solution?

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

---

Mysql as such does not support any command named FULL OUTER JOIN. The three joins supported are INNER JOIN,LEFT JOIN & RIGHT JOIN.

You can however implement full outer join by using the Command UNION as
(left join query) UNION (right join query)

For instance, consider the following example where I have two tables students and marks. To perform full outer join, I would execute the following code:

SELECT * FROM students  
LEFT JOIN marks   
ON students.id = marks.id  
UNION ALL  
SELECT * FROM students 
RIGHT JOIN marks  
ON students.id = marks.id;

---

Answer:

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

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:

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:

 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.

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:

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.