MySQL Bugs: #18003: FULL OUTER JOIN (no syntax to cover)

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:	MacOS (OS-X)
Assigned to:		CPU Architecture:	Any

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

Thank you for the bug report. I changed to Feature request.

Can I have a full outer join, pretty please? :-)

Kidding aside - it would be a great enhancement.

OEN

Supporting message (by me) for this requirement posted on the following URL.
http://forums.mysql.com/read.php?33,137053,137053#msg-137053

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.

FULL OUTER JOIN is very usefull for testing -- comparing tables.

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.

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

Vote for this one, too! +1

I think this issue is important, is there an announced date or version where this will be solved?

Adding my vote to this one++

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.

See also bug #69858

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.

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

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.

very important feature to have - workaround is unacceptable

Is it still hard to implement this feature in 2020 ?

Unfortunately is not even about being hard. But its unnecessary code when using only one join should work. But using as exampled,its just use left and right join with an union.

The work-around just feel insane to me. The query grows exponentially with each full join added. Just look at this monstrosity just from 2 full join. https://gist.github.com/danhuynhdev/1dc89d7daa21092c3a6cea53fa5ed6f2. For context, I working for a BI vendor and have to write a query rewriter for FULL JOIN :((.

Spring Data JDBC will get a feature that requires Full Outer Joins.
We'd love to support it for MySql as well.