How to do a FULL OUTER JOIN in MySQL

Question

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

User · Accepted Answer

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

User · Answer

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 n 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 n 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

User · Answer

I fix the response  and works include all rows  based on response of Pavle Lekic             SELECT a   FROM tablea a     LEFT JOIN tableb b ON a  key    b key     WHERE b  key  is null           UNION ALL           SELECT a   FROM tablea a     LEFT JOIN tableb b ON a  key    b key     where  a  key    b  key            UNION ALL           SELECT b   FROM tablea a     right JOIN tableb b ON b  key    a key     WHERE a  key  is null

User · Answer

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

User · Answer

None of the above 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 for this to work with NULL values  which may also be necessary   then use the NULL-safe comparison operator   lt   gt  rather than

User · Answer

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

User · Answer

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

User · Answer

It is also possible  but you have to mention the same field names in select    SELECT t1 name  t2 name FROM t1 LEFT JOIN t2 ON t1 id   t2 id UNION SELECT t1 name  t2 name FROM t2 LEFT JOIN t1 ON t1 id   t2 id

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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           SQL Standard 2006 SQL Foundation 7 7 Syntax Rules 1  General Rules 1 b  3 c  amp  d  5 b

User · Answer

what d you say about Cross join solution   SELECT t1    t2   FROM table1 t1 INNER JOIN table2 t2  ON 1 1

User · Answer

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

[sql] How to do a FULL OUTER JOIN in MySQL?

Examples related to sql

Examples related to mysql

Examples related to join

Examples related to outer-join

Examples related to full-outer-join