没有虚拟列的不同字段的 MySQL Union

Question

假设我有 2 个或更多表，这些表由不同的列组成，其中不一定存在共享 key (id):

Alpha:  
+----+-------+-------+-------+
| id | paula | randy | simon |
+----+-------+-------+-------+
|  1 |     8 |     7 |     2 |
|  2 |     9 |     6 |     2 |
|  3 |    10 |     5 |     2 |
+----+-------+-------+-------+

Beta:
+----+---------+-----+------------+------+
| id | is_nice | sex |        dob | gift |
+----+---------+-----+------------+------+
|  2 |       1 |   F | 1990-05-25 | iPod |
|  3 |       0 |   M | 1990-05-25 | coal |
+----+---------+-----+------------+------+

Gamma:
+----+---------+--------+
| id | is_tall | is_fat |
+----+---------+--------+
|  1 |       1 |      1 |
| 99 |       0 |      1 |
+----+---------+--------+

期望的效果是在数据不可用的地方插入 NULL 时将表混合在一起:

+----+-------+-------+-------+---------+-----+------------+------+---------+--------+
| id | paula | randy | simon | is_nice | sex |        dob | gift | is_tall | is_fat |
+----+-------+-------+-------+---------+-----+------------+------+---------+--------+
|  1 |     8 |     7 |     2 |         |     |            |      |       1 |      1 |
|  2 |     9 |     6 |     2 |       1 |   F | 1990-05-25 | iPod |         |        |
|  3 |    10 |     5 |     2 |       0 |   M | 1990-05-25 | coal |       1 |      1 |
| 99 |       |       |       |         |     |            |      |       0 |      0 |
+----+-------+-------+-------+---------+-----+------------+------+---------+--------+

我可以使用 NULL 'dummy' 列和 UNION ( MySql SELECT union for different columns? )，但如果表的数量很大，这似乎是一种痛苦。我想我可以使用 JOIN 方法来完成此操作，但我需要一些帮助来解决这个问题。

这个有效:

SELECT `id`, `paula`, `randy`, ..., NULL AS `is_nice`, ... FROM `Alpha`
UNION SELECT `id`, NULL AS `paula`, ..., FROM `Beta`
UNION SELECT `id`, NULL AS `paula`, ..., `is_fat` FROM `Gamma` ;

但这确实感觉像是错误的做法。每当我想添加其他表时，我如何才能获得相同的结果，而不必编辑一行又一行的 SQL 插入 NULL AS whatever？

提前致谢!

Answer 1

SELECT
    allid.id
  , a.paula, a.randy a.simon
  , b. ...
  , c. ... 
FROM
        ( SELECT id
          FROM Alpha
        UNION 
          SELECT id
          FROM Beta
        UNION 
          SELECT id
          FROM Gamma 
        ) AS allid
    LEFT JOIN
        Alpha AS a
            ON a.id = allid.id
    LEFT JOIN
        Beta AS b
            ON b.id = allid.id
    LEFT JOIN
        Gamma AS g
            ON g.id = allid.id

---

如果表除了 id 没有共享其他列，您可以使用简单的编写方式(但更容易破解):

SELECT 
    *
FROM
        ( SELECT id
          FROM Alpha
        UNION 
          SELECT id
          FROM Beta
        UNION 
          SELECT id
          FROM Gamma 
        ) AS allid
    NATURAL LEFT JOIN
        Alpha
    NATURAL LEFT JOIN
        Beta 
    NATURAL LEFT JOIN
        Gamma