如何利用SQL嵌套查询实现数据对账_通过EXISTS对比两系统差异-SQL-PHP中文网

应使用not exists替代not in以绕过null陷阱，正确写法为select order_id, customer_id from orders where not exists (select 1 from customers where customers.id = orders.customer_id)，并确保customers.id有索引。

如何利用sql嵌套查询实现数据对账_通过exists对比两系统差异

用 NOT EXISTS 查外键缺失，绕过 NULL 陷阱

校验订单表 orders.customer_id 是否全部在客户表 customers.id 中存在时，别写 WHERE customer_id IN (SELECT id FROM customers)。只要子查询里 customers.id 有一行是 NULL，整条条件就变成 UNKNOWN，该订单直接消失——不是漏查，是 SQL 三值逻辑的必然结果。

正确写法是用 NOT EXISTS，它只关心“有没有匹配行”，完全不碰 NULL 判定：

SELECT order_id, customer_id 
FROM orders 
WHERE NOT EXISTS (
  SELECT 1 FROM customers WHERE customers.id = orders.customer_id
);

子查询里固定写 SELECT 1，不写 SELECT *，避免字段传输开销和优化器误判
确保 customers.id 有索引，否则每次都要扫全表
这个语句查出来的是“在订单表里、但客户不存在”的脏数据，可直接进清洗队列

用 EXISTS + LEFT JOIN 替代相关子查询，防性能雪崩

比如要找“每笔订单金额大于该客户历史平均订单金额”的记录，别写：

WHERE amount > (SELECT AVG(amount) FROM orders o2 WHERE o2.customer_id = orders.customer_id)

这种相关子查询会让数据库对主表每行都执行一次子查询，10 万行订单就是 10 万次子查询，哪怕加了索引也扛不住。

改成先聚合再 JOIN：

SELECT o1.order_id, o1.amount
FROM orders o1
LEFT JOIN (
  SELECT customer_id, AVG(amount) AS avg_amt 
  FROM orders 
  GROUP BY customer_id
) t ON o1.customer_id = t.customer_id
WHERE o1.amount > t.avg_amt;

GROUP BY customer_id 必须有，否则子查询结果会膨胀成笛卡尔积
orders.customer_id 字段必须建索引，否则子查询阶段就慢
MySQL 8.0+ 可考虑用窗口函数 AVG() OVER (PARTITION BY customer_id)，更简洁且通常更快

跨系统对账时用 ABS 容差比对浮点总和

比对 A 系统明细表 sales_detail 的日汇总金额和 B 系统汇总表 daily_summary 的 total_amount，不能写：

WHERE (SELECT SUM(amount) FROM sales_detail WHERE date = '2026-05-05') = 
      (SELECT total_amount FROM daily_summary WHERE date = '2026-05-05')

浮点数存储有精度误差，= 几乎永远为假。业务上允许分（0.01）级误差，就用 ABS：

SELECT 
  (SELECT SUM(amount) FROM sales_detail WHERE DATE(created_at) = '2026-05-05') AS detail_sum,
  (SELECT total_amount FROM daily_summary WHERE date = '2026-05-05') AS summary_sum,
  ABS(
    (SELECT SUM(amount) FROM sales_detail WHERE DATE(created_at) = '2026-05-05') - 
    (SELECT total_amount FROM daily_summary WHERE date = '2026-05-05')
  ) AS diff
WHERE ABS(
  (SELECT SUM(amount) FROM sales_detail WHERE DATE(created_at) = '2026-05-05') - 
  (SELECT total_amount FROM daily_summary WHERE date = '2026-05-05')
) > 0.01;

所有子查询必须显式带上相同时间条件，否则数据库可能全表扫描再过滤
如果明细表按秒级时间戳存，汇总表按天分区，务必统一用 DATE(created_at) 而非 created_at >= '2026-05-05' AND created_at ，避免隐式类型转换
频繁执行这类对账，建议把子查询结果物化为临时表，避免重复计算

EXPLAIN 是唯一能验证你没写错的步骤

无论用 EXISTS 还是派生表，上线前必须跑 EXPLAIN 看执行计划。常见翻车点：

看到 Type: ALL 且 rows 数十万，说明连接字段没索引或用了函数（如 DATE(created_at) 没对字段建函数索引）
看到 Extra: Using temporary; Using filesort，说明子查询里写了 ORDER BY + LIMIT，MySQL 5.7 及更早版本会报错或退化成全表扫描
看到派生表（<derivedn></derivedn>）没走索引，可能是 MySQL 版本太低（5.6 默认不给派生表建索引），得手动建临时表

对账逻辑越复杂，越容易在执行计划里藏坑——不看 EXPLAIN 就上线，等于靠运气跑批处理。