How to do a FULL OUTER JOIN in MySQL?

Posted by Editorial Staff | Updated on

If you want to do a FULL OUTER JOIN using MySQL? You don’t have FULL JOINS on MySQL, but you can sure emulate them. For a code SAMPLE transcribed from this SO question you have with two tables t1, t2:

The query above works for special cases where a FULL OUTER JOIN operation would not produce any duplicate rows. The query above depends on the UNION set operator to remove duplicate rows introduced by the query pattern. We can avoid introducing duplicate rows by using an anti-join pattern for the second query, and then use a UNION ALL set operator to combine the two sets. In the more general case, where a FULL OUTER JOIN would return duplicate rows, we can do this:


If you like this question & answer and want to contribute, then write your question & answer and email to freewebmentor[@]gmail.com. Your question and answer will appear on FreeWebMentor.com and help other developers.

Related Questions & Answers