如何合并含公共与非公共列的两表记录并去重保留指定字段值

原语句问题分析 #

• 未以Patient ID、Check_in_Date、order_name作为重复判断的核心键，反而依赖可能重复的Name字段，且未包含Patient_ID，导致重复判断逻辑错误。
• 为table2的preformed_by赋值空字符串后，当两表存在相同核心键的记录时，会因preformed_by值不同被UNION保留，最终产生重复记录。

方法一：筛选table2独有记录后合并（高效去重） #

先获取table1的全部记录，再补充table2中未在table1出现过的记录（按核心键匹配），用UNION ALL避免额外去重开销：

-- 取table1所有记录
SELECT Patient_ID, Name, Check_in_Date, order_name, preformed_by
FROM table1
UNION ALL
-- 取table2中与table1无匹配核心键的记录
SELECT t2.Patient_ID, t2.Name, t2.Check_in_Date, t2.order_name, '' AS preformed_by
FROM table2 t2
WHERE NOT EXISTS (
    SELECT 1
    FROM table1 t1
    WHERE t1.Patient_ID = t2.Patient_ID
      AND t1.Check_in_Date = t2.Check_in_Date
      AND t1.order_name = t2.order_name
)

方法二：全外连接+COALESCE（逻辑直观） #

通过全外连接合并两表所有记录，再用COALESCE优先选择table1的字段值，确保核心键重复时保留table1的preformed_by：

SELECT 
    COALESCE(t1.Patient_ID, t2.Patient_ID) AS Patient_ID,
    COALESCE(t1.Name, t2.Name) AS Name,
    COALESCE(t1.Check_in_Date, t2.Check_in_Date) AS Check_in_Date,
    COALESCE(t1.order_name, t2.order_name) AS order_name,
    COALESCE(t1.preformed_by, '') AS preformed_by
FROM table1 t1
FULL OUTER JOIN table2 t2
    ON t1.Patient_ID = t2.Patient_ID
    AND t1.Check_in_Date = t2.Check_in_Date
    AND t1.order_name = t2.order_name

方案说明 #

• 方法一适合数据量较大的场景，NOT EXISTS的过滤逻辑效率更高，且UNION ALL无需额外去重操作。
• 方法二逻辑更清晰，能直观体现两表的对应关系，适合需要排查数据匹配情况的场景。

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