MySQL实现FULL OUTER JOIN并按列GROUP BY及结果优化问题

原始DDL与DML语句 #

CREATE TABLE `table1` (
  `id` int NOT NULL DEFAULT '0',
  `email` varchar(100) NOT NULL,
  `value1` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `table2` (
  `id` int NOT NULL DEFAULT '0',
  `email` varchar(100) NOT NULL,
  `value2` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `table3` (
  `id` int NOT NULL DEFAULT '0',
  `email` varchar(100) NOT NULL,
  `value3` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `table4` (
  `id` int NOT NULL DEFAULT '0',
  `email` varchar(100) NOT NULL,
  `value4` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `table1`
(`id`, `email`, `value1`)
VALUES
(1, 'email1@test.com', 1.1),
(2, 'email2@test.com', 1.2);

INSERT INTO `table2`
(`id`, `email`, `value2`)
VALUES
(2, 'email2@test.com', 2.2);

INSERT INTO `table3`
(`id`, `email`, `value3`)
VALUES
(1, 'email1@test.com', 3.1),
(2, 'email2@test.com', 3.2);

INSERT INTO `table4`
(`id`, `email`, `value4`)
VALUES
(1, 'email1@test.com', 4.1),
(2, 'email2@test.com', 4.2);

当前模拟FULL OUTER JOIN的SQL语句 #

SELECT * FROM table1 as t1 
LEFT JOIN table2 AS t2 ON t1.id = t2.id
LEFT JOIN table3 AS t3 ON t2.id = t3.id
LEFT JOIN table4 AS t4 ON t3.id = t4.id
UNION ALL
SELECT * FROM table1 as t1 
RIGHT JOIN table2 AS t2 ON t1.id = t2.id
LEFT JOIN table3 AS t3 ON t2.id = t3.id
LEFT JOIN table4 AS t4 ON t3.id = t4.id
WHERE t1.id IS NULL
UNION ALL
SELECT * FROM table1 as t1 
RIGHT JOIN table2 AS t2 ON t1.id = t2.id
RIGHT JOIN table3 AS t3 ON t2.id = t3.id
LEFT JOIN table4 AS t4 ON t3.id = t4.id
WHERE t2.id IS NULL
UNION ALL
SELECT * FROM table1 as t1 
RIGHT JOIN table2 AS t2 ON t1.id = t2.id
RIGHT JOIN table3 AS t3 ON t2.id = t3.id
RIGHT JOIN table4 AS t4 ON t3.id = t4.id
WHERE t3.id IS NULL;

执行结果 #

期望结果 #

问题 #

1. 从结果可见id=1被拆分为两行，原因是什么？如何将同一id和email的信息合并到一行，无对应值则显示NULL？
2. 我希望id和email仅在开头显示一次，不重复，该如何实现？

问题1解答 #

原因

你当前的关联逻辑是链式依赖的：用t1.id连t2，再用t2.id连t3，最后用t3.id连t4。对于id=1的情况，因为t2里没有这条数据，t2.id是NULL，导致t3和t4都无法通过t2.id关联上，所以第一个分支只能拿到t1的id=1数据；而后面的UNION分支中，当t2.id IS NULL时，会单独取出t3和t4的id=1数据，这就把同一id的内容拆成了两行。

解决方法

不要用链式关联，先收集所有表中出现过的唯一id作为基准，再让每个表直接关联这个基准id，这样就能把同一id的所有数据合并到一行。

示例SQL：

WITH all_ids AS (
    SELECT id FROM table1
    UNION
    SELECT id FROM table2
    UNION
    SELECT id FROM table3
    UNION
    SELECT id FROM table4
)
SELECT 
    t1.id, t1.email, t1.value1,
    t2.id, t2.email, t2.value2,
    t3.id, t3.email, t3.value3,
    t4.id, t4.email, t4.value4
FROM all_ids ai
LEFT JOIN table1 t1 ON ai.id = t1.id
LEFT JOIN table2 t2 ON ai.id = t2.id
LEFT JOIN table3 t3 ON ai.id = t3.id
LEFT JOIN table4 t4 ON ai.id = t4.id;

这个语句先通过UNION获取所有存在的id，再分别左连每个表，不管其他表有没有对应数据，同一id的所有关联内容都会出现在同一行，没有数据的字段自动显示NULL，完全匹配你的期望结果。

问题2解答 #

要实现id和email只在开头显示一次，你可以用COALESCE函数把所有表的id、email合并到开头的列中（同一id对应的email应该是一致的），同时去掉后面重复的id和email列。

示例SQL：

WITH all_ids AS (
    SELECT id FROM table1
    UNION
    SELECT id FROM table2
    UNION
    SELECT id FROM table3
    UNION
    SELECT id FROM table4
)
SELECT 
    COALESCE(t1.id, t2.id, t3.id, t4.id) AS id,
    COALESCE(t1.email, t2.email, t3.email, t4.email) AS email,
    t1.value1,
    t2.value2,
    t3.value3,
    t4.value4
FROM all_ids ai
LEFT JOIN table1 t1 ON ai.id = t1.id
LEFT JOIN table2 t2 ON ai.id = t2.id
LEFT JOIN table3 t3 ON ai.id = t3.id
LEFT JOIN table4 t4 ON ai.id = t4.id;

COALESCE会按顺序取第一个非NULL的值，所以优先展示t1的id和email，没有的话依次取t2、t3、t4的，这样开头只显示一组id和email，后面只保留各个value字段，完全满足不重复显示的需求。

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