5-43 他テーブルとの共通集合

SQLパズル

好物テーブル
人物   ジャンル
----   --------
1郎   ごはん類
2郎   ごはん類
3郎   めんるい
4郎   めんるい
5郎   どんぶり

食堂テーブル
ジャンル   メニュー   Ａ食堂   Ｂ食堂
--------  --------   ------   ------
ごはん類   玉子ご飯   マズイ   マズイ
ごはん類   お茶づけ   マズイ   マズイ
ごはん類   おむすび   マズイ   マズイ
めんるい   ラーメン   うまい   マズイ
めんるい   タンメン   うまい   マズイ
めんるい   ソーメン   うまい   マズイ
どんぶり   カツどん   マズイ   マズイ
どんぶり   テンどん   マズイ   うまい
どんぶり   親子どん   マズイ   マズイ

Ａ食堂またはＢ食堂で、
うまいメニューが属するジャンル（めんるい＆どんぶり）が好きな人物を取得する。

出力結果
人物
----
3郎
4郎
5郎

データ作成スクリプト

create Table 好物(
人物     char(4),
ジャンル char(8));

create Table 食堂(
ジャンル char(8),
メニュー char(8),
Ａ食堂   char(6),
Ｂ食堂   char(6));

insert into 好物(人物,ジャンル) values('1郎','ごはん類');
insert into 好物(人物,ジャンル) values('2郎','ごはん類');
insert into 好物(人物,ジャンル) values('3郎','めんるい');
insert into 好物(人物,ジャンル) values('4郎','めんるい');
insert into 好物(人物,ジャンル) values('5郎','どんぶり');
insert into 食堂(ジャンル,メニュー,Ａ食堂,Ｂ食堂) values('ごはん類','玉子ご飯','マズイ','マズイ');
insert into 食堂(ジャンル,メニュー,Ａ食堂,Ｂ食堂) values('ごはん類','お茶づけ','マズイ','マズイ');
insert into 食堂(ジャンル,メニュー,Ａ食堂,Ｂ食堂) values('ごはん類','おむすび','マズイ','マズイ');
insert into 食堂(ジャンル,メニュー,Ａ食堂,Ｂ食堂) values('めんるい','ラーメン','うまい','マズイ');
insert into 食堂(ジャンル,メニュー,Ａ食堂,Ｂ食堂) values('めんるい','タンメン','うまい','マズイ');
insert into 食堂(ジャンル,メニュー,Ａ食堂,Ｂ食堂) values('めんるい','ソーメン','うまい','マズイ');
insert into 食堂(ジャンル,メニュー,Ａ食堂,Ｂ食堂) values('どんぶり','カツどん','マズイ','マズイ');
insert into 食堂(ジャンル,メニュー,Ａ食堂,Ｂ食堂) values('どんぶり','テンどん','マズイ','うまい');
insert into 食堂(ジャンル,メニュー,Ａ食堂,Ｂ食堂) values('どんぶり','親子どん','マズイ','マズイ');
commit;

SQL

--■■■in述語を使う方法■■■
select 人物 from 好物 a
where ジャンル in(select ジャンル from 食堂 b
                   where 'うまい' in (b.Ａ食堂,b.Ｂ食堂))
order by 人物;

--■■■exists述語を使う方法■■■
select 人物 from 好物 a
where exists(select 1 from 食堂 b
              where b.ジャンル=a.ジャンル
                and 'うまい' in (b.Ａ食堂,b.Ｂ食堂))
order by 人物;

--■■■共通集合を求める方法■■■
select 人物 from 好物 a
where exists(select a.ジャンル from dual
             intersect
             select ジャンル from 食堂
              where 'うまい' in (Ａ食堂,Ｂ食堂))
order by 人物;

--■■■結合させる方法■■■
select distinct a.人物
  from 好物 a,食堂 b
 where a.ジャンル=b.ジャンル
   and 'うまい' in (b.Ａ食堂,b.Ｂ食堂)
order by 人物;

解説

in述語は、右辺だけでなく左辺にも、リテラル(固定値)を使用できます。

SQL For Practitioners 2-2. IN のリストに列を指定する