Oracle中获取两表差异的最优查询（需匹配指定输出格式）

表结构与数据 #

表AAA(NUM, TXT) #

1   One
2   too
3   Three
4   Four

表BBB(NUM, TXT) #

1   One
3   
4   Four
2   Two
5   Five

当前查询语句 #

select 'only A' where_, only_a.* from (select num, txt from AAA minus select num, txt from BBB) only_a union all
select 'only B' where_, only_b.* from (select num, txt from BBB minus select num, txt from AAA) only_b

当前输出问题 #

当前输出仅单独列出仅存在于A或B的行，以及内容不同的行，但无法直观对比同一NUM在两张表中的内容差异，展示形式不够清晰。

期望输出格式 #

期望将同一NUM的记录合并展示，清晰呈现三类差异：

• 内容不同：同一NUM对应的TXT值在两张表中不一致（含一方为空的情况）
• 仅存在于A：NUM仅在AAA表中存在
• 仅存在于B：NUM仅在BBB表中存在

输出示例结构：

最优Oracle查询语句 #

SELECT
    CASE
        WHEN a.num IS NULL THEN 'ONLY IN B'
        WHEN b.num IS NULL THEN 'ONLY IN A'
        ELSE 'MISMATCH'
    END AS TYPE,
    COALESCE(a.num, b.num) AS NUM,
    a.txt AS AAA_TXT,
    b.txt AS BBB_TXT
FROM AAA a
FULL OUTER JOIN BBB b 
    ON a.num = b.num
WHERE 
    (a.txt != b.txt OR (a.txt IS NULL AND b.txt IS NOT NULL) OR (a.txt IS NOT NULL AND b.txt IS NULL))
    OR a.num IS NULL
    OR b.num IS NULL
ORDER BY NUM;

说明 #

1. 使用FULL OUTER JOIN关联两张表，确保所有NUM的记录都被包含
2. 通过CASE表达式标记差异类型，清晰区分三类情况
3. COALESCE函数统一展示NUM值，避免因单边为空导致的缺失
4. WHERE子句过滤掉完全匹配的行（NUM和TXT都一致的记录）
5. 最终按NUM排序，使输出更规整易读

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