如何实现保留条件列所有值的两表连接？解决连接后id列空值问题

我有两张数据表，结构和数据如下：

表1（包含id和score1字段）：

表2（包含id、score2和score3字段）：

我想要将这两张表连接后得到如下结果：

但我尝试了所有JOIN类型后，结果中的id列出现了空值，请问该怎么解决这个问题？

嘿，这个问题其实很常见！你遇到的id空值问题，本质是普通的JOIN（内连接、左/右连接）没办法同时覆盖两张表里所有的id——内连接只保留两边都存在的id，左/右连接只能保留其中一张表的全部id，另一张表的id会被漏掉。要实现你要的结果，得先把两张表的id合并成一个完整的集合，再基于这个集合关联两张表。

解决方案步骤： #

• 获取所有唯一的id集合：用UNION合并两张表的id列，自动去重得到所有存在的id；
• 左连接两张表：以这个完整的id集合为主表，分别左连接表1和表2，确保每个id都能匹配到对应的score值；
• 填充缺失值为0：用COALESCE()函数（通用SQL函数，部分数据库也可以用IFNULL()）把关联不到的NULL值替换成0；
• 计算总分total：直接把三个score字段相加即可。

具体SQL代码（通用写法，适配大多数数据库）： #

WITH all_ids AS (
    SELECT id FROM 表1
    UNION
    SELECT id FROM 表2
)
SELECT
    ai.id,
    COALESCE(t1.score1, 0) AS score1,
    COALESCE(t2.score2, 0) AS score2,
    COALESCE(t2.score3, 0) AS score3,
    COALESCE(t1.score1, 0) + COALESCE(t2.score2, 0) + COALESCE(t2.score3, 0) AS total
FROM all_ids ai
LEFT JOIN 表1 t1 ON ai.id = t1.id
LEFT JOIN 表2 t2 ON ai.id = t2.id
ORDER BY ai.id;

代码解释： #

• WITH all_ids AS (...)：这是CTE（公共表表达式），用来临时存储所有唯一的id集合；
• UNION：合并两个SELECT的结果并自动去重，确保每个id只出现一次；
• COALESCE(a, b)：如果a是NULL就返回b，这里用来把关联不到的score值替换成0；
• 最后通过ORDER BY ai.id保证结果按id排序，和你想要的输出一致。

如果你的数据库不支持CTE（比如一些旧版本的MySQL），也可以用子查询来实现：

SELECT
    ai.id,
    COALESCE(t1.score1, 0) AS score1,
    COALESCE(t2.score2, 0) AS score2,
    COALESCE(t2.score3, 0) AS score3,
    COALESCE(t1.score1, 0) + COALESCE(t2.score2, 0) + COALESCE(t2.score3, 0) AS total
FROM (
    SELECT id FROM 表1
    UNION
    SELECT id FROM 表2
) ai
LEFT JOIN 表1 t1 ON ai.id = t1.id
LEFT JOIN 表2 t2 ON ai.id = t2.id
ORDER BY ai.id;

这样执行后，就能得到你想要的完整结果，不会出现id空值的情况啦！

内容的提问来源于stack exchange，提问作者QuangTM