如何不使用NOT IN实现符合指定筛选条件的SQL查询

现有Student表结构 #

Id   companyId    status
----------------------------------------
101  1001         In-Progress
102  1001         In-Progress
103  1001         Final
104  1002         In-Progress   
105  1003         Pending With Company 
106  1003         In-Progress
107  1004         In-Progress
108  1004         In-Progress 
109  1005         In-Progress
110  1005         Completed
111  1006         In-Progress
112  1006         Canceled
113  1007         In-Progress
114  1007         Pending with Student 

筛选条件 #

• 单条记录本身的status为In-Progress
• 对应companyId下的所有记录的status只能是In-Progress、Completed、Canceled三种，不能出现Final、Pending With Company、Pending with Student这类非法状态

预期输出 #

Id   companyId    status
--------------------------------
104  1002         In-Progress   
107  1004         In-Progress
108  1004         In-Progress 
109  1005         In-Progress
111  1006         In-Progress

原有NOT IN实现方案 #

SELECT * 
FROM student
WHERE status = 'In-Progress' 
  AND companyId NOT IN (SELECT companyId FROM student 
                        WHERE status IN ('Final', 'Pending With Company ', 'Pending with Student'));

不使用NOT IN的实现方案 #

方案1：使用NOT EXISTS实现（兼容性最佳，空值场景下表现更稳定） #

SELECT * 
FROM student s1
WHERE s1.status = 'In-Progress'
  AND NOT EXISTS (
    SELECT 1 
    FROM student s2 
    WHERE s2.companyId = s1.companyId
      AND s2.status IN ('Final', 'Pending With Company ', 'Pending with Student')
);

方案2：使用LEFT JOIN + IS NULL实现 #

SELECT s1.*
FROM student s1
LEFT JOIN student s2 
  ON s1.companyId = s2.companyId
  AND s2.status IN ('Final', 'Pending With Company ', 'Pending with Student')
WHERE s1.status = 'In-Progress'
  AND s2.Id IS NULL;

方案3：使用GROUP BY + 条件聚合实现 #

SELECT s1.*
FROM student s1
INNER JOIN (
  SELECT companyId
  FROM student
  GROUP BY companyId
  HAVING SUM(CASE WHEN status IN ('Final', 'Pending With Company ', 'Pending with Student') THEN 1 ELSE 0 END) = 0
) valid_companies 
ON s1.companyId = valid_companies.companyId
WHERE s1.status = 'In-Progress';

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