2-2-10 共通集合と論理積

SQLパズル

Table1    Table2    Table3
Col1      Col1      Col1
----      ----      ----
   1         2         3
   2         4         6
   3         8         9
   4        10        12
   5                  15
   6                  18
   7
   8
   9
  10

Table1のCol1が、
Table2とTable3の両方から、外部キー経由で参照されていれば1、されてなければ0
を出力する。

Table1、Table2、Table3のプライマリキーは、Col1とする。

出力結果
Col1  IsReference
----  -----------
   1            0
   2            0
   3            0
   4            0
   5            0
   6            1
   7            0
   8            0
   9            0
  10            0

データ作成スクリプト

create table Table1(Col1 number,primary key(Col1));
create table Table2(Col1 number,primary key(Col1));
create table Table3(Col1 number,primary key(Col1));

begin
    for i in 1..10 loop
        insert into Table1(Col1) values(i);
        insert into Table2(Col1) select i from dual where mod(i,2)=0; --2の倍数
        insert into Table3(Col1) select i from dual where mod(i,3)=0; --3の倍数
    end loop;
    commit;
end;
/

SQL

--■■■exists述語の引数に共通集合を使う方法■■■
select Col1,
case when exists(select 1 from Table2 b where b.Col1=a.Col1
       intersect select 1 from Table3 b where b.Col1=a.Col1)
     then 1 else 0 end as IsReference
from Table1 a
order by Col1;

--■■■exists述語の論理積を使う方法■■■
select Col1,
case when exists(select 1 from Table2 b where b.Col1=a.Col1)
      and exists(select 1 from Table3 b where b.Col1=a.Col1)
     then 1 else 0 end as IsReference
from Table1 a
order by Col1;

--■■■集合演算の公式Ａ∩Ｂ＝Ａ∩¬(Ａ∩¬Ｂ)を使って共通集合を求める方法■■■
select Col1,
case when exists(select 1 from(
                     select Col1 from Table2
                     minus (select Col1 from Table2 minus select Col1 from Table3)) b
                  where b.Col1=a.Col1)
     then 1 else 0 end as IsReference
from Table1 a
order by Col1;

解説

exists述語の論理積は、
exists述語の引数に共通集合を使うことで代用できます。

---