Is there a reason MySQL doesn't support FULL OUTER JOINS?

Question

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 inadvance;-)

Answer 1

Because it was never implemented by MySQL developers. Why? Because there was not enough pressure from customers.

Answer 2

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

to this:

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.

Answer 3

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!

Answer 4

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.

Answer 5

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.