MySQL如何通过公共列关联两表保留其余列值，替代FULL OUTER JOIN？

MySQL 本身不支持原生的 FULL OUTER JOIN 语法，要实现「基于公共列关联两张表、同时保留两张表所有行、匹配行合并列、非匹配行对应侧列补NULL」的效果，可以通过 左连接 + 独有行补集 + UNION 合并 的方案实现，具体逻辑和代码如下：

前提示例 #

假设你有两张需要关联的表：

• table_a：公共关联列为 common_id，其他业务列为 col_a1、col_a2
• table_b：公共关联列为 common_id，其他业务列为 col_b1、col_b2

实现代码 #

-- 第一步：左连接取表A的所有行，关联匹配到的表B数据
SELECT 
  COALESCE(a.common_id, b.common_id) AS common_id,
  a.col_a1,
  a.col_a2,
  b.col_b1,
  b.col_b2
FROM table_a a
LEFT JOIN table_b b 
  ON a.common_id = b.common_id

UNION

-- 第二步：取表B中没有和表A匹配的独有行，避免重复
SELECT 
  COALESCE(a.common_id, b.common_id) AS common_id,
  a.col_a1,
  a.col_a2,
  b.col_b1,
  b.col_b2
FROM table_a a
RIGHT JOIN table_b b 
  ON a.common_id = b.common_id
WHERE a.common_id IS NULL

如果你不想用RIGHT JOIN语法，也可以把第二段查询替换为B表左连接A表后过滤A表为空的逻辑，效果完全一致：

SELECT 
  COALESCE(a.common_id, b.common_id) AS common_id,
  a.col_a1,
  a.col_a2,
  b.col_b1,
  b.col_b2
FROM table_b b
LEFT JOIN table_a a 
  ON b.common_id = a.common_id
WHERE a.common_id IS NULL

注意事项 #

• 这里用 COALESCE(a.common_id, b.common_id) 可以保证关联列永远显示非空的结果，不会出现匹配失败时关联列为空的情况
• 如果确认两张表没有重复的公共列值，也可以把 UNION 换成 UNION ALL，省去去重步骤，查询性能更高
• 两段查询的列顺序、数据类型必须完全一致，否则会出现字段映射错误

内容的提问来源于stack exchange，提问作者Carl Eric Doromal