SQL 兩表分組數據精確比對：EXCEPT 集合運算 vs 字符串拼接，性能實測

業務場景

有兩張結構相同的表 table_left、table_right，按 groupId 分組，每組包含多行 data。需求：找出 table_left 中哪些 groupId 在 table_right 中存在「完全相同的數據集」（行數、值、順序均一致）。

數學建模：集合視角

將每組視為集合，兩組「完全相等」等價於：

A ⊆ B 且 B ⊆ A ⇔ A \ B = ∅ 且 B \ A = ∅

SQL Server 的 EXCEPT 運算符天然實現「差集」，完美契合此邏輯。

方案一：字符串拼接法（傳統做法）

步驟

1. 給兩表各加一列 dataPath NVARCHAR(200) 存儲拼接串。
2. CROSS APPLY ... FOR XML PATH('') 將同組 data 按排序拼成 -data1-data2-data3。
3. 更新回表，建立非聚簇索引。
4. WHERE EXISTS (SELECT 1 FROM table_right WHERE dataPath = table_left.dataPath) 即可匹配。

完整代碼片段

ALTER TABLE table_left  ADD dataPath NVARCHAR(200);
ALTER TABLE table_right ADD dataPath NVARCHAR(200);

UPDATE a SET dataPath = b.dataPath
FROM table_left a
CROSS APPLY (
    SELECT (SELECT '-' + x.data FROM table_left x WHERE x.groupId = a.groupId ORDER BY x.data FOR XML PATH('')) AS dataPath
) b;

UPDATE a SET dataPath = b.dataPath
FROM table_right a
CROSS APPLY (
    SELECT (SELECT '-' + x.data FROM table_right x WHERE x.groupId = a.groupId ORDER BY x.data FOR XML PATH('')) AS dataPath
) b;

CREATE NONCLUSTERED INDEX ix_left  ON table_left (dataPath);
CREATE NONCLUSTERED INDEX ix_right ON table_right(dataPath);

SELECT DISTINCT a.groupId
FROM table_left a
WHERE EXISTS (SELECT 1 FROM table_right x WHERE x.dataPath = a.dataPath);

方案二：EXCEPT 集合運算法（推薦）

核心邏輯

SELECT DISTINCT a.groupId
FROM table_left a
INNER JOIN table_right b ON b.data = a.data  -- 先按單行值過濾，大幅縮小候選集
WHERE NOT EXISTS (
    SELECT x.data FROM table_left  x WHERE x.groupId = a.groupId
    EXCEPT
    SELECT y.data FROM table_right y WHERE y.groupId = b.groupId
)
AND NOT EXISTS (
    SELECT x.data FROM table_right x WHERE x.groupId = b.groupId
    EXCEPT
    SELECT y.data FROM table_left  y WHERE y.groupId = a.groupId
);

雙向 EXCEPT 皆為空 ⇔ 兩組多重集完全相等。

性能實測對比（圖 5、6）

進階：多列、長文本場景的擴展

當 data 超過 900 字節或需比對多列（如 dataSub1, dataSub2），引入計算列校驗和 + 索引：

ALTER TABLE table_left  ADD dataChecksum AS CHECKSUM(dataSub1, dataSub2);
ALTER TABLE table_right ADD dataChecksum AS CHECKSUM(dataSub1, dataSub2);
CREATE NONCLUSTERED INDEX ix_left_cs  ON table_left (dataChecksum);
CREATE NONCLUSTERED INDEX ix_right_cs ON table_right(dataChecksum);

SELECT DISTINCT a.groupId
FROM table_left a
INNER JOIN table_right b
    ON b.dataChecksum = a.dataChecksum
   AND b.dataSub1 = a.dataSub1
   AND b.dataSub2 = a.dataSub2
WHERE NOT EXISTS (
    SELECT x.dataSub1, x.dataSub2 FROM table_left  x WHERE x.groupId = a.groupId
    EXCEPT
    SELECT y.dataSub1, y.dataSub2 FROM table_right y WHERE y.groupId = b.groupId
)
AND NOT EXISTS (
    SELECT x.dataSub1, x.dataSub2 FROM table_right x WHERE x.groupId = b.groupId
    EXCEPT
    SELECT y.dataSub1, y.dataSub2 FROM table_left  y WHERE y.groupId = a.groupId
);

CHECKSUM 做快速預過濾，EXCEPT 做精確比對，兼顧性能與準確性。

總結建議

1. 首選 EXCEPT 方案：代碼簡潔、無副作用、性能優、天然支援多列、無長度限制。
2. 僅在極舊版本（SQL 2000 不支援 EXCEPT）或極特殊場景下退而求其次用拼接法。
3. 大數據量時，確保比對列上有索引，INNER JOIN ... ON b.data = a.data 能大幅縮小候選組數。