Oracle SQL技术问询：筛选未拥有package_table中任何套餐的用户

需求 #

仅筛选出未拥有package表中任何套餐的用户（示例目标用户user_id = 4）。

现有表结构 #

套餐表 package #

create table package (pack_id, name) as
 select   1,name_1   from dual union all
select   2, name_2 from dual union all
select   3, name_3  from dual ;

用户表 user #

create table user (user_id, pack_id) as
 select   1,  2  from dual union all
select  1, 3 from dual union all
select  2, 1 from dual  union all
select  3, 3 from dual union all
select  3, 2 from dual union all
select  3, 1 from dual union all
select  4, 6 from dual ;

尝试的无效SQL语句 #

select id,pack
from 
(select id,decode(sign(sum(decode(pack_id,'1','2','3',1,0)))1,'Yes','No') pack
from user
group by id)
where pack = 'No'

问题：该语句无效，反而返回了拥有package表中套餐的用户。

问题分析 #

原SQL存在多处语法和逻辑错误：

1. Decode语法错误：decode(pack_id,'1','2','3',1,0) 不符合decode的语法规则，正确的多值匹配需依次指定「值-结果」对，最后补充默认值。
2. 语法冗余错误：sign(sum(...))1 多了一个无意义的1，导致语法不合法。
3. 逻辑颠倒：即使语法修正，原逻辑的标记规则与筛选条件不匹配，最终会把拥有有效套餐的用户错误筛选出来。

正确解法 #

方法1：使用NOT EXISTS（性能最优） #

通过子查询检查用户的所有套餐ID都不在有效套餐列表中：

SELECT DISTINCT u.user_id
FROM "user" u
WHERE NOT EXISTS (
    SELECT 1
    FROM package p
    WHERE p.pack_id = u.pack_id
);

方法2：左连接+空值筛选 #

将用户表与套餐表左连接，筛选未匹配到有效套餐的用户：

SELECT DISTINCT u.user_id
FROM "user" u
LEFT JOIN package p ON u.pack_id = p.pack_id
WHERE p.pack_id IS NULL;

方法3：分组统计判断 #

统计每个用户拥有的有效套餐数量，筛选数量为0的用户：

SELECT u.user_id
FROM "user" u
GROUP BY u.user_id
HAVING SUM(CASE WHEN u.pack_id IN (SELECT pack_id FROM package) THEN 1 ELSE 0 END) = 0;

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