SQL Server如何通过FULL OUTER JOIN合并两表获取全量关联结果

你写的FULL OUTER JOIN连接逻辑是正确的，但SELECT *的写法无法得到预期结果，需要调整查询字段的选取规则。

直接用SELECT *会同时返回[One].Id和[Two].Id两个独立的Id列：

• 对于Id=1/2/3的两表匹配行，两个Id列值相同
• 对于Id=4的仅在TWO表存在的行，[One].Id会返回NULL，[Two].Id返回4
  最终结果会出现两列Id，和你需要的单Id列展示效果不一致。

用COALESCE函数合并两个表的Id列，按顺序取第一个非空值作为最终Id，同时显式指定需要返回的字段即可。支持FULL OUTER JOIN的数据库（SQL Server、PostgreSQL、Oracle等）可以直接用如下写法：

SELECT 
  COALESCE([One].Id, [Two].Id) AS Id,
  [One].v1,
  [One].V2,
  [Two].v3,
  [Two].V4
FROM [One]
FULL OUTER JOIN [Two] ON [One].Id = [Two].Id
ORDER BY Id;

执行后返回的结果和你给出的预期结果完全一致。

如果使用不支持FULL OUTER JOIN的数据库（比如MySQL），可以通过UNION ALL组合左右连接实现等价效果：

SELECT 
  [One].Id AS Id,
  [One].v1,
  [One].V2,
  [Two].v3,
  [Two].V4
FROM [One]
LEFT JOIN [Two] ON [One].Id = [Two].Id

UNION ALL

SELECT 
  [Two].Id AS Id,
  NULL AS v1,
  NULL AS V2,
  [Two].v3,
  [Two].V4
FROM [Two]
LEFT JOIN [One] ON [One].Id = [Two].Id
WHERE [One].Id IS NULL
ORDER BY Id;

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