如何验证两张关联表中主键与外键的双向匹配关系？

核心方案 #

要同时检测两类不匹配情况（人类无对应动物、动物无对应人类），单左连接只能覆盖前者，所以得用全外连接（FULL OUTER JOIN）——它会保留两张表里所有未匹配的记录，再通过筛选条件把这些异常记录拎出来。

通用SQL查询（支持全外连接的数据库：PostgreSQL、SQL Server等） #

SELECT
    h.id AS human_id,
    h.name AS human_name,
    a.id AS animal_id,
    a.name AS animal_name,
    CASE
        WHEN h.id IS NULL THEN '动物无对应人类主人'
        WHEN a.id IS NULL THEN '人类无对应动物'
        ELSE '匹配正常'
    END AS mismatch_type
FROM Humans h
FULL OUTER JOIN Animals a ON h.id = a.human_id
WHERE h.id IS NULL OR a.id IS NULL;

逻辑解释 #

• FULL OUTER JOIN：把两张表的所有记录都纳入结果，不管彼此是否匹配
• WHERE 子句：只留下存在不匹配的记录——要么人类那边没对应的动物（a.id IS NULL），要么动物没对应的人类（h.id IS NULL）
• mismatch_type 字段直接标注异常类型，看结果一目了然

替代方案（MySQL等不支持全外连接的数据库） #

如果你的数据库不支持FULL OUTER JOIN，可以用UNION ALL合并两个左连接的结果，分别抓取两类异常：

-- 抓取无对应动物的人类
SELECT
    h.id AS human_id,
    h.name AS human_name,
    a.id AS animal_id,
    a.name AS animal_name,
    '人类无对应动物' AS mismatch_type
FROM Humans h
LEFT JOIN Animals a ON h.id = a.human_id
WHERE a.id IS NULL

UNION ALL

-- 抓取无对应人类的动物
SELECT
    h.id AS human_id,
    h.name AS human_name,
    a.id AS animal_id,
    a.name AS animal_name,
    '动物无对应人类主人' AS mismatch_type
FROM Animals a
LEFT JOIN Humans h ON a.human_id = h.id
WHERE h.id IS NULL;

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