如何无需显式列名全外连接两表验证数据一致性？

一、支持ON *语法的数据库（如PostgreSQL） #

如果你的数据库支持ON *语法（自动匹配两表同名列作为连接条件），可以直接用类似你期望的SQL，还能扩展统计整行差异：

SELECT
  COUNT(1) AS total_rows,
  SUM(CASE WHEN table_a.id IS NULL THEN 1 ELSE 0 END) AS only_in_table_b,
  SUM(CASE WHEN table_b.id IS NULL THEN 1 ELSE 0 END) AS only_in_table_a,
  -- 统计整行数据存在差异的行数（含NULL值的正确比较）
  SUM(CASE WHEN table_a IS DISTINCT FROM table_b THEN 1 ELSE 0 END) AS differing_rows
FROM
  table_a
FULL OUTER JOIN
  table_b
ON
  *;

说明：

• ON *会自动把两表所有同名列作为连接条件，不用手动逐个输入
• table_a IS DISTINCT FROM table_b能准确判断整行是否有差异，解决=无法识别NULL相等的问题
• 判断数据一致的标准：only_in_table_a和only_in_table_b都为0（两表主键/唯一键集合完全匹配），且differing_rows为0（所有匹配行的列值完全一致）

二、不支持ON *语法的数据库（如MySQL、SQL Server） #

这类数据库需要动态生成包含所有列的连接条件，以下是具体实现：

MySQL示例（8.0+）

-- 自动生成连接条件字符串
SELECT GROUP_CONCAT(
  CONCAT('table_a.', COLUMN_NAME, ' <=> table_b.', COLUMN_NAME)
  SEPARATOR ' AND '
) INTO @join_condition
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '你的数据库名'
  AND TABLE_NAME = 'table_a';

-- 构建并执行全外连接查询
SET @sql = CONCAT('
SELECT
  COUNT(1) AS total_rows,
  SUM(CASE WHEN table_a.id IS NULL THEN 1 ELSE 0 END) AS only_in_table_b,
  SUM(CASE WHEN table_b.id IS NULL THEN 1 ELSE 0 END) AS only_in_table_a,
  SUM(CASE WHEN NOT (', @join_condition, ') THEN 1 ELSE 0 END) AS differing_rows
FROM table_a
FULL OUTER JOIN table_b
ON ', @join_condition, ';');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server示例

DECLARE @join_condition NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 自动生成连接条件
SELECT @join_condition = STRING_AGG(
  CONCAT('table_a.', QUOTENAME(COLUMN_NAME), ' = table_b.', QUOTENAME(COLUMN_NAME)),
  ' AND '
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' -- 替换为你的schema
  AND TABLE_NAME = 'table_a';

-- 构建并执行查询语句
SET @sql = N'
SELECT
  COUNT(1) AS total_rows,
  SUM(CASE WHEN table_a.id IS NULL THEN 1 ELSE 0 END) AS only_in_table_b,
  SUM(CASE WHEN table_b.id IS NULL THEN 1 ELSE 0 END) AS only_in_table_a,
  SUM(CASE WHEN NOT (' + @join_condition + ') THEN 1 ELSE 0 END) AS differing_rows
FROM table_a
FULL OUTER JOIN table_b
ON ' + @join_condition + ';';

EXEC sp_executesql @sql;

说明：

• 通过系统表INFORMATION_SCHEMA.COLUMNS获取所有列名，自动拼接成连接条件
• MySQL用<=>替代=，可正确处理NULL值比较；SQL Server 2022+可用IS NOT DISTINCT FROM替代=实现NULL值的正确比较

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