Categories
DBA

MySQL FULL OUTER JOIN

But the first two joins—the inner join, and the left exclusion join—are logically equivalent to a left outer join, so we can write:


SELECT * FROM a LEFT JOIN b ON a.id=b.id
UNION ALL
SELECT * FROM a RIGHT JOIN b ON a.id=b.id WHERE a.id IS NULL;

+------+------+------+------+
| id   | name | id   | name |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    |    2 | b    |
|    1 | a    | NULL | NULL |
| NULL | NULL |    3 | c    |
+------+------+------+------+

Why doesn’t MySQL implement FULL OUTER JOIN syntax for this? We don’t know.

via Common Queries Tree.