12-5 not existsで集合の商演算

SQLパズル

納品テーブル
商品番号   商社番号  納品数量
--------  --------  --------
    1111      aaaa        10
    1111      bbbb        20
    2222      aaaa        30
    2222      bbbb        40
    2222      cccc        50
    3333      bbbb        60
    3333      cccc        70

商品テーブル
商品番号   商品名
--------  ------------
    1111   USBメモリ
    2222   LANケーブル
    3333   マウスパッド

商社テーブル
商社番号  商社名
--------  ------
   aaaa   商社A
   bbbb   商社B
   cccc   商社C

すべての商品を納品する商社の、
商社名を出力する。

出力結果
商社名
------
商社B

データ作成スクリプト

create table 納品(
商品番号 char(4),
商社番号 char(4),
納品数量 number(2));

insert into 納品 values('1111','aaaa',10);
insert into 納品 values('1111','bbbb',20);
insert into 納品 values('2222','aaaa',30);
insert into 納品 values('2222','bbbb',40);
insert into 納品 values('2222','cccc',50);
insert into 納品 values('3333','bbbb',60);
insert into 納品 values('3333','cccc',70);

create table 商品(
商品番号 char(4),
商品名   varchar2(12));

insert into 商品 values('1111','USBメモリ');
insert into 商品 values('2222','LANケーブル');
insert into 商品 values('3333','マウスパッド');

create table 商社(
商社番号 char(4),
商社名   varchar2(5));

insert into 商社 values('aaaa','商社A');
insert into 商社 values('bbbb','商社B');
insert into 商社 values('cccc','商社C');
commit;

SQL

--■■■not existsを2回使う方法■■■
select 商社名
  from 商社 a
 where not exists(select 1 from 商品 b
                   where not exists(select 1 from 納品 c
                                     where c.商社番号 = a.商社番号
                                       and c.商品番号 = b.商品番号));

--■■■minusを使う方法■■■
select 商社名
  from 商社 a
 where not exists(select b.商品番号 from 商品 b
                  minus
                  select c.商品番号 from 納品 c
                   where c.商社番号 = a.商社番号);

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select c.商社名
  from 商品 a Left Join 納品 b
 partition by (b.商社番号)
    on (a.商品番号 = b.商品番号)
  Join 商社 c
    on (b.商社番号 = c.商社番号)
group by b.商社番号,c.商社名
having count(*) = count(b.商品番号);

解説

minusを使うと、
集合の商演算をシンプルにできます。