SQL实现两表关联合并 解决左表无对应值时关联失效问题

问题原因 #

你当前的查询存在两个核心问题：

1. 关联条件错误：仅用language_id关联会导致同语言下不同key的条目出现笛卡尔积，你需要同时按language_id和key两个字段匹配两条目的对应关系
2. 关联类型错误：INNER JOIN只会保留两张表同时存在匹配关系的条目，无法覆盖仅在单侧表存在的记录

解决方案 #

方案1：FULL OUTER JOIN实现（支持PostgreSQL、SQL Server、Oracle、MySQL 8.0.31+等支持全外连接的数据库）

SELECT 
  COALESCE(c.language_id, cc.language_id) AS language_id,
  COALESCE(c.key, cc.key) AS `key`,
  COALESCE(cc.text, c.text) AS text
FROM clients c
FULL OUTER JOIN custom_clients cc 
  ON c.language_id = cc.language_id 
  AND c.key = cc.key
ORDER BY language_id, `key`;

逻辑说明：

• 全外连接会保留两张表的所有条目，按language_id+key匹配对应条目
• COALESCE函数会优先取非空值，COALESCE(cc.text, c.text)实现了同匹配规则下custom_clients的text优先级高于clients的需求

方案2：UNION ALL + 窗口函数实现（兼容性更强，支持所有主流数据库版本）

SELECT language_id, `key`, text
FROM (
  SELECT 
    language_id, 
    `key`, 
    text,
    ROW_NUMBER() OVER (
      PARTITION BY language_id, `key` 
      ORDER BY priority DESC
    ) AS rn
  FROM (
    SELECT language_id, `key`, text, 1 AS priority FROM clients
    UNION ALL
    SELECT language_id, `key`, text, 2 AS priority FROM custom_clients
  ) t
) t2
WHERE rn = 1
ORDER BY language_id, `key`;

逻辑说明：

• 先把两张表的所有条目合并，给custom_clients设置更高的优先级
• 按language_id+key分组，每组取优先级最高的条目，实现冲突时优先取custom_clients配置的需求

两种方案都可以得到你期望的输出结果。

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