如何在无主键的两张近似表中找出不匹配行及差异列？

现有两张结构近似的表，均无唯一键或主键列，需要识别出两张表中不匹配的行，以及具体存在差异的列（需求可视化示例见附图）。

尝试过多种方法，比如下方的示例SQL语句，但无法得到精准结果；也曾尝试包含空值检查的左/右/外连接，但当表中存在空值时，匹配逻辑仍会失效，求更优解决方案。

用户示例代码 #

IF OBJECT_ID(N'tempdb..#Table2') IS NOT NULL
BEGIN
    DROP TABLE #Table2
END
GO
CREATE TABLE #Table2
(
     Column1 int,
     Column2 varchar(50), 
     Column3 varchar(50),
     Column4 varchar(50)
)
Insert into #Table2 (Column1, Column2, Column3, Column4) values (1, 'CC1-1', 'GL1-1', 'ON1-1')
Insert into #Table2 (Column1, Column2, Column3, Column4) values (1, 'CC1-1', 'GL1-1', 'ON1-4')
Insert into #Table2 (Column1, Column2, Column3, Column4) values (1, 'CC1-2', 'GL1-2', 'ON1-2')
Insert into #Table2 (Column1, Column2, Column3, Column4) values (1, 'CC1-6', 'GL1-6', 'ON1-6')
Insert into #Table2 (Column1, Column2, Column3, Column4) values (2, 'CC1-6', 'GL1-6', NULL)  
IF OBJECT_ID(N'tempdb..#Table1') IS NOT NULL
BEGIN
    DROP TABLE #Table1
END
GO
CREATE TABLE #Table1
(
     Column1 int,
     Column2 varchar(50), 
     Column3 varchar(50),
     Column4 varchar(50)
)
Insert into #Table1 (Column1, Column2, Column3, Column4) values (1, 'CC1-1', 'GL1-1', 'ON1-1')
Insert into #Table1 (Column1, Column2, Column3, Column4) values (1, 'CC1-1', 'GL1-1', 'ON1-4')
Insert into #Table1 (Column1, Column2, Column3, Column4) values (1, 'CC1-2', 'GL1-2', 'ON1-2')
Insert into #Table1 (Column1, Column2, Column3, Column4) values (1, 'CC1-6', 'GL1-6', 'ON1-6')
Insert into #Table2 (Column1, Column2, Column3, Column4) values (2, 'CC1-2', 'GL1-2', null) 

select * from #Table2
select * from #Table1
select Stg.*, Inv.*,
    case when isnull(Stg.Column2, '') = isnull(Inv.Column2, '') 
        and isnull(Stg.Column3, '') = isnull(Inv.Column3, '') 
        and isnull(Stg.Column4, '') = isnull(Inv.Column4, '') then 'Matched'
    else 'Not matching: ' +
        case when isnull(Stg.Column2, '') <> isnull(Inv.Column2, '') then 'Column2,' else '' end +
        case when isnull(Stg.Column3, '') <> isnull(Inv.Column3, '') then 'Column3,' else '' end + 
        case when isnull(Stg.Column4, '') <> isnull(Inv.Column4, '') then 'Column4' else '' end
    end as 'Remarks'
from #Table1 Stg
left join #Table2 Inv on Stg.Column1 = Inv.Column1 
    and (isnull(Stg.Column2, '') = isnull(Inv.Column2, '')
    or isnull(Stg.Column3, '') = isnull(Inv.Column3, '')
    or isnull(Stg.Column4, '') = isnull(Inv.Column4, ''))

解决方案 #

方法1：全列对比+UNION ALL定位差异行 #

通过UNION ALL合并两张表的所有行，统计每行出现次数，次数为1的是仅存在于单张表的差异行；对两张表都存在但列值不同的行，逐列对比标记差异列。

-- 找出仅存在于单张表的行
WITH AllRows AS (
    SELECT 'Table1' AS SourceTable, Column1, Column2, Column3, Column4 FROM #Table1
    UNION ALL
    SELECT 'Table2' AS SourceTable, Column1, Column2, Column3, Column4 FROM #Table2
),
RowCounts AS (
    SELECT Column1, Column2, Column3, Column4, COUNT(*) AS RowCount
    FROM AllRows
    GROUP BY Column1, Column2, Column3, Column4
)
SELECT ar.SourceTable, ar.Column1, ar.Column2, ar.Column3, ar.Column4, '仅存在于当前表' AS Remarks
FROM AllRows ar
JOIN RowCounts rc ON ar.Column1 = rc.Column1 
    AND ISNULL(ar.Column2, '') = ISNULL(rc.Column2, '')
    AND ISNULL(ar.Column3, '') = ISNULL(rc.Column3, '')
    AND ISNULL(ar.Column4, '') = ISNULL(rc.Column4, '')
WHERE rc.RowCount = 1

UNION ALL

-- 找出两张表都存在但列值不同的行（Table1 vs Table2）
SELECT 
    'Table1' AS SourceTable,
    t1.Column1, t1.Column2, t1.Column3, t1.Column4,
    '与Table2差异列：' + 
    STRING_AGG(CASE 
        WHEN ISNULL(t1.Column2, '') <> ISNULL(t2.Column2, '') THEN 'Column2'
        WHEN ISNULL(t1.Column3, '') <> ISNULL(t2.Column3, '') THEN 'Column3'
        WHEN ISNULL(t1.Column4, '') <> ISNULL(t2.Column4, '') THEN 'Column4'
    END, ', ') AS Remarks
FROM #Table1 t1
JOIN #Table2 t2 ON t1.Column1 = t2.Column1
WHERE NOT (
    ISNULL(t1.Column2, '') = ISNULL(t2.Column2, '')
    AND ISNULL(t1.Column3, '') = ISNULL(t2.Column3, '')
    AND ISNULL(t1.Column4, '') = ISNULL(t2.Column4, '')
)
GROUP BY t1.Column1, t1.Column2, t1.Column3, t1.Column4

UNION ALL

-- 找出两张表都存在但列值不同的行（Table2 vs Table1）
SELECT 
    'Table2' AS SourceTable,
    t2.Column1, t2.Column2, t2.Column3, t2.Column4,
    '与Table1差异列：' + 
    STRING_AGG(CASE 
        WHEN ISNULL(t2.Column2, '') <> ISNULL(t1.Column2, '') THEN 'Column2'
        WHEN ISNULL(t2.Column3, '') <> ISNULL(t1.Column3, '') THEN 'Column3'
        WHEN ISNULL(t2.Column4, '') <> ISNULL(t1.Column4, '') THEN 'Column4'
    END, ', ') AS Remarks
FROM #Table2 t2
JOIN #Table1 t1 ON t2.Column1 = t1.Column1
WHERE NOT (
    ISNULL(t2.Column2, '') = ISNULL(t1.Column2, '')
    AND ISNULL(t2.Column3, '') = ISNULL(t1.Column3, '')
    AND ISNULL(t2.Column4, '') = ISNULL(t1.Column4, '')
)
ORDER BY SourceTable, Column1;

方法2：HASHBYTES生成行哈希快速定位差异 #

对每行所有列生成哈希值，先快速找出哈希不同的行，再进一步对比具体列，适合数据量较大的场景。

-- 生成每张表的行哈希值
WITH Table1Hash AS (
    SELECT 
        Column1, Column2, Column3, Column4,
        HASHBYTES('SHA2_256', 
            CONCAT(
                ISNULL(CAST(Column1 AS VARCHAR(50)), ''), '|',
                ISNULL(Column2, ''), '|',
                ISNULL(Column3, ''), '|',
                ISNULL(Column4, '')
            )
        ) AS RowHash
    FROM #Table1
),
Table2Hash AS (
    SELECT 
        Column1, Column2, Column3, Column4,
        HASHBYTES('SHA2_256', 
            CONCAT(
                ISNULL(CAST(Column1 AS VARCHAR(50)), ''), '|',
                ISNULL(Column2, ''), '|',
                ISNULL(Column3, ''), '|',
                ISNULL(Column4, '')
            )
        ) AS RowHash
    FROM #Table2
)
-- 找出哈希不同的行并标记差异列
SELECT 
    'Table1' AS SourceTable,
    t1.Column1, t1.Column2, t1.Column3, t1.Column4,
    '与Table2差异列：' + 
    STRING_AGG(CASE 
        WHEN ISNULL(t1.Column2, '') <> ISNULL(t2.Column2, '') THEN 'Column2'
        WHEN ISNULL(t1.Column3, '') <> ISNULL(t2.Column3, '') THEN 'Column3'
        WHEN ISNULL(t1.Column4, '') <> ISNULL(t2.Column4, '') THEN 'Column4'
    END, ', ') AS Remarks
FROM Table1Hash t1
JOIN Table2Hash t2 ON t1.Column1 = t2.Column1
WHERE t1.RowHash <> t2.RowHash
GROUP BY t1.Column1, t1.Column2, t1.Column3, t1.Column4

UNION ALL

-- 找出仅存在于Table1的行
SELECT 'Table1' AS SourceTable, Column1, Column2, Column3, Column4, '仅存在于Table1' AS Remarks
FROM Table1Hash t1
WHERE NOT EXISTS (
    SELECT 1 FROM Table2Hash t2 
    WHERE t1.RowHash = t2.RowHash AND t1.Column1 = t2.Column1
)

UNION ALL

-- 找出仅存在于Table2的行
SELECT 'Table2' AS SourceTable, Column1, Column2, Column3, Column4, '仅存在于Table2' AS Remarks
FROM Table2Hash t2
WHERE NOT EXISTS (
    SELECT 1 FROM Table1Hash t1 
    WHERE t2.RowHash = t1.RowHash AND t2.Column1 = t1.Column1
)
ORDER BY SourceTable, Column1;

关键注意事项 #

• 空值处理：用ISNULL(列, '')将空值转为空字符串，避免SQL中NULL = NULL不成立的逻辑问题；
• 差异列拼接：STRING_AGG适用于SQL Server 2017及以上版本，低版本可改用多个CASE语句拼接差异列；
• 关联键选择：示例中用Column1作为关联键，实际需根据业务场景选择最能代表行唯一性的字段组合，若无可靠关联键，可直接用全列分组统计出现次数。

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