如何找出新旧两张数据表的增删改数据差异？附SQL代码片段

嘿，我看你正在折腾新旧两个商品数据表的差异对比——旧表ITEMS有16000行，新表ITEMS_032818多了2000行左右，想精准找出新增、删除和修改的数据对吧？我来帮你把那段没写完的SQL补全，再给你一套完整的差异定位方案。

1. 定位新表中新增的数据 #

新增数据指的是只在新表ITEMS_032818里存在、旧表ITEMS没有的记录（这里默认UPC是唯一标识字段）。你的那段SQL可以补全成这样：

SELECT UPC, Name 
FROM ITEMS_032818 AS ND 
WHERE NOT EXISTS (
    SELECT 1  -- 这里不用选UPC/Name，选1查询效率更高
    FROM ITEMS AS OD 
    WHERE ND.UPC = OD.UPC
);

如果你习惯用LEFT JOIN写法，这个版本有时候性能更优：

SELECT ND.UPC, ND.Name
FROM ITEMS_032818 AS ND
LEFT JOIN ITEMS AS OD ON ND.UPC = OD.UPC
WHERE OD.UPC IS NULL;

2. 定位旧表中删除的数据（新表中已移除的旧记录） #

这部分是找旧表有、但新表已经没有的行，写法和新增查询刚好反过来：

SELECT UPC, Name 
FROM ITEMS AS OD 
WHERE NOT EXISTS (
    SELECT 1
    FROM ITEMS_032818 AS ND 
    WHERE OD.UPC = ND.UPC
);

同样可以用LEFT JOIN实现：

SELECT OD.UPC, OD.Name
FROM ITEMS AS OD
LEFT JOIN ITEMS_032818 AS ND ON OD.UPC = ND.UPC
WHERE ND.UPC IS NULL;

3. 定位修改的数据（UPC存在于两张表，但字段内容有变化） #

如果是对比Name字段的修改，SQL可以这么写：

SELECT 
    OD.UPC,
    OD.Name AS Old_Name,
    ND.Name AS New_Name
FROM ITEMS AS OD
JOIN ITEMS_032818 AS ND ON OD.UPC = ND.UPC
WHERE OD.Name <> ND.Name;

注意：如果Name字段可能存在NULL值，要额外处理NULL的对比情况，避免遗漏：

SELECT 
    OD.UPC,
    OD.Name AS Old_Name,
    ND.Name AS New_Name
FROM ITEMS AS OD
JOIN ITEMS_032818 AS ND ON OD.UPC = ND.UPC
WHERE (OD.Name <> ND.Name) 
   OR (OD.Name IS NULL AND ND.Name IS NOT NULL) 
   OR (OD.Name IS NOT NULL AND ND.Name IS NULL);

要是有其他字段（比如价格、库存）需要对比，直接在WHERE条件里添加对应的字段判断即可。

额外实用技巧 #

• 确保UPC是唯一标识字段，不然会出现重复匹配，影响结果准确性。
• 数据量较大时，给UPC字段建立索引能大幅提升查询速度。
• 可以把三类差异合并成一个结果集，方便统一查看：

-- 新增数据
SELECT '新增' AS Change_Type, UPC, Name AS Current_Name, NULL AS Old_Name
FROM ITEMS_032818 AS ND 
WHERE NOT EXISTS (SELECT 1 FROM ITEMS AS OD WHERE ND.UPC = OD.UPC)

UNION ALL

-- 删除数据
SELECT '删除' AS Change_Type, UPC, NULL AS Current_Name, Name AS Old_Name
FROM ITEMS AS OD 
WHERE NOT EXISTS (SELECT 1 FROM ITEMS_032818 AS ND WHERE OD.UPC = ND.UPC)

UNION ALL

-- 修改数据
SELECT '修改' AS Change_Type, OD.UPC, ND.Name AS Current_Name, OD.Name AS Old_Name
FROM ITEMS AS OD
JOIN ITEMS_032818 AS ND ON OD.UPC = ND.UPC
WHERE OD.Name <> ND.Name;

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