【SQL】existsでの商演算

質問

閲覧ありがとうございます．
現在SQLでデータベースの勉強をしているのですが，existsを用いて商演算を行おうとしてわからない箇所が出てきたので，その点に関しての質問をさせて頂きます．
例として，Ａ，Ｂと名付けた２つのテーブルを最初に用意します．

[テーブル１（名前：机１）]
| 番号 |氏名|年齢|　好物　|
---------------------------
| １２ | あ | １ | りんご |
| ２３ | い | １ | すいか |
| ２３ | い | １ | りんご |
| ４５ | え | ３ | すいか |
| ４５ | え | ３ | りんご |

[テーブル２（名前：机２）]
|　好物　|
----------
| りんご |
| すいか |

この机１を机２で割ることで，この要素を含む氏名を取り出したいと考えて，以下のようなＳＱＬ文を実行しました．

select 氏名 from 机１ A
where exists(
select * from 机２ B
where A.好物 = B.好物);

[求めている結果表示]
| 番号 |氏名|
-------------
| ２３ | い |
| ４５ | え |

[実際に表示された結果]
| 番号 |氏名|
-------------
| １２ | あ |
| ２３ | い |
| ２３ | い |
| ４５ | え |
| ４５ | え |

実行すると
(1)氏名が重複で表示されてしまう
(2)「りんご」のみ好物の「あ」も表示されてしまう
という問題が発生してしまい，色々と調べて考えたものの，結局分からなかったので，今に至ります．

長文・駄文すみませんでした．
まだSQLを学び初めて少ししか経っていないので，全く違うことをしていたらすみません．よろしければ，問題解決へのアドバイスをお願いします．

回答No.3

qbr2 · ベストアンサー

EXISTSを使って商演算を行っていく順序を書いておきます。

SELECT A.番号, A.氏名, A.年齢, A.好物,B.好物 FROM 机1 A,机2 B
とすることで、机1と机2の全ての組み合わせが出力されます。

| 番号 |氏名|年齢|　好物　|
---------------------------
| １２ | あ | １ | りんご |
| １２ | あ | １ |すいか |
| ２３ | い | １ | すいか |
| ２３ | い | １ | りんご |
| ４５ | え | ３ | すいか |
| ４５ | え | ３ | りんご |

上の表から机1に含まれないレコードを抽出します。

SELECT A.番号, A.氏名, A.年齢, A.好物,B.好物 FROM 机1 A,机2 B
WHERE NOT EXISTS (SELECT * FROM 机1 C WHERE A.番号=C.番号 AND A.氏名=C.氏名 AND A.年齢=C.年齢 AND B.好物=C.好物)

| 番号 |氏名|年齢|　好物　|
---------------------------
| １２ | あ | １ |すいか |

最後に机1から、番号=１２,氏名=あ,年齢=１　ではない番号、氏名を抽出します
データの重複を回避するため、DISTINCTを使用しています。（別に、GROUP BY でも構いません)

SELECT DISTINCT D.番号,D.氏名 FROM 机1 D
WHERE NOT EXISTS
(SELECT A.番号, A.氏名, A.年齢, A.好物,B.好物 FROM 机1 A,机2 B
WHERE NOT EXISTS (SELECT * FROM 机1 C WHERE A.番号=C.番号 AND A.氏名=C.氏名 AND B.好物=C.好物)
WHERE D.番号=A.番号,D.氏名=A.氏名,D.年齢=A.年齢)

上のSQLだと、少し冗長なのでまとめると、

SELECT DISTINCT A.番号,A.氏名 FROM 机1 A WHERE NOT EXISTS
(SELECT 1 FROM 机2 B WHERE NOT EXISTS(SELECT 1 FROM 机1 C
WHERE A.番号=C.番号 AND A.氏名=C.氏名 AND A.年齢=C.年齢 AND B.好物=C.好物))

となります。

お礼

こちらが恐縮してしまうほどの丁寧な回答，ありがとうございました．
おかげさまで．商演算において exists はどのような役割を持っているのか，ということについて自分なりに理解することができました．そして，質問の箇所に書いた問題点も，qbr2さんの回答を参考にして，無事に解決することができました．

本当にありがとうございました！

回答No.2

chukenkenkou

人毎に、すべての好物を持つか探す必要があるので、今の正規化されていないテーブル構成では、テーブル１を、「人と好物」の関連付けのためと、最終的に「人を絞る」ための2回、参照する必要があります。

ここのカテゴリ通り、RDBMSはPostgreSQLですか？
バージョンは？

<SQL例1>not existsを2段階で使用
select *
from
(select
"番号","氏名"
from t1
group by "番号","氏名") as x
where not exists
(select 1 from t2 as y
where not exists
(select 1 from t1
where x."番号"="番号"
and y."好物"="好物"
)
)
;

<SQL例2>exceptを使用
select *
from
(select
"番号","氏名"
from t1
group by "番号","氏名") as x
where not exists
(select "好物" from t2
except
select "好物" from t1
where x."番号"="番号"
)
;

お礼

書き忘れてしまいすみませんでした．はい，このカテゴリを探したのもお察しの通り postgreSQL を使用しているからです．
あと，バージョンは 1.8.4 です
そして，回答ありがとうございました，先ほどchukenkenkouさんの回答を参考に実行してみたところ，自分が望む結果を得ることができました．
not existを2回実行することや except を使用するなどの方法は知らなかったので，この機会に勉強させて頂きました．
本当にありがとうございました．

回答No.1

あんらいぷ ぜろいち（@Unripe01）

こんにちは
集合の商演算について、
うまく説明できないので、

多分この参考サイトに答えがのっているのではないでしょうか。

参考URL：

http://oraclesqlpuzzle.hp.infoseek.co.jp/12-5.html

お礼

回答ありがとうございました．
こんなサイトがあったんですね，まだまだ調べ方が足りなかったようです．すみません．ありがたく参考にさせてもらいます．