最常用且可控的方式是用LEFT JOIN连接主表和历史表，再通过COALESCE或IS DISTINCT FROM显式比对字段差异，避免NULL导致误判，且差异判断须置于SELECT或WHERE中而非ON子句。
用LEFT JOIN对比主表和历史表的字段差异
直接用 LEFT JOIN 把当前主表和历史快照表连起来，是最常用也最可控的方式。关键不是“能不能连”，而是连完怎么识别哪些字段变了——得靠显式列比对，不能依赖 WHERE a.col != b.col 这种写法，因为 NULL 一出现就全失效。
实操建议：
- 所有参与比对的字段都用
COALESCE(a.col, '') != COALESCE(b.col, '')或更稳妥的(a.col IS DISTINCT FROM b.col)（PostgreSQL 支持，语义上天然处理 NULL） - 别在
ON子句里写字段比对逻辑，那会改变连接基数；差异判断必须放在SELECT或WHERE中 - 如果历史表有多个版本，先用子查询或 CTE 限定只取最新一条历史记录（比如按
version_id或updated_at降序LIMIT 1）
MySQL中用LATERAL JOIN模拟逐行历史拉取（8.0.14+）
MySQL 8.0.14 起支持 LATERAL，能解决“每条主表记录要匹配自己最近的历史版本”这类典型场景。以前只能靠相关子查询或窗口函数模拟，性能差还难读。
实操建议：
- 语法上
LATERAL必须跟在JOIN后，且右侧子查询可引用左侧表字段，例如：LATERAL (SELECT * FROM history h WHERE h.ref_id = main.id ORDER BY h.effective_at DESC LIMIT 1) - 务必给
history(ref_id, effective_at)加联合索引，否则LATERAL会退化成嵌套循环扫描 - 注意
LATERAL不是所有 MySQL 版本都可用，低于 8.0.14 会报错Unknown syntax，别硬套
避免用FULL OUTER JOIN做跨版本合并（兼容性陷阱）
FULL OUTER JOIN 看似适合“找出当前有、历史无，或历史有、当前无”的情况，但实际几乎没法安全落地：MySQL 根本不支持，SQLite 需模拟，SQL Server/Oracle 虽支持，但一旦历史表缺失某主键，结果集会出现大量 NULL 行，后续过滤极易漏判。
更稳的做法是分两步：
- 用
LEFT JOIN找出“当前存在但历史缺失”的记录（h.id IS NULL） - 用
RIGHT JOIN或反向LEFT JOIN找出“历史存在但当前已删除”的记录（m.id IS NULL） - 再用
UNION ALL合并，比强求一个FULL OUTER JOIN更清晰、更可控
WHERE条件写在JOIN外还是内？影响结果集本质
历史对比类查询里，WHERE 放错位置会导致“该比的没比上”。比如想查“过去30天内变更过的字段”，如果把时间条件写在 ON 里：ON h.ref_id = m.id AND h.updated_at >= NOW() - INTERVAL 30 DAY，那没历史记录的主表行会被直接过滤掉；而写在外部 WHERE，又会让 LEFT JOIN 退化成 INNER JOIN。
正确做法只有两个：
- 时间条件留在
ON子句，但主表行必须保留，所以LEFT JOIN后不要加WHERE h.ref_id IS NOT NULL - 真要筛“有变更”的记录，改用
EXISTS子查询：EXISTS (SELECT 1 FROM history h WHERE h.ref_id = m.id AND h.updated_at >= ...)，语义明确且不干扰连接逻辑
历史版本对比真正难的不是语法，是搞清“比什么”和“以谁为基准”。字段级差异容易算，但业务上哪些变化算有效变更、NULL 算不算变化、时间窗口怎么定，这些得在 SQL 外就想清楚。不然写一堆 COALESCE 和 IS DISTINCT FROM，最后发现比的压根不是业务关心的点。
