9-26 存在しない組み合わせを出力

SQLパズル

InOutTable
InData  OutData
------  -------
InAAAA  OutBBB
InAAAA  OutCCC
InBBBB  OutDDD
InCCCC  OutAAA
InCCCC  OutDDD
InDDDD  OutAAA
InDDDD  OutBBB

InOutTableの、InDataとOutDataの全ての組み合わせの中で
InOutTableに存在しない組み合わせを出力する

出力結果
InData  OutData
------  -------
InAAAA  OutAAA
InAAAA  OutDDD
InBBBB  OutAAA
InBBBB  OutBBB
InBBBB  OutCCC
InCCCC  OutBBB
InCCCC  OutCCC
InDDDD  OutCCC
InDDDD  OutDDD

SQLパズル(日本語版)のパズル25 [データフローダイアグラム] を参考にさせていただきました
SQLパズル 第2版のパズル26 [DFD] を参考にさせていただきました

データ作成スクリプト

create table InOutTable(
InData  varchar2(6) not null,
OutData varchar2(6) not null);

insert into InOutTable values('InAAAA','OutBBB');
insert into InOutTable values('InAAAA','OutCCC');
insert into InOutTable values('InBBBB','OutDDD');
insert into InOutTable values('InCCCC','OutAAA');
insert into InOutTable values('InCCCC','OutDDD');
insert into InOutTable values('InDDDD','OutAAA');
insert into InOutTable values('InDDDD','OutBBB');
commit;

SQL

--■■■分析関数を使う方法■■■
select distinct InData,OutData
from (select a.InData,b.OutData,
      count(distinct a.OutData) over(partition by a.InData) as 結合可能数,
      count(distinct a.OutData) over(partition by a.InData,b.OutData) as 結合数
        from InOutTable a,InOutTable b
      where a.OutData != b.OutData)
where 結合可能数 = 結合数
order by InData,OutData;

--■■■not inを使う方法■■■
select distinct a.InData,b.OutData
  from InOutTable a,InOutTable b
 where a.OutData != b.OutData
   and (a.InData,b.OutData)
   not in (select c.InData,c.OutData
             from InOutTable c)
order by InData,OutData;

--■■■not existsを使う方法■■■
select distinct a.InData,b.OutData
  from InOutTable a,InOutTable b
 where a.OutData != b.OutData
   and not exists(select 1 from InOutTable c
                   where c.InData  = a.InData
                     and c.OutData = b.OutData)
order by InData,OutData;

--■■■minusを使う方法■■■
select a.InData,b.OutData
  from InOutTable a,InOutTable b
 where a.OutData != b.OutData
minus select c.InData,c.OutData from InOutTable c
order by InData,OutData;

解説

クロスジョインで組み合わせを列挙してます

---