使用MINUS运算符与CASE函数实现表差异ID标记遇问题求助

需求：找出table1与table2中互相不存在的ID，并为其标记对应状态（not in table2或not in table1）。尝试使用MINUS运算符和CASE函数实现，但编写的SQL无法得到预期结果，不清楚如何正确运用CASE函数。

输入数据 #

table1   table2
id        id 
1         2
2         3
3         4
4         8
5         9
7         6       

预期输出 #

编写的错误SQL #

select * from table1
minus 
select * from table2 
case when select * from table1
minus 
select * from table2 
then id
else not in table1
end as status

你的错误SQL存在核心语法与逻辑问题：MINUS的用法不符合规范，不能直接在其后续拼接CASE表达式；同时CASE的条件判断逻辑完全混乱，子查询与返回值的写法都不合法。

以下提供两种可行的实现方案：

方法1：UNION ALL + NOT EXISTS（兼容多数数据库） #

这种写法适配MySQL、Oracle、SQL Server等绝大多数数据库：

-- 提取table1中独有的ID并标记状态
SELECT id, 'not in table2' AS status
FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.id = table1.id)
UNION ALL
-- 提取table2中独有的ID并标记状态
SELECT id, 'not in table1' AS status
FROM table2
WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE table1.id = table2.id)
ORDER BY status, id;

方法2：MINUS + UNION（适用于Oracle等支持MINUS的数据库） #

先通过MINUS分别获取两张表的独有ID，再合并结果并标记状态：

SELECT id, 'not in table2' AS status
FROM (
    SELECT id FROM table1
    MINUS
    SELECT id FROM table2
) t1_unique
UNION
SELECT id, 'not in table1' AS status
FROM (
    SELECT id FROM table2
    MINUS
    SELECT id FROM table1
) t2_unique
ORDER BY status, id;

CASE函数的正确用法说明 #

如果想用CASE函数实现，可以先合并两张表的所有ID，再通过条件判断标记状态：

SELECT 
    id,
    CASE
        WHEN EXISTS (SELECT 1 FROM table1 WHERE table1.id = combined.id) 
             AND NOT EXISTS (SELECT 1 FROM table2 WHERE table2.id = combined.id) 
        THEN 'not in table2'
        WHEN EXISTS (SELECT 1 FROM table2 WHERE table2.id = combined.id) 
             AND NOT EXISTS (SELECT 1 FROM table1 WHERE table1.id = combined.id) 
        THEN 'not in table1'
    END AS status
FROM (
    SELECT id FROM table1
    UNION
    SELECT id FROM table2
) combined
WHERE 
    NOT EXISTS (SELECT 1 FROM table1 WHERE table1.id = combined.id)
    OR NOT EXISTS (SELECT 1 FROM table2 WHERE table2.id = combined.id)
ORDER BY status, id;

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