Is there a reason MySQL doesn’t support FULL OUTER JOINS? I’ve tried full outer join syntax in mysql many times and it never worked, just found out its not supported by mysql so just curious as to why?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
MySQL lacks a lot of functionality that other databases have*. I think they have a pretty huge backlog of ideas and not enough developers to implement them all.
This feature was requested in 2006 and is still not implemented. I guess it has low priority because you can work around it by combining LEFT and RIGHT OUTER JOIN with a UNION ALL. Not pleasant, but it does the trick. Change this:
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.table2_id = table2.id
SELECT * FROM table1 LEFT JOIN table2 ON table1.table2_id = table2.id UNION ALL SELECT * FROM table1 RIGHT JOIN table2 ON table1.table2_id = table2.id WHERE table1.table2_id IS NULL
* To be fair to MySQL, they also have some features that many other databases don’t have.
I don’t believe the MySQL devs have ever stated any technical reason why it might be difficult to implement.
But MySQL, like most DBMSs, has many places where it does not fully implement the ANSI standard. Since FULL OUTER JOIN is a rarely-used feature, and can typically be replaced by a UNION workaround, there is little pressure to get it fixed.
I suggest adding your voice to bug 18003.
Because it was never implemented by MySQL developers.
Because there was not enough pressure from customers.
Out of a large system, I usually might use a FULL OUTER JOIN maybe once or twice, so there isn’t a huge demand for it and of course you can work around it fairly easily and potentially more explicitly readably (if you are inferring derived columns based on left/right results) with a UNION of LEFT and RIGHT JOINs.
as a product matures, more features get added with each release. just chalk this up to not being implemented yet. I’m sure it will eventually be there, it doesn’t mean that MySql is bad or anything, every database has extra as well as missing features. I wish SQL Server had the group concatenation feature that MySql has!
From High Performance MySQL:
At the moment, MySQL’s join execution strategy is simple: it treats every join as a nested-loop join. A FULL OUTER JOIN can’t be executed with nested loops and backtracking as soon as a table with no matching rows is found, because it might begin with a table that has no matching rows.This explains why MySQL doesn’t support FULL OUTER JOIN