SQL如何实现无重复记录的FULL JOIN全连接操作

问题根因 #

直接按日期字段做全外连接时，若同一个日期在表1有N条记录、表2有M条记录，会生成N*M条笛卡尔积结果，这就是你得到重复记录、没有出现预期null值的原因。
你的需求本质是同日期下的两张表记录按顺序一一配对，数量更多的那侧多出的记录，另一侧字段补null，需要先给同分组的记录添加行号再关联。

另外你原代码中COALESCE(...) AS date后面缺少逗号，也会先触发语法报错。

修正后的查询语句 #

以下写法兼容所有支持窗口函数的SQL引擎（MySQL 8.0+、PostgreSQL、SQL Server、Hive等）：

SELECT 
  COALESCE(t1.date_a, t2.date_b) AS date,
  t1.id_a,
  t2.id_b
FROM (
  -- 给表1同日期的记录按id_a排序加行号
  SELECT 
    id_a,
    date_a,
    ROW_NUMBER() OVER (PARTITION BY date_a ORDER BY id_a) AS rn
  FROM Table1
) t1
FULL OUTER JOIN (
  -- 给表2同日期的记录按id_b排序加行号
  SELECT 
    id_b,
    date_b,
    ROW_NUMBER() OVER (PARTITION BY date_b ORDER BY id_b) AS rn
  FROM Table2
) t2
ON t1.date_a = t2.date_b AND t1.rn = t2.rn
ORDER BY date;

逻辑说明 #

• 对两张表分别按日期分组，组内按id排序生成唯一行号
• 关联时同时匹配日期和行号，保证同日期下的记录按顺序一一对应
• 某张表同日期的记录数量更多时，多出的行号在另一张表没有匹配项，就会自动填充null，正好符合你预期的输出结果

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