Bug #18003 FULL OUTER JOIN (no syntax to cover)
Submitted: 6 Mar 2006 23:33 Modified: 7 Mar 2006 23:34
Reporter: Michael Christen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.1 OS:Mac OS X (OS-X)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[6 Mar 2006 23:33] Michael Christen
Description:
MySQL seems to support LEFT OUTER JOIN, but not FULL OUTER JOIN --

We really need the ability to JOIN RECORDS when the keys match, but also include all records FROM BOTH SIDES when the keys don't match!

--- need to be able to INSERT INTO TEMPTABLE [FIELDLIST] SELECT [FIELDLIST] FROM TABLE1 A FULL OUTER JOIN TABLE2 B ON A.KEY = B.KEY -- 

to get a a new table with all records!

This seems serious enough to be a bug, not a feature request.

Thanks.

How to repeat:
try FULL OUTER JOIN

Suggested fix:
ADD THE FEATURE ASAP -- YOU NEED TO SUPPORT FULL OUTER JOIN -- ALSO ORACLE SQL LETS YOU ADD (+) NEXT TO COLUMNS TO INCLUDE ROWS THAT DON'T MATCH
[7 Mar 2006 23:34] Miguel Solorzano
Thank you for the bug report. I changed to Feature request.
[28 May 2006 11:51] Occo Eric Nolf
Can I have a full outer join, pretty please? :-)

Kidding aside - it would be a great enhancement.

OEN
[28 Jan 2007 19:26] JJ Persaud
Supporting message (by me) for this requirement posted on the following URL.
http://forums.mysql.com/read.php?33,137053,137053#msg-137053
[3 May 2009 12:25] Kenneth Jorgensen
I would have to agree with Michael Christen on this one. MySQL is a great database product, and I enjoy fully working with it. However, not having full outer joins have annoyed me in the past, and while it is not hard to write a workaround using UNION and a bit of extra code, it makes my queries far more complicated than they have to be.

A preliminary fix would be to automatically rewrite the query using the UNION trick maybe ? This would probably make a full outer join use more temporary tables and involve a lot of overhead, but at least we'd have it.
[24 Jan 2011 16:22] Hans Ginzel
FULL OUTER JOIN is very usefull for testing -- comparing tables.
[14 Feb 2012 21:50] Matthew Looman
FULL OUTER JOIN is also useful when importing data from multiple sources/staging tables. The UNION ALL implementation gets very unwieldy when more than two source tables need to be combined.
[11 Jun 2012 14:39] ted strauss
FULL OUTER JOIN is an absolute must for my project, which is a data analytics application for science market. To my dismay I now have to switch to Postgres b/c of this. I hope someone can implement it one day. 
To add some additional voices to this issue, please see the stream of Qs on StackOverflow for this feature -- http://stackoverflow.com/search?q=mysql+full+join
[10 Aug 2012 7:14] Oliver Paczkowski
Vote for this one, too! +1
[27 Aug 2012 19:02] Jorge Jaen
I think this issue is important, is there an announced date or version where this will be solved?
[15 Jan 2013 19:54] Gilad Horev
Adding my vote to this one++
[28 Feb 2013 23:32] John Overman
Please add this feature.  It seems fairly trivial to just emulate an actual full outer join, for the amount of credibility and usability it will add.
[29 Jul 2013 7:24] Shane Bester
See also bug #69858
[28 May 2014 22:57] Stuardo Rodríguez
The report I'm building is already very complex and it needs 2 FULL OUTER JOINS so that means I have to make it x3 times bigger

SELECT
        User.user_id, User.username, User.created, User.last_name, User.name
        , Campus.name
        , Division.name
        , Sponsor.name, Sponsor.initials
        , Ptm.ptm_id, Ptm.name, Ptm.created, Ptm.launch_date, Ptm.slides, Ptm.avg_slide_time
        , PtmAnswer.completion_date, PtmAnswer.earned_points
        , QuickReview.hits
        , ResourceHits.resources_hits
        , AreaManager.username, AreaManager.name, AreaManager.last_name
    FROM          users                User
             JOIN (SELECT * FROM users_groups GROUP BY user_id)
                                       UsersGroups    ON UsersGroups.user_id   = User.user_id
             JOIN distributors         Campus         ON User.distributor_id   = Campus.id    AND Campus.id                 = $resellerId
  FULL OUTER JOIN ptms_users           PtmAnswer      ON PtmAnswer.user_id     = User.user_id AND PtmAnswer.refresher       = $refresher
  FULL OUTER JOIN ptms                 Ptm            ON PtmAnswer.ptm_id      = Ptm.ptm_id
        LEFT JOIN manufacturers        Sponsor        ON Ptm.manufacturer_id   = Sponsor.user_id
        LEFT JOIN divisions            Division       ON User.division_id      = Division.id
        LEFT JOIN (SELECT count(*) hits, ptm_quickreview_hits.* FROM ptm_quickreview_hits GROUP BY user_id, ptm_id)
                                       QuickReview    ON QuickReview.ptm_id    = Ptm.ptm_id   AND QuickReview.user_id       = User.user_id
        LEFT JOIN (SELECT count(*) resources_hits, resource_hits.* FROM resource_hits GROUP BY user_id, ptm_id)
                                       ResourceHits   ON ResourceHits.ptm_id   = Ptm.ptm_id   AND ResourceHits.user_id      = User.user_id
                                                      AND IF($refresher, ResourceHits.created <= PtmAnswer.completion_date, true)
        LEFT JOIN users                AreaManager    ON User.area_manager_id  = AreaManager.user_id
    WHERE TRUE $where
    ORDER BY User.user_id ASC, Ptm.ptm_id ASC

I can do the UNION rewrite, but I hope not lo live long enough to be the one to maintain that code. Please, please, add FULL OUTER JOIN to MySQL.
[28 May 2015 21:14] Sascha Baumeister
Here's my two cent why MySQL support for FULL OUTER JOIN is important:

- First: Having this operation would all university teachers like me to teach solely INNER JOIN and (full) OUTER JOIN, show students that both operations are commutative and how they work, and leave left and right outer join aside as "optimizations for advanced users". From experience, I'd say that'd shave off around 1/3 of the time I need to teach join operations, would leave my students 1/3 less confused, and after two weeks they'd remember 1/3 more useful stuff about joins. And this is important, especially when teaching non-engineers who are hardly able to copy files.

- Second: This is so important to me that I'm currently searching for a replacement RDBMS. Sadly, mariadb hasn't got full outer joins either so far, but Firebird for example does. If you really want to lose free and multiplicative support of University teaching, continue not to support full outer join. This is seldom an expert issue, hardly any developer really needs the operation, and if they do they can usually work around it using left outer join and union. But for teaching relational algebra and SQL, having support for full outer join would make teacher, student and casual user lives a LOT easier ...
[10 May 2016 5:09] Max Mir
There is no way to elegantly solve a problem involving multiple full outer joins. Can we please add this functionality - it has been several years since this feature was first requested.
[20 Jul 2016 13:09] Domenico Discepola
very important feature to have - workaround is unacceptable