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`;
- Give me everything that is in
TableAand the intersection of
- Give me the intersection of
TableBand everything that is in
- 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.