This blog post is brought to you by the developer of BitBudget. BitBudget is an automated budgeting app for Android and iOS which syncs with your bank account and helps you avoid overspending. If you’d like to quit living paycheck-to-paycheck and get a better handle on your finances, download it today! https://bitbudget.io

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: