WITH T AS (SELECT 1 MEMSEQ, 180 GROUPSEQ FROM DUAL UNION ALL SELECT 1 MEMSEQ, 250 GROUPSEQ FROM DUAL UNION ALL SELECT 2 MEMSEQ, 180 GROUPSEQ FROM DUAL UNION ALL SELECT 3 MEMSEQ, 180 GROUPSEQ FROM DUAL UNION ALL SELECT 3 MEMSEQ, 250 GROUPSEQ FROM DUAL) SELECT * FROM ( SELECT MEMSEQ, GROUPSEQ, COUNT(*) OVER (PARTITION BY MEMSEQ) CNT FROM T WHERE GROUPSEQ IN (180, 250) ) WHERE CNT = 1 AND GROUPSEQ = 180;
WITH t AS ( SELECT 1 memberSeq, 180 groupSeq UNION ALL SELECT 1, 250 UNION ALL SELECT 2, 180 UNION ALL SELECT 3, 180 UNION ALL SELECT 3, 250 ) -- 1. Group By & Having SELECT COUNT(*) FROM (SELECT memberSeq FROM t WHERE groupSeq IN (180, 250) GROUP BY memberSeq HAVING COUNT(CASE WHEN groupSeq = 250 THEN 1 END) = 0 ) a ; -- 2. Not In SELECT COUNT(*) FROM t a WHERE a.groupSeq = 180 AND a.memberSeq NOT IN (SELECT b.memberSeq FROM t b WHERE b.groupSeq = 250 ) ; -- 3. Not Exists SELECT COUNT(*) FROM t a WHERE a.groupSeq = 180 AND NOT EXISTS (SELECT 1 FROM t b WHERE b.memberSeq = a.memberSeq AND b.groupSeq = 250 ) ; -- 4. Outer Join & Is Null SELECT COUNT(*) FROM t a LEFT OUTER JOIN t b ON b.memberSeq = a.memberSeq AND b.groupSeq = 250 WHERE a.groupSeq = 180 AND b.groupSeq IS NULL ;