SQL EXISTS演算子について

-------------------------------------------------------
SELECT DISTINCT 商社．商社名 FROM 商社
WHERE NOT EXISTS
（SELECT ＊ FROM 商品
WHERE NOT EXISTS
（SELECT ＊ FROM 納品
WHERE 納品．商品番号 ＝ 商品．商品番号
AND 納品．商社番号 ＝ 商社．商社番号））

各テーブルレイアウト
納品（［商品番号］、［商社番号］、納品数量）
商品（［商品番号］、商品名）
商社（［商社番号］、商社名）
［］は主キー

条件：納品表に行が存在することは、その商品を商社が納品することを意味する。

---------------------------------------------------------
上記ＳＱＬは、「全ての商品を納入する商社の商社名を求める」ものだそうですが、なぜそうなるのか理解できません。
具体的にどういう判定でそうなるのか教えていただけないでしょうか。

No.1 回答者： dda167 回答日時： 2009/12/01 15:39

SELECT ＊ FROM 納品

WHERE 納品．商品番号 ＝ 商品．商品番号
AND 納品．商社番号 ＝ 商社．商社番号

条件がEXISTSの場合、
「納品テーブルに、その商社が納入する商品が存在する」です。
NOT EXISTSだから……
「納品テーブルに、その商社が納入する商品は存在しない」です。

SELECT ＊ FROM 商品
WHERE NOT EXISTS (...)

条件がEXISTSの場合、
「商品テーブルに、その商社が納入しない商品が存在する」です。
NOT EXISTSだから……
「商品テーブルに、その商社が納入しない商品は存在しない」です。

「すべての商品を納入する」を
「納入しない商品は存在しない」と言い換えているわけです。

※DISTINCTはいらないような気がする……

No.2 回答者： k_o_r_o_c_h_a_n 回答日時： 2009/12/02 01:01

そのSQLって正しく動作しないような気がするんですよね。

”商品と納品”のサブクエリの結果の有無で、商社の全問合せか０件になりませんか？
（商社と無関係なサブクエリ結果を条件として、商社を処理してる）

No.3 回答者： piroin654 回答日時： 2009/12/02 04:38

集合論的にとらえないと大変です。

一番内側のサブクエリ、

(SELECT * FROM 納品
WHERE 納品.商品番号=商品.商品番号
And 納品.商社番号=商社.商社番号)

は、納品テーブルのすべてのレコードを取り出しますが(商品を取り出しているのではありません）、
集合論的にはこのほかに納品テーブルには存在しない商品と商社の組み合わせが存在します。
このサブクエリのNot Existsはこの存在しない組み合わせ持つレコードと考えておきます。
つまり空の集合です。（もしかするとこれがキーポイントかもしれません）

外のサブクエリ、

SELECT * FROM 商品
WHERE Not EXISTS(SELECT----)

は、この内側のサブクエリの集合の中から各要素、すなわち各レコードのフィールドの
商品番号と商品テーブルのデータと合致するレコードを取り出します。
しかし、対象となる内側のサブクエリのデータは空なので外のサブクエリのデータは空です。
つまりこの時点では納品していない商品があるレコードを取り出します。
そして、この外のサブクエリのNot Existsは納品していない商品のないレコードを取り出します。

最後に、

SELECT DISTINCT 商社.商社名 FROM 商社

によって、商社テーブルの商社番号と取り出したレコードの商社番号が一致する商社名を取り出すと
考えることが出来ます。

実際にテーブルにデータを入れてこれらのサブクエリを稼動させるとよくわかります。

No.4 回答者： piroin654 回答日時： 2009/12/02 05:33

回答を少し訂正します。

つまり空の集合です。（もしかするとこれがキーポイントかもしれません）

の部分ですが、空集合という意味ではありません。商社と商品のすべての組み合わせの中で、
納品テーブルに存在しない商社と商品の組み合わせをもつレコードという意味です。実際に
サブクエリを稼動させて出てくるのはこのレコードです。

No.5 回答者： piroin654 回答日時： 2009/12/02 06:34

すみません。

部分的な訂正よりすべてを一緒にします。SQLは個々のコマンドの働きの把握も大切
ですが、集合論と同じように考えると理解が早いと思います。サブクエリ、集合、直積などが
キーワードです。以下訂正文です。


集合論的にとらえないと大変です。
一番内側のサブクエリ、

(SELECT * FROM 納品
WHERE 納品.商品番号=商品.商品番号
And 納品.商社番号=商社.商社番号)

は、納品テーブルのすべてのレコードを取り出しますが(商品を取り出しているのではありません）、
集合論的にはこのほかに納品テーブルには存在しない商品と商社の組み合わせが存在します。
このサブクエリのNot Existsはこの存在しない組み合わせ持つレコードと考えておきます。
つまり空の集合です。（もしかするとこれがキーポイントかもしれません）
ただし、空集合という意味ではありません。商社と商品のすべての組み合わせの中で、
納品テーブルに存在しない商社と商品の組み合わせをもつレコードという意味です。実際に
サブクエリを稼動させて出てくるのはこのレコードです。


外のサブクエリ、

SELECT * FROM 商品
WHERE Not EXISTS(SELECT----)

は、この内側のサブクエリの集合の中から各要素、すなわち各レコードのフィールドの
商品番号と商品テーブルのデータと合致するレコードを取り出します。
しかし、対象となる内側のサブクエリのデータは納品テーブルに存在しないので外のサブクエリの
データは納品テーブルに存在しない商品があるレコードです。つまりこの時点では納品していない
商品があるレコードを取り出します。
そして、この外のサブクエリのNot Existsは納品していない商品のないレコードを取り出します。

最後に、

SELECT DISTINCT 商社.商社名 FROM 商社

によって、商社テーブルの商社番号と取り出したレコードの商社番号が一致する商社名を取り出すと
考えることが出来ます。

実際にテーブルにデータを入れてこれらのサブクエリを稼動させるとよくわかります。

この回答へのお礼

piroin654さん　大変丁寧にありがとうございます。

＞サブクエリ、集合、直積などがキーワードです。
→直積で考えることでようやく理解できました。内側のNOT　EXISTSで、納品テーブルに存在するレコードがFALSEになり、存在しないレコードがTRUEになると考えると整理できました。

＞実際にテーブルにデータを入れてこれらのサブクエリを稼動させるとよくわかります。
→まずそれを考えたのですが、今回のクエリはサブクエリ毎に参照テーブルが独立していないので、切り離して実行できないことから頭で考えるしかないのかと・・・。
実際にデータを入れ、「全ての商品を納入する商社の商社名を求める」という結果を得られることの確認だけは出来てたのですが。

「直積」で、全ての謎が解けました。
ありがとうございました。

お礼日時：2009/12/02 15:50

No.6 回答者： piroin654 回答日時： 2009/12/04 02:16

単純にして味わい深くかつ巧妙なSQL文ですが、直積というヒントでこのSQL文のからくりをイメージ

出来たとはすばらしい。もはや上級者の仲間入りです。SQL文と直積の概念を結びつけるのは大変です
。集合を十分わかっていないとイメージがわかないものです。ましてや指定のテーブルを使って直積
の概念を取り入れた題意のSQLを一つだけ開発しなさい、と言われたとんでもない難問です。
何か少し疑問が残ったみたいですが、Ms Accessを持たれていたら以下のSQLを登録して実行してみて
ください。ただしMs Accessはこのままで受け付けますが、登録後に若干の変更を行います。これは
Ms Accessの方言だと思ってください。実行すると商社.商社番号を問い合わせてきます。そのときに
納品していないものがある商社の商社番号を入れると、納品していない商品を実行結果として表示
します。ここがこのSQLのミソです。

SELECT * FROM 商品
WHERE Not EXISTS
(SELECT * FROM 納品
WHERE 納品.商品番号=商品.商品番号
And 納品.商社番号=商社.商社番号);

もちろんデータが大量にある場合は商社テーブルのレコードをループしながら商社番号をこのSQLに
送り込むプログラムが必要ですが、数個のレコードでこのからくりは確認出来るのでそこまでは必要
無いと思います。

また直積という概念はSQLを自在に扱う上で重要なカギの一つとなります。ただ、このSQL文に内在
する見えるデータと見えないデータをTrueとFalseというスイッチではなく、Exsitsあるいは
Not Existsという言葉の概念で捉えてください。たしかにTrueやFalseを条件の判断に使う
データベース(例えばMs Access)がありますが、本来はこのSQL文が標準的な書き方です。直積が
わかればTrueとかFalseという補助手段は必要ありません。そのほうが将来のためです。

データを処理するアルゴリズムとこまごまとしたテクニックは分けて考えてください。
必要があればテクニックは後からついてきます。そっか、アルゴリズムを構築するときはTrueとか
Falseを使いますよね。話が少々迷路に入りかけているのでこのあたりにします。がんばってください。