跨表匯入資料時需比對多欄位組合是否重複(複合主鍵場景)。直接 GROUP BY HAVING COUNT(*)>1 僅能找出重複,無法直接過濾「來源表有、目標表無」的差集資料。本文介紹多欄位串聯比對法,並解決 NULL 導致串聯失效的經典陷阱。
來源表 t1、目標表 t2,依 (col1, col2, col3) 判斷唯一性。需找出 t1 中「在 t2 不存在」的組合並插入。
Oracle 中 NULL || 'anything' = NULL,若任一欄位為 NULL,整串串聯結果變 NULL,導致比對失效。解法:每欄位先用 NVL(col, '') 轉為空字串再串聯。
INSERT INTO t2 (col1, col2, col3, ...)
SELECT col1, col2, col3, ...
FROM t1
WHERE NVL(col1,'') || '|' || NVL(col2,'') || '|' || NVL(col3,'')
NOT IN (
SELECT NVL(col1,'') || '|' || NVL(col2,'') || '|' || NVL(col3,'')
FROM t2
);
'|'、'#')避免「1+23」與「12+3」串聯衝突。NVL(col, '') 僅適用字串型欄位;數值/日期型需 NVL(TO_CHAR(col), '')。NOT IN 子查詢可能效能不佳,改用 NOT EXISTS 或 LEFT JOIN ... WHERE t2.pk IS NULL。MERGE INTO t2 target
USING (
SELECT col1, col2, col3, other_cols
FROM t1
) source
ON (NVL(target.col1,'') = NVL(source.col1,'')
AND NVL(target.col2,'') = NVL(source.col2,'')
AND NVL(target.col3,'') = NVL(source.col3,''))
WHEN NOT MATCHED THEN
INSERT (col1, col2, col3, other_cols)
VALUES (source.col1, source.col2, source.col3, source.other_cols);
MERGE 單陳述式完成「查找-插入」,原子性更強、計畫更穩定。WHEN MATCHED THEN UPDATE 同步更新異動欄位。t2(col1, col2, col3) 建立複合唯一索引,加速查找並強制唯一性。ORA_HASH 或 DBMS_CRYPTO.HASH 產生指紋欄位索引,比對指紋而非原文。ANALYZE / DBMS_STATS 確保統計資訊準確。多欄位串聯比對是處理複合鍵去重的經典技巧,關鍵在於統一分隔符與NULL 轉空字串,配合 MERGE 與複合索引可構建高效、正確的資料同步管線。