mysql - 如何在 mySQL 中为两个以上的表格式化 FULL OUTER JOIN？

Question

我有四张 table 。

Table A   Table B    Table C   Table D
+----+    +------+   +------+  +------+
| id |    |  id  |   |  id  |  |  id  |
+----+    +------+   +------+  +------+
|  1 |    |   2  |   |   3  |  |   4  |
+----+    |   4  |   +------+  +------+
          +------+

是否有执行 FULL OUTER JOIN 的查询使得输出是这样的？ :

+------+------+------+------+
|  id  |  id  |  id  |  id  |
+------+------+------+------+
| 1    | NULL | NULL | NULL |
| NULL | 2    | NULL | NULL |
| NULL | NULL | 3    | NULL |
| NULL | 4    | NULL | 4    |
+------+------+------+------+

我知道如何为 2 个表执行此操作:

SELECT * FROM table_a
LEFT JOIN table_b ON table_a.res_id = table_b.res_id
UNION
SELECT * FROM table_a
RIGHT JOIN table_b ON table_a.res_id = table_b.res_id;

但我不知道如何为超过 2 个表执行此操作。

感谢任何帮助。

SQL fiddle :http://sqlfiddle.com/#!9/58f416/1

Answer 1

对所有表使用 UNION 子查询。然后用它 LEFT JOIN 四个表:

SELECT 
    a.res_id as a_id,
    b.res_id as b_id,
    c.res_id as c_id,
    d.res_id as d_id
FROM (
    SELECT res_id FROM table_a
    UNION
    SELECT res_id FROM table_b
    UNION
    SELECT res_id FROM table_c
    UNION
    SELECT res_id FROM table_d
) u
LEFT JOIN table_a a ON a.res_id = u.res_id
LEFT JOIN table_b b ON b.res_id = u.res_id
LEFT JOIN table_c c ON c.res_id = u.res_id
LEFT JOIN table_d d ON d.res_id = u.res_id