如何基于ID和Indicator查询两张表的差异记录？

需求概述 #

需要从两张表中筛选出以下两类记录，排除仅在单张表中存在的ID：

1. 相同ID和Indicator但Value不同的记录
2. 相同ID下，某张表存在另一张表没有的Indicator对应的记录

示例数据 #

declare @table1 table (id int, indicator varchar(20),value int);
INSERT INTO @table1 VALUES
(11,'AC',80),
(11,'HE',90),
(12,'AC',10),
(12,'HE',80),
(13,'AC',10),
(13,'HE',10);


declare @table2 table(id int, indicator varchar(20),value int);
INSERT INTO @table2 VALUES
(11,'AC',80),
(11,'HE',90),
(12,'AC',11),
(12,'HE',80),
(13,'AC',10),
(14,'AC',10);

场景说明 #

• ID 11在两张表中ID、Indicator、Value完全匹配，无需返回
• ID 12的Indicator 'AC'在两张表中Value分别为10和11，属于差异记录，需返回
• ID 13在两张表中都存在，但表1的Indicator 'HE'在表2中无对应记录，需返回；若表2存在此类情况，需显示表2记录，表1对应字段为NULL
• 仅在单张表存在的ID（如14），直接排除

期望结果 #

解决方案SQL #

SELECT
    t1.id AS [Table 1 ID],
    t1.indicator AS [Table 1 Indicator],
    t1.value AS [Table 1 Value],
    t2.id AS [Table 2 ID],
    t2.indicator AS [Table 2 Indicator],
    t2.value AS [Table 2 Value]
FROM @table1 t1
FULL OUTER JOIN @table2 t2
    ON t1.id = t2.id AND t1.indicator = t2.indicator
-- 过滤出两张表都存在的ID
WHERE EXISTS (SELECT 1 FROM @table1 WHERE id = COALESCE(t1.id, t2.id))
  AND EXISTS (SELECT 1 FROM @table2 WHERE id = COALESCE(t1.id, t2.id))
-- 筛选差异条件：要么Value不同，要么某一方无匹配
AND (
    t1.value <> t2.value
    OR t1.id IS NULL
    OR t2.id IS NULL
)
ORDER BY COALESCE(t1.id, t2.id), COALESCE(t1.indicator, t2.indicator);

代码说明 #

1. 用FULL OUTER JOIN关联两张表，关联条件为id和indicator，覆盖所有匹配与不匹配的情况
2. 通过EXISTS子句仅保留两张表都存在的ID，排除单表独有的ID
3. 筛选逻辑包含三类差异：
   • 双方匹配但value不一致
   • 表2有记录但表1无对应匹配（表1字段为NULL）
   • 表1有记录但表2无对应匹配（表2字段为NULL）
4. 按ID和Indicator排序，让结果更规整

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