mysql - 没有伪列的不同字段上的MySQL联合

因此，假设我有两个或多个表，这些表由不同的列组成，其中不一定存在共享密钥（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 |
+----+---------+--------+

所需的效果是在id插入空值（数据不可用）时将表聚合在一起：

+----+-------+-------+-------+---------+-----+------------+------+---------+--------+
| 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 |
+----+-------+-------+-------+---------+-----+------------+------+---------+--------+

我可以使用空的'dummy'列和UNION（MySql SELECT union for different columns?），但如果表的数量很大，这似乎是一个巨大的痛苦。我想有一个连接方法可以用来完成这个任务，但是我需要一些帮助来解决这个问题。
这是有效的：

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的行和行插入空值，就可以得到相同的结果，就像whatever一样，只要我想添加额外的表就可以了？
提前谢谢！

最佳答案

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，表没有共享其他列，则可以使用simple to write（但更容易断开）：

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