5-16 exists同士の論理積

SQLパズル

tblMember
メンバID   名前
--------   ----
   A       山田
   B       佐藤
   C       山下
   D       上田

tbl組
組ID  メンバID
----  --------
  1       A
  2       A
  2       B
  3       A
  3       C
  4       C
  5       C

1組,2組,3組の全てに属するメンバーを出力する。

出力結果
メンバID  名前
--------  ----
    A     山田

データ作成スクリプト

create table tblMember(
メンバID char(1),
名前 char(4));

create table tbl組(
組ID number,
メンバID char(1));

insert into tblMember(メンバID,名前) values('A','山田');
insert into tblMember(メンバID,名前) values('B','佐藤');
insert into tblMember(メンバID,名前) values('C','山下');
insert into tblMember(メンバID,名前) values('D','上田');
insert into tbl組(組ID,メンバID) values(1,'A');
insert into tbl組(組ID,メンバID) values(2,'B');
insert into tbl組(組ID,メンバID) values(2,'A');
insert into tbl組(組ID,メンバID) values(3,'A');
insert into tbl組(組ID,メンバID) values(3,'C');
insert into tbl組(組ID,メンバID) values(4,'C');
insert into tbl組(組ID,メンバID) values(5,'C');
commit;

SQL

--■■■existsを使用する方法■■■
select メンバID,名前 from tblMember a
where exists(select 1 from tbl組 b
              where b.メンバID = a.メンバID
                and b.組ID=1)
  and exists(select 1 from tbl組 b
              where b.メンバID = a.メンバID
                and b.組ID=2)
  and exists(select 1 from tbl組 b
              where b.メンバID = a.メンバID
                and b.組ID=3);

--■■■intersectを使用して、共通集合を求める方法■■■
select メンバID,名前 from tblMember a
where メンバID
in(select b.メンバID from tbl組 b where b.組ID=1 intersect
   select b.メンバID from tbl組 b where b.組ID=2 intersect
   select b.メンバID from tbl組 b where b.組ID=3);

--■■■分析関数を使う方法■■■
select distinct メンバID,名前
from (select a.メンバID,a.名前,
      count(case when b.組ID in(1,2,3) then b.組ID end)
      over(partition by a.メンバID) as カウント
      from tblMember a,tbl組 b
      where a.メンバID = b.メンバID)
where カウント = 3;

--■■■having句でブール代数を使う方法■■■
select a.メンバID,a.名前
  from tblMember a,tbl組 b
 where a.メンバID = b.メンバID
group by a.RowID,a.メンバID,a.名前
having max(case when b.組ID = 1 then 1 else 0 end)
     * max(case when b.組ID = 2 then 1 else 0 end)
     * max(case when b.組ID = 3 then 1 else 0 end) = 1;

解説

existsは、共通集合で代用でき、
not existsは、差集合で代用できます。

---