如何正确查询生效日期合规且仅含'S'合同类型的员工ID？

表结构及数据 #

-- EMP表
create table EMP(ID number(10), effective_date date);
-- 数据
EID    Effective_date
--------------------
1     02/14/2023
2     02/15/2023
3     04/30/2023
4     03/24/2023

-- TYPE表
create table type(ID number(10),contract_type varchar2(2));
-- 数据
TID  contract_type
------------------
1       P
1       S
1       P
2       S
2       S
3       P
3       S
4       S

查询需求 #

需要找出满足以下条件的EID：

• EMP表中effective_date大于等于当前日期（sysdate）
• 对应TYPE表中该ID的contract_type仅为'S'（无其他类型）

预期结果：

2
4

原查询的问题 #

原查询存在以下问题导致无法得到正确结果：

1. WHERE条件缺少逻辑运算符AND，存在语法错误
2. 仅筛选contract_type='S'的记录后统计数量，无法排除那些同时存在非'S'类型的ID（比如ID1包含'S'但也有'P'，会被错误纳入结果）
3. GROUP BY字段与SELECT字段不匹配，不符合SQL规范

修正后的查询方案 #

方案一：分组过滤非S类型 #

SELECT e.ID AS EID
FROM emp e
JOIN (
    -- 筛选出所有合同类型都是S的ID
    SELECT ID AS TID
    FROM type
    GROUP BY ID
    -- 统计非S类型的记录数为0，说明该ID没有其他类型
    HAVING COUNT(CASE WHEN contract_type != 'S' THEN 1 END) = 0
) t ON e.ID = t.TID
WHERE e.effective_date >= SYSDATE;

方案二：使用NOT EXISTS排除非S类型 #

SELECT e.ID AS EID
FROM emp e
WHERE e.effective_date >= SYSDATE
-- 确保该ID不存在非S的合同类型
AND NOT EXISTS (
    SELECT 1
    FROM type t
    WHERE t.ID = e.ID
    AND t.contract_type != 'S'
);

两种方案都可以得到预期结果，方案二更直观，在数据量较大时执行效率通常更优。

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