SQL JOIN如何找出两张表完全匹配的数据-SQL-PHP中文网

要查两表所有字段值（含null）完全一致的行，必须显式比对每个字段并用(t1.c = t2.c or (t1.c is null and t2.c is null))处理null，或在postgresql中使用is not distinct from；仅on主键关联不等于整行相同，且重复行会导致笛卡尔积假阳性。

sql join如何找出两张表完全匹配的数据

直接用 INNER JOIN 不行——它只按 ON 条件关联，不是“整行完全相同”。要查两表中**所有字段值（含 NULL）完全一致的行**，必须显式比对每个字段，并正确处理 NULL 语义。

为什么 ON t1.id = t2.id 不等于“数据完全相同”

INNER JOIN 的 ON 子句只控制关联逻辑，不负责字段内容比对。哪怕两表结构一模一样，只写 ON t1.id = t2.id，结果也只是按主键匹配的记录，其他字段可能完全不同。

常见错误：以为 SELECT * FROM a INNER JOIN b ON a.id = b.id 就能找出“完全相同的行”，实际只是把 a 和 b 中 id 相同的行拼在一起，name、email 等字段是否相等完全没检查
更危险的是：如果业务上允许 id 重复（比如非主键字段），那这个 JOIN 还会放大结果集，产生笛卡尔积
真正“完全匹配”意味着：每一列的值都相等，且 NULL 和 NULL 要算作相等（SQL 标准中 NULL = NULL 是 UNKNOWN，不是 TRUE）

如何写安全的全字段 NULL 感知比对

在 MySQL、SQL Server、SQLite 等不支持 IS NOT DISTINCT FROM 的数据库中，必须手动展开每个字段的 NULL 安全判断：

对每个字段 c，写成：(t1.c = t2.c OR (t1.c IS NULL AND t2.c IS NULL))
所有字段条件用 AND 连接，缺一个就漏判
字段多时容易出错，建议先查元数据：SELECT column_name FROM information_schema.columns WHERE table_name = 'table_a' ORDER BY ordinal_position，再拼条件
别用 COALESCE(t1.c, '') = COALESCE(t2.c, '')——类型不一致时会误判（比如 0 和空字符串都转成 ''）

示例（三字段）：

SELECT t1.* 
FROM table_a t1 
INNER JOIN table_b t2 
  ON (t1.name = t2.name OR (t1.name IS NULL AND t2.name IS NULL))
 AND (t1.age = t2.age OR (t1.age IS NULL AND t2.age IS NULL))
 AND (t1.city = t2.city OR (t1.city IS NULL AND t2.city IS NULL));

PostgreSQL 用户可用 IS NOT DISTINCT FROM 简化

PostgreSQL 支持标准 SQL 的 IS NOT DISTINCT FROM，语义明确且可读性高：

它天然处理 NULL：两个 NULL 返回 TRUE，NULL 和非 NULL 返回 FALSE
写法简洁：t1.col IS NOT DISTINCT FROM t2.col 等价于上面的手动展开
但注意：不能混用，比如 t1.id IS NOT DISTINCT FROM t2.id AND t1.name = t2.name —— 后者仍不处理 NULL

示例：

SELECT t1.* 
FROM table_a t1 
INNER JOIN table_b t2 
  ON t1.id IS NOT DISTINCT FROM t2.id
 AND t1.name IS NOT DISTINCT FROM t2.name
 AND t1.amount IS NOT DISTINCT FROM t2.amount;

别忽略重复数据导致的假阳性

即使所有字段都比对一致，如果某张表里存在完全重复的行（比如无主键、无唯一约束），INNER JOIN 会生成组合爆炸：

表 A 有 2 行完全相同，表 B 也有 2 行完全相同 → JOIN 结果是 4 行，而非 2 行
这时 COUNT(*) 不代表“有多少条不同数据”，只代表匹配组合数
若目标是确认“两表内容集合是否相等”，应先去重：SELECT DISTINCT * FROM table_a 和 SELECT DISTINCT * FROM table_b 再比对，或改用 EXCEPT / UNION ALL 配合计数验证

真正难的不是写对语法，而是厘清“完全匹配”在你场景里到底指什么：是行级精确副本？还是业务意义上等价？前者必须逐字段 + NULL 安全 + 去重；后者往往需要领域逻辑介入，SQL 只能打底。