无主键外键的两张表对比查找缺失行的最优方法

核心逻辑是把整行全部字段的组合作为唯一识别标识，不需要额外设置主键外键就可以完成差异对比，根据使用场景可选以下方案：

数据库内直接查询方案（最常用） #

方案A：集合运算符查询（适配多数新版本数据库） #

支持PostgreSQL、SQL Server、SQLite 3.39.0+、MySQL 8.0.31+版本，写法最简单：

• 查找table1中存在但table2中缺失的行

SELECT id, h_id, role, l_name FROM table1
EXCEPT
SELECT id, h_id, role, l_name FROM table2;

• 查找table2中存在但table1中缺失的行，交换两个表的顺序即可

SELECT id, h_id, role, l_name FROM table2
EXCEPT
SELECT id, h_id, role, l_name FROM table1;

注意：如果需要保留重复行的差异（比如同一张表出现两条完全相同的行，另一张表只有一条），把EXCEPT替换为EXCEPT ALL即可。

方案B：UNION ALL分组计数（全数据库兼容） #

如果使用的是低版本MySQL、Oracle等不支持EXCEPT的数据库，用该方案可以实现同样效果：

SELECT 
    id, h_id, role, l_name,
    MAX(source_table) AS only_exists_in
FROM (
    SELECT id, h_id, role, l_name, 'table1' AS source_table FROM table1
    UNION ALL
    SELECT id, h_id, role, l_name, 'table2' AS source_table FROM table2
) AS combined_table
GROUP BY id, h_id, role, l_name
HAVING COUNT(*) = 1;

返回结果中only_exists_in字段会直接标注该缺失行属于哪张表。

百万级以上大表优化方案 #

如果表数据量特别大，全字段分组对比效率低，可以先计算整行哈希值作为唯一标识再做对比，性能提升至少5~10倍：

-- 示例为MySQL写法，其他数据库替换对应哈希函数即可
SELECT 
    MD5(CONCAT_WS('|', id, h_id, role, l_name)) AS row_hash,
    id, h_id, role, l_name
FROM table1
WHERE row_hash NOT IN (
    SELECT MD5(CONCAT_WS('|', id, h_id, role, l_name)) FROM table2
);

离线文件对比方案 #

如果已经把两张表导出为csv/tsv格式的本地文件，直接用命令行工具操作比查询数据库更快：

# 先对两个文件排序
sort table1.csv > table1_sorted.csv
sort table2.csv > table2_sorted.csv
# 三列输出分别为：仅table1存在的行、仅table2存在的行、两表共有的行
comm table1_sorted.csv table2_sorted.csv

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