How to do a FULL JOIN in MySQL, TLDR: Fake It!

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:

SELECT * FROM a
LEFT JOIN b ON a.id = b.id
UNION
SELECT * FROM a
RIGHT JOIN b ON a.id = b.id

Nifty ven diagrams from TablePlus demonstrating the UNION of the LEFT JOIN and RIGHT JOIN results to create a FULL JOIN:

 

topherPedersen