5-29 having句でスカラー問い合わせ

SQLパズル

t3テーブル
gr  name
--  ----
 1   A
 1   B
 1   C
 2   D
 2   C
 3   X

t4テーブル
name
----
 A
 C

t4テーブルの全ての要素(この場合はAとC)を持つ、t3テーブルのgrを出力する。

出力結果
gr
--
 1

データ作成スクリプト

create Table t3(
gr   number(1),
name char(1));

create Table t4(name char(1));

insert into t3(gr,name) values(1,'A');
insert into t3(gr,name) values(1,'B');
insert into t3(gr,name) values(1,'C');
insert into t3(gr,name) values(2,'D');
insert into t3(gr,name) values(2,'C');
insert into t3(gr,name) values(3,'X');
insert into t4(name) values('A');
insert into t4(name) values('C');
commit;

SQL

--■■■grごとに、t4との共通集合の要素数が、t4の要素数と一致するかチェック■■■
select gr from t3 a
where exists(select 1 from t4 b
              where b.name=a.name)
group by gr
having count(a.name) = (select count(b.name) from t4 b);

--■■■grごとに、t4との差集合が空集合かチェック(minus演算子で差集合を求める)■■■
select distinct gr from t3 a
where not exists(select b.name from t4 b
                  minus select c.name from t3 c
                         where c.gr = a.gr);

--■■■grごとに、t4との差集合が空集合かチェック(not existsで差集合を求める)■■■
select distinct gr from t3 a
where not exists(select 1 from t4 b
                  where not exists(select 1 from t3 c
                                    where c.gr = a.gr
                                      and c.name = b.name));

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select b.gr
  from t4 a
  Left Join t3 b
 partition by (b.gr)
    on (a.name = b.name)
group by b.gr
having count(*) = count(b.name);

解説

having句でもスカラー問い合わせが使用できます。

exists述語の引数に、
集合同士の和集合や、集合同士の差集合や、集合同士の共通集合
などの集合演算の結果を使うことができます。

9-9 差集合が空集合かチェック
CodeZine：分析関数の衝撃（完結編）

---