10-115 差集合の和集合

SQLパズル

Table1            Table2            Table3
TypeID  Name      TypeID  Name      TypeID  Name
------  ------    ------  ------    ------  ---------
  1123  paper       1123  paper       1234  pencil
  1234  pencil      1234  pencil      1234  folder
  1234  pen         1234  pen         1234  clips
  1234  clips       1234  folder      2256  glue
  2938  rubber      1234  clips       9093  clipboard

Table1、Table2、Table3で、
一つのテーブルにのみ存在するレコードを
以下の形式で出力する。

出力結果
DiffInfo                     TypeID  Name
---------------------------  ------  ---------
in Table1 but not in Table2    2938  rubber
in Table1 but not in Table3    1123  paper
in Table1 but not in Table3    1234  pen
in Table1 but not in Table3    2938  rubber
in Table2 but not in Table1    1234  folder
in Table2 but not in Table3    1123  paper
in Table2 but not in Table3    1234  pen
in Table3 but not in Table1    1234  folder
in Table3 but not in Table1    2256  glue
in Table3 but not in Table1    9093  clipboard
in Table3 but not in Table2    2256  glue
in Table3 but not in Table2    9093  clipboard

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table Table1(
TypeID number(4),
Name   varchar2(10));

create table Table2 as select * from Table1;
create table Table3 as select * from Table1;

insert into Table1 values(1123,'paper');
insert into Table1 values(1234,'pencil');
insert into Table1 values(1234,'pen');
insert into Table1 values(1234,'clips');
insert into Table1 values(2938,'rubber');

insert into Table2 values(1123,'paper');
insert into Table2 values(1234,'pencil');
insert into Table2 values(1234,'pen');
insert into Table2 values(1234,'folder');
insert into Table2 values(1234,'clips');

insert into Table3 values(1234,'pencil');
insert into Table3 values(1234,'folder');
insert into Table3 values(1234,'clips');
insert into Table3 values(2256,'glue');
insert into Table3 values(9093,'clipboard');
commit;

SQL

select 'in Table1 but not in Table2' as DiffInfo,TypeID,Name from Table1 minus
select 'in Table1 but not in Table2',TypeID,Name from Table2
union all
(select 'in Table1 but not in Table3',TypeID,Name from Table1 minus
 select 'in Table1 but not in Table3',TypeID,Name from Table3)
union all
(select 'in Table2 but not in Table1',TypeID,Name from Table2 minus
 select 'in Table2 but not in Table1',TypeID,Name from Table1)
union all
(select 'in Table2 but not in Table3',TypeID,Name from Table2 minus
 select 'in Table2 but not in Table3',TypeID,Name from Table3)
union all
(select 'in Table3 but not in Table1',TypeID,Name from Table3 minus
 select 'in Table3 but not in Table1',TypeID,Name from Table1)
union all
(select 'in Table3 but not in Table2',TypeID,Name from Table3 minus
 select 'in Table3 but not in Table2',TypeID,Name from Table2);

解説

差集合の和集合を求めてます。

---