MySQL and full outer join

MySQL does not natively support full outer joins. Recently, in my day job, I ran into a situation where I needed this functionality (check out the Venn diagram below for the visual).

Here's how to do it in MySQL with a two table example and the notation from the Venn diagram:

SELECT [select_list]
FROM TableA `A`
LEFT OUTER JOIN TableB `B` 
ON `A`.`Key` = `B`.`Key`

UNION

SELECT [select_list]
FROM TableA `A`
RIGHT OUTER JOIN TableB `B` 
ON `A`.`Key` = `B`.`Key`;

In english:

  1. Give me everything that is in TableA and the intersection of TableA and TableB.
  2. Give me the intersection of TableA and TableB and everything that is in TableB.
  3. Union the results (MySQL collapses duplicates in the union).

The diagram gives a succinct visualization of several common joins. MySQL supports the top five natively, while the two full outer joins on the bottom can be accomplished with the technique I have demonstrated here.