Over the past two days I’ve been pretty stumped working on this new database for my latest app, Moolabeast. I’ve been experimenting with different table designs etc., and discovered what I really need is a FULL JOIN. Great! But just one problem… MySQL doesn’t support FULL JOINS. Yikes!
On StackOverflow I found a bunch of different posts from developers describing how to “fake” a FULL JOIN using various SQL statements that were a little beyond my comprehension. However, I finally found an excellent blog post from TablePlus explaining how this works.
Therefore, if you find yourself struggling to do a FULL JOIN in MySQL, I suggest checking out TablePlus’s explanation. I don’t think I could do it justice! It took me a while to find a really good post on the subject, thus that’s why I wanted to write this post to point more people in the right direction.
Essentially you need to do a LEFT JOIN, and a RIGHT JOIN, and then a UNION which will merge the results of the two joins. Now it isn’t perfect, but it’s probably close enough:
Nifty ven diagrams from TablePlus demonstrating the UNION of the LEFT JOIN and RIGHT JOIN results to create a FULL JOIN: