双向校验两张表差异：为何EXCEPT仅返回hotel，需同时返回wc

当前的EXCEPT语句只能筛选出data表存在但poi_equipement关联结果中没有的记录（也就是缺失数据hotel），没法拿到poi_equipement关联结果存在但data表没有的冗余数据（wc）。要同时返回两类差异并明确来源，推荐两种实现方式：

方法一：使用全外连接（FULL OUTER JOIN） #

先把两个表的目标数据单独提取出来，再通过全外连接匹配，筛选出两边不重合的记录，并用CASE标记差异来源：

WITH data_objects AS (
    SELECT object AS item
    FROM data
    WHERE subject = 'url1'
),
poi_subjects AS (
    SELECT e.subject AS item
    FROM poi_equipement pe
    INNER JOIN equipement e ON pe.equipement_id = e.id
)
SELECT 
    COALESCE(d.item, p.item) AS item,
    CASE
        WHEN d.item IS NULL THEN '仅存在于poi_equipement（冗余数据）'
        WHEN p.item IS NULL THEN '仅存在于data（缺失数据）'
    END AS difference_source
FROM data_objects d
FULL OUTER JOIN poi_subjects p ON d.item = p.item
WHERE d.item IS NULL OR p.item IS NULL;

方法二：用UNION ALL结合两次EXCEPT #

分别筛选出两类差异，再合并结果：

-- 提取data表有但poi_equipement没有的缺失数据
SELECT 
    object AS item,
    '仅存在于data（缺失数据）' AS difference_source
FROM data
WHERE subject = 'url1'
EXCEPT
SELECT 
    e.subject AS item,
    '仅存在于data（缺失数据）' AS difference_source
FROM poi_equipement pe
INNER JOIN equipement e ON pe.equipement_id = e.id

UNION ALL

-- 提取poi_equipement有但data没有的冗余数据
SELECT 
    e.subject AS item,
    '仅存在于poi_equipement（冗余数据）' AS difference_source
FROM poi_equipement pe
INNER JOIN equipement e ON pe.equipement_id = e.id
EXCEPT
SELECT 
    object AS item,
    '仅存在于poi_equipement（冗余数据）' AS difference_source
FROM data
WHERE subject = 'url1';

两种方法都能同时返回hotel和wc，并清晰标记每条差异属于缺失还是冗余，以及对应的表。

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