MySQL: Expressing full joins with two tables having foreign keys to a common base table

Question

Suppose we have the need for a query performing two FULL JOINs (in PostgreSQL terminology) over table1-3, where table2 and table3 both have foreign keys pointing into table1. This shall work in MySQL, where FULL JOIN is not available. Thus a workaround has to be found.

Starting with LEFT JOINs, the following code works as described in (1):

SELECT table1.ID, table1.x, table2.y, table3.z
   FROM (table1
      LEFT JOIN table2
         ON table1.ID = table2.FOREIGN_ID)
      LEFT JOIN table3
         ON table1.ID = table3.FOREIGN_ID

Following (2), I'm able to emulate a full outer join using the UNION statement for a single join:

SELECT table1.ID, table1.x, table2.y
   FROM table1
      LEFT JOIN table2
         ON table1.ID = table2.FOREIGN_ID
UNION
SELECT table1.ID, table1.x, table2.y
   FROM table1
      RIGHT JOIN table2
         ON table1.ID = table2.FOREIGN_ID

yielding

and

SELECT table1.ID, table1.x, table3.z
   FROM table1
       LEFT JOIN table3
         ON table1.ID = table3.FOREIGN_ID
UNION
SELECT table1.ID, table1.x, table3.z
   FROM table1
       RIGHT JOIN table3
         ON table1.ID = table3.FOREIGN_ID
ORDER BY ID

yielding

I need advice about how to get to this output from here:

That is, the result rows, which reference an entry from table1 via foreign key should be joined soundly, while the stray entries from table2 and table3 (those with foreign key NULL) should be listed in extra rows (y2 and z4 in the example).

Answer 1

Turned out that this would be a trivial two-step FULL JOIN with in PostgreSQL:

SELECT table1.id, table1.x, table2.y, table3.z
  FROM table1
  FULL JOIN table2
    ON table2.Foreign_Id = table1.id
  FULL JOIN table3
    ON table3.Foreign_Id = table1.id
 ORDER BY table1.id, table2.id, table3.id;

Shoutouts to the guys from freenode #sql pointing out this equivalent MySQL query, which avoids using FULL JOIN:

SELECT id1, x, y, z FROM (
SELECT table1.id as id1, table1.x, table2.id as id2, table2.y, table3.id as id3, table3.z
  FROM table1
  LEFT JOIN table2
    ON table2.Foreign_Id = table1.id
  LEFT JOIN table3
    ON table3.Foreign_Id = table1.id
 UNION ALL
SELECT NULL, NULL, table2.id, table2.y, NULL, NULL
  FROM table2 WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE table1.id = table2.Foreign_Id)
 UNION ALL
SELECT NULL, NULL, NULL, NULL, table3.id, table3.z
  FROM table3 WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE table1.id = table3.Foreign_Id)
 ORDER BY id1, id2, id3 ) AS T