如何在SQL中使用FULL JOIN完美合并两套系统的核对账单？-SQL-PHP中文网

MySQL不支持FULL JOIN，PostgreSQL/SQL Server虽支持但UNION去重会丢失账单差异；须用LEFT+RIGHT+UNION ALL模拟，构造含时间、金额、收款方的业务主键，金额比较用ABS差值，时间字段需统一时区。

如何在sql中使用full join完美合并两套系统的核对账单？

SQL里根本没有FULL JOIN的“完美”实现

绝大多数生产环境用的MySQL 5.7/8.0默认不支持FULL JOIN语法，直接写会报错ERROR 1064 (42000)。PostgreSQL和SQL Server虽支持，但语义上它只是LEFT JOIN和RIGHT JOIN结果的UNION去重合并——而“核对账单”恰恰最怕隐式去重：同一笔交易在两边系统里可能因时间戳精度、金额四舍五入、备注字段空格等产生微小差异，直接UNION会把本该对比的两条记录吞掉一条。

用LEFT + RIGHT + UNION ALL模拟FULL JOIN时必须加唯一标识

真实账单核对不能依赖id或order_no做JOIN条件，因为两套系统编号规则往往不同。必须先构造业务主键，比如：CONCAT(date, ABS(ROUND(amount, 2)), COALESCE(payee, ''))。然后按这个主键做模拟：

SELECT 'left_only' AS source, a.* FROM system_a a
LEFT JOIN system_b b ON a.biz_key = b.biz_key
WHERE b.biz_key IS NULL
UNION ALL
SELECT 'right_only', b.* FROM system_b b
LEFT JOIN system_a a ON b.biz_key = a.biz_key
WHERE a.biz_key IS NULL
UNION ALL
SELECT 'both', a.*, b.* FROM system_a a
INNER JOIN system_b b ON a.biz_key = b.biz_key
WHERE ABS(a.amount - b.amount) > 0.01 OR a.status != b.status;

关键点：

biz_key必须包含足够区分度的字段组合，避免不同交易被误判为同一笔
金额比较必须用ABS(a.amount - b.amount) > 0.01而非!=，浮点误差和会计四舍五入会导致直接相等判断失效
用UNION ALL而非UNION，保留所有差异行，不丢数据

Oracle和PostgreSQL的FULL JOIN语法陷阱

即使数据库支持FULL JOIN，也别直接用。Oracle 12c+中FULL JOIN在WHERE子句里过滤NULL值时，容易因执行计划优化导致逻辑错误；PostgreSQL 14中若JOIN字段含NULL，FULL JOIN会把两边都为NULL的记录当成匹配项——而账单里“付款方为空”和“收款方为空”是完全不同的业务含义。

稳妥做法是显式补全：

SELECT COALESCE(a.id, b.id) AS id,
       a.amount AS a_amount, b.amount AS b_amount,
       a.status AS a_status, b.status AS b_status
FROM system_a a
FULL JOIN system_b b ON a.biz_key = b.biz_key
WHERE NOT (a.biz_key IS NOT NULL AND b.biz_key IS NOT NULL 
           AND a.amount = b.amount AND a.status = b.status);

注意：

COALESCE(a.id, b.id)确保ID不为空，但要意识到这掩盖了哪边缺失
WHERE条件必须包住整个匹配逻辑，不能只写a.amount != b.amount，否则NULL参与比较结果恒为UNKNOWN，记录被意外过滤

核对结果导出前务必检查NULL传播路径

账单字段如currency、exchange_rate在任一系统中为NULL时，用FULL JOIN或模拟方案后，对应字段会变成NULL。但核对报表里需要明确标注是“系统A无此字段”还是“系统B未同步”，而不是让下游ETL脚本把NULL当0处理。

建议统一用CASE表达式归因：

CASE 
  WHEN a.currency IS NULL AND b.currency IS NOT NULL THEN 'missing_in_A'
  WHEN a.currency IS NOT NULL AND b.currency IS NULL THEN 'missing_in_B'
  ELSE 'matched'
END AS currency_status

真正麻烦的是时间字段：created_at在MySQL里存DATETIME，另一系统用TIMESTAMP WITH TIME ZONE，直接JOIN会因时区转换丢失精度。必须先用CONVERT_TZ()或AT TIME ZONE对齐到同一时区再生成biz_key。

业务主键没对齐，后面所有“完美合并”都是空中楼阁。