未知主键时如何对比两张结构相似表的差异

我有两张结构完全相同的production和development表，想要对比二者的差异。已知主键时，我用下面的SQL查询找出production中存在但development中不存在的记录：

SELECT
  *
FROM
  `production` AS prod
WHERE
  NOT EXISTS (
  SELECT
    *
  FROM
    `non-production` AS nonprod
  WHERE
    nonprod.server = prod.server
    AND nonprod.id = prod.id)
ORDER BY partition_date

但现在要处理未知主键的表，请问该怎么编写同类对比查询？是否需要匹配所有字段？

解决方案 #

当没有主键可以依赖时，确实需要通过匹配所有字段来判断两条记录是否完全一致——因为没有主键的情况下，唯一能确定两条记录是同一条的依据就是所有字段值完全相同。下面提供两种实用的查询写法：

写法1：用NOT EXISTS匹配所有字段 #

将两张表的所有字段逐一对应进行等值匹配，示例如下（请替换为你表中的实际全部字段）：

SELECT
  *
FROM
  `production` AS prod
WHERE
  NOT EXISTS (
  SELECT
    *
  FROM
    `development` AS dev
  WHERE
    dev.server = prod.server
    AND dev.id = prod.id
    AND dev.name = prod.name
    AND dev.partition_date = prod.partition_date)
ORDER BY prod.partition_date

如果表中有NULL值，注意SQL中NULL = NULL结果为UNKNOWN，此时可以用IS NOT DISTINCT FROM（部分SQL方言支持）替代=，确保NULL值也能被正确匹配。

写法2：用EXCEPT简化查询（推荐） #

大部分主流SQL方言（如BigQuery、PostgreSQL、SQL Server等）支持EXCEPT关键字，它会自动对比两张表的所有字段（要求字段顺序、类型完全一致，正好符合你的表结构相同的前提），直接返回production独有的记录：

SELECT * FROM `production`
EXCEPT
SELECT * FROM `development`
ORDER BY partition_date

这种写法无需手动罗列所有字段，代码更简洁，且大部分方言会自动处理NULL值的匹配问题，出错概率更低。

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