如何检测两张同结构表中Name对应surname/time的差异及缺失项

现有两张结构相同的表A和B，仅数据可能存在差异。需求是检测两张表中：

• 同一Name对应的surname和/或time是否存在差异
• 某张表中存在的缺失记录

原尝试使用左连接查询，但返回错误结果，需修正逻辑。

原表数据 #

表A #

Name surname time
1427  4624   06-12-2020
1427  5272   07-13-2021
2642  5281   03-12-2022
…

表B #

Name surname time
1427 4624    06-12-2021
1427 5273    07-13-2021
2642 5281    03-12-2022
…

原查询及问题 #

原左连接查询语句：

Select distinct a.name, a.surname as surname_a, b.surname as surname_b
From a
Left join b
On a.name=b.name
Where surname_a<>surname_b
And a.time<>b.time

全量数据集下返回错误结果，例如：

Name surname_a surname_b
1427  4624      5273
1427  5273      4624

而期望输出应为：

Name surname 
1427 4624      (因日期不同)
1427 5272      (表B中缺失)
1427 5273      (表A中缺失)

详细示例数据 #

表A #

表B #

期望结果 #

说明：期望结果包含两张表中time存在差异的记录，以及任一表中缺失的记录。

解决方案 #

推荐两种准确实现需求的方法：

方法1：全外连接筛选差异与缺失 #

通过全外连接匹配Name和surname，筛选出单侧缺失或time不一致的记录：

SELECT 
    COALESCE(a.Name, b.Name) AS Name,
    COALESCE(a.surname, b.surname) AS surname,
    COALESCE(a.time, b.time) AS time,
    CASE
        WHEN a.Name IS NULL THEN '仅存在于表B'
        WHEN b.Name IS NULL THEN '仅存在于表A'
        ELSE 'time存在差异'
    END AS 差异说明
FROM a
FULL OUTER JOIN b
    ON a.Name = b.Name 
    AND a.surname = b.surname
WHERE 
    a.Name IS NULL 
    OR b.Name IS NULL 
    OR a.time <> b.time

方法2：UNION ALL合并缺失与差异记录 #

分别查询两张表中对方不存在的记录，再合并结果：

-- 表A存在但表B不存在的记录
SELECT Name, surname, time, '仅存在于表A' AS 差异说明
FROM a
WHERE NOT EXISTS (
    SELECT 1 
    FROM b 
    WHERE b.Name = a.Name AND b.surname = a.surname AND b.time = a.time
)
UNION ALL
-- 表B存在但表A不存在的记录
SELECT Name, surname, time, '仅存在于表B' AS 差异说明
FROM b
WHERE NOT EXISTS (
    SELECT 1 
    FROM a 
    WHERE a.Name = b.Name AND a.surname = b.surname AND a.time = b.time
)

两种方法均能返回符合预期的结果，方法2逻辑更直观，便于维护。

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