如何查询两张含共同PK的表中彼此缺失的values数据

问题描述 #

现有两张表Table A和Table B，二者拥有共同的pk字段，需要生成指定的预期结果集，以此找出两张表中彼此缺失的values值。此前尝试分别使用左连接和右连接编写两个查询，但未能得到预期结果。

数据表结构 #

Table A #

Table B #

预期结果 #

解决方案 #

要实现这个需求，需要使用全外连接（FULL OUTER JOIN），同时关联pk和values字段（注意处理values中的空格差异，比如用TRIM()函数统一格式），最后按pk和values排序，确保结果顺序符合预期。

SQL 查询语句 #

SELECT
    COALESCE(a.pk, b.pk) AS pk,
    a.values AS a.value,
    b.values AS b.value
FROM TableA a
FULL OUTER JOIN TableB b
    ON a.pk = b.pk 
    AND TRIM(a.values) = TRIM(b.values)
ORDER BY 
    COALESCE(a.pk, b.pk),
    COALESCE(a.values, b.values);

逻辑说明 #

1. 全外连接：同时保留Table A和Table B中的所有记录，匹配的记录合并，不匹配的记录则对应字段显示NULL，正好满足“找出彼此缺失值”的需求。
2. COALESCE函数：用于统一获取pk值（当其中一张表没有对应记录时，取另一张表的pk），同时保证排序的一致性。
3. TRIM处理：解决两张表中values字段的空格差异（比如Table A中的Value A和Table B中的Value A），确保匹配逻辑准确。
4. 排序：按pk和values排序，让结果顺序和预期一致。

如果你的数据库不支持FULL OUTER JOIN（比如MySQL），可以用左连接 + 右连接的结果合并来模拟：

SELECT
    a.pk AS pk,
    a.values AS a.value,
    b.values AS b.value
FROM TableA a
LEFT JOIN TableB b
    ON a.pk = b.pk 
    AND TRIM(a.values) = TRIM(b.values)

UNION ALL

SELECT
    b.pk AS pk,
    NULL AS a.value,
    b.values AS b.value
FROM TableB b
LEFT JOIN TableA a
    ON a.pk = b.pk 
    AND TRIM(a.values) = TRIM(b.values)
WHERE a.pk IS NULL

ORDER BY pk, COALESCE(a.value, b.value);

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