MySQL多条件Full Outer Join实现方法咨询

你现在遇到的核心问题是：之前用的隐式内连接只会返回两个表中完全匹配的记录，而你需要的是包含所有记录的结果——不管记录只在table1、只在table2还是同时存在于两个表。这种场景下，**全外连接（FULL OUTER JOIN）**就是最适合的解决方案。

先把你的原始表结构整理成更清晰的格式：

原始表结构 #

table1 #

table2 #

解决方案：使用FULL OUTER JOIN #

下面是完全符合你需求的SQL语句：

SELECT
    -- 生成连续的结果id，排序规则可按需调整
    ROW_NUMBER() OVER (ORDER BY COALESCE(t1.date_t, t2.date_t), COALESCE(t1.userid, t2.userid)) AS id,
    -- 用COALESCE处理null，确保字段不会为空
    COALESCE(t1.userid, t2.userid) AS userid,
    COALESCE(t1.date_t, t2.date_t) AS date_t,
    COALESCE(t1.game, t2.game) AS game,
    COALESCE(t1.hdigit, t2.hdigit) AS hdigit,
    t1.inside,
    t2.outside
FROM table1 t1
FULL OUTER JOIN table2 t2
    -- 保留你原来的匹配规则，确保同一条记录能正确合并
    ON t1.userid = t2.userid 
    AND t1.date_t = t2.date_t 
    AND t1.game = t2.game 
    AND t1.hdigit = t2.hdigit
ORDER BY id;

语句关键部分解释： #

1. FULL OUTER JOIN：会返回两个表的所有记录，当某条记录只在一个表中存在时，另一个表的对应字段会自动显示null，完美覆盖你需要的三种场景（两表共有的记录、仅table1有的记录、仅table2有的记录）。
2. COALESCE函数：专门用来处理null值，比如当某条记录只在table2中时，t1.userid会是null，这时候就用t2.userid填充，保证显示的字段值不会为空。
3. ROW_NUMBER()函数：用来生成你期望结果里的连续id，排序逻辑可以根据你的实际业务需求调整（比如按日期、用户id排序）。

兼容不支持FULL OUTER JOIN的数据库（如MySQL） #

如果你的数据库不支持FULL OUTER JOIN，可以用UNION ALL结合左连接和右连接来模拟：

-- 先取table1的所有记录 + 匹配的table2记录
SELECT
    ROW_NUMBER() OVER (ORDER BY COALESCE(t1.date_t, t2.date_t), COALESCE(t1.userid, t2.userid)) AS id,
    COALESCE(t1.userid, t2.userid) AS userid,
    COALESCE(t1.date_t, t2.date_t) AS date_t,
    COALESCE(t1.game, t2.game) AS game,
    COALESCE(t1.hdigit, t2.hdigit) AS hdigit,
    t1.inside,
    t2.outside
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.userid = t2.userid 
    AND t1.date_t = t2.date_t 
    AND t1.game = t2.game 
    AND t1.hdigit = t2.hdigit

UNION ALL

-- 再取table2中独有的记录（即和table1无匹配的记录）
SELECT
    -- 确保id连续，在table1的记录数基础上累加
    (SELECT COUNT(*) FROM table1) + ROW_NUMBER() OVER (ORDER BY t2.date_t, t2.userid) AS id,
    t2.userid,
    t2.date_t,
    t2.game,
    t2.hdigit,
    NULL AS inside,
    t2.outside
FROM table2 t2
LEFT JOIN table1 t1
    ON t1.userid = t2.userid 
    AND t1.date_t = t2.date_t 
    AND t1.game = t2.game 
    AND t1.hdigit = t2.hdigit
WHERE t1.id IS NULL

ORDER BY id;

为什么你之前的语句不行？ #

你之前用的是隐式内连接（用逗号分隔表，WHERE子句加匹配条件），这种连接方式的本质是INNER JOIN，只会返回两个表中完全满足匹配条件的记录，自然会丢失只在单个表中存在的记录，而全外连接正好填补了这个缺口。

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