9-27 集合の包含関係を調べる

SQLパズル

TableA     TableB     TableC     TableD
Val        Val        Val        Val
---        ---        ---        ---
AAA        AAA        AAA        AAA
BBB        BBB        BBB        BBB
CCC        CCC                   CCC
                                 DDD

TableAとTableB、
TableAとTableC、
TableAとTableD、
の包含関係を調べる。

各集合に重複した要素(重複行)は、
存在しないものとする。

出力結果
TableB  TableC  TableD
------  ------  ------
A=B     A⊃C    A⊂D

SQLパズル(日本語版)のパズル26 [等集合の発見] を参考にさせていただきました
SQLパズル 第2版のパズル27 [等しい集合を見つける] を参考にさせていただきました

データ作成スクリプト

create table TableA(Val varchar2(3));
create table TableB(Val varchar2(3));
create table TableC(Val varchar2(3));
create table TableD(Val varchar2(3));

insert all
into tableA values('AAA')
into tableA values('BBB')
into tableA values('CCC')
into TableB values('AAA')
into TableB values('BBB')
into TableB values('CCC')
into TableC values('AAA')
into TableC values('BBB')
into TableD values('AAA')
into TableD values('BBB')
into TableD values('CCC')
into TableD values('DDD')
select 1 from dual;
commit;

SQL

select
case when not exists(select Val,count(*) over() from TableA
                      minus
                     select Val,count(*) over() from TableB) then 'A=B'
     when not exists(select Val from TableA
                      minus
                     select Val from TableB) then 'A⊂B'
     when not exists(select Val from TableB
                      minus
                     select Val from TableA) then 'A⊃B' end as TableB,
case when not exists(select Val,count(*) over() from TableA
                      minus
                     select Val,count(*) over() from TableC) then 'A=C'
     when not exists(select Val from TableA
                      minus
                     select Val from TableC) then 'A⊂C'
     when not exists(select Val from TableC
                      minus
                     select Val from TableA) then 'A⊃C' end as TableC,
case when not exists(select Val,count(*) over() from TableA
                      minus
                     select Val,count(*) over() from TableD) then 'A=D'
     when not exists(select Val from TableA
                      minus
                     select Val from TableD) then 'A⊂D'
     when not exists(select Val from TableD
                      minus
                     select Val from TableA) then 'A⊃D' end as TableD
from dual;

解説

not existsで差集合が空集合か調べてます。

集合の一致
ふたつの集合ＡとＢに対して、ＡとＢとが等しいとは、
Ａ⊂Ｂ　と　Ｂ⊂Ａ　が両方とも成り立つときをいう。
またこのときＡ＝Ｂと書く。
だそうですが

(集合Ａに重複した要素が存在しない、かつ、集合Ｂに重複した要素が存在しない)
かつ
(Ａ⊂Ｂ、または、Ｂ⊃Ａが成立する)
かつ
集合Ａと集合Ｂの要素数が等しい

と、Ａ＝Ｂも同値です。

なので、select句で、count(*) over()
で要素数を求めて、minusで要素数を比較してます

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

ただし、例外があって、
TableAが空集合(レコードがない)の場合に、必ず、'A=B'となってしまいます。

case when not exists(select Val,count(*) over() from TableA
                      minus
                     select Val,count(*) over() from TableB) then 'A=B'

なので、TableAまたはTableBが空集合の場合を考慮すると

case when not exists(select Val,count(*) over() from TableA
                      minus
                     select Val,count(*) over() from TableB)
      and not exists(select Val,count(*) over() from TableB
                      minus
                     select Val,count(*) over() from TableA) then 'A=B'

となります。
そして、空集合を考慮したSQLは、こうなります。

select
case when not exists(select Val,count(*) over() from TableA
                      minus
                     select Val,count(*) over() from TableB)
      and not exists(select Val,count(*) over() from TableB
                      minus
                     select Val,count(*) over() from TableA) then 'A=B'
     when not exists(select Val from TableA
                      minus
                     select Val from TableB) then 'A⊂B'
     when not exists(select Val from TableB
                      minus
                     select Val from TableA) then 'A⊃B' end as TableB,
case when not exists(select Val,count(*) over() from TableA
                      minus
                     select Val,count(*) over() from TableC)
      and not exists(select Val,count(*) over() from TableC
                      minus
                     select Val,count(*) over() from TableA) then 'A=C'
     when not exists(select Val from TableA
                      minus
                     select Val from TableC) then 'A⊂C'
     when not exists(select Val from TableC
                      minus
                     select Val from TableA) then 'A⊃C' end as TableC,
case when not exists(select Val,count(*) over() from TableA
                      minus
                     select Val,count(*) over() from TableD)
      and not exists(select Val,count(*) over() from TableD
                      minus
                     select Val,count(*) over() from TableA) then 'A=D'
     when not exists(select Val from TableA
                      minus
                     select Val from TableD) then 'A⊂D'
     when not exists(select Val from TableD
                      minus
                     select Val from TableA) then 'A⊃D' end as TableD
from dual;

---

CodeZine：SQLで集合演算
CodeZine：分析関数の衝撃（完結編）

---