SQL select文のinとexistsの違いがわかっていなかった問題

はじめに

平成29年 秋 応用情報処理試験 午前 問29の問題を説明していたときにinとexistsの違いがわからなくなったので調べました。

対象読者

以下の問題を見て、ウとエで迷う人。

正解は

ウ。
実際にデータを作ってSQLを実行してみた。
[作ったデータ]

[SQLを実行した結果]

エはどうなるのか？

SQLを実行してみたら、こうなった。

元のテーブルを下に再掲する。倉庫番号C003で在庫数が100以上の商品コードは1と2だけなのに、100未満の3と4まで
集計対象に入ってしまっている。

そもそもexistsって何だ？

日本語に直すと、存在するという意味なので、以下のSQLを日本語で読み直すと、

1行目:SELECT 商品コード,SUM(在庫数) AS 在庫合計 FROM 倉庫別商品在庫集計
2行目:where EXISTS 
3行目:(SELECT * FROM 倉庫別商品在庫集計 WHERE 倉庫番号='C003' AND 在庫数>=100)
4行目:GROUP BY 商品コード;

下みたいな意味になる（と思っていた）

1行目:倉庫別商品在庫集計テーブルから商品コードと在庫数の合計を表示しろ
2行目:ただし()のselect中に存在するレコードに該当するものだけ。
3行目:()とは倉庫番号がC003でかつ、在庫数が100のもの。
4行目:出てきたレコードを商品コードでまとめろ。

こんな解釈すると、INと何が違うんだっけ？ってなる。そして迷う。
それはそのはずで、原因はそもそもexistsの解釈が間違っていた。

existsの意味は、存在するならTrue，存在しないならFalseを返す
というだけの命令文。決して存在するレコードだけ返すという意味ではない。
これを踏まえて読み直すと以下。

1行目:倉庫別商品在庫集計テーブルから商品コードと在庫数の合計を表示しろ
2行目:()のselectで書かれたレコードは存在しますか？
3行目:()とは倉庫番号がC003でかつ、在庫数が100のもの。
4行目:出てきたレコードを商品コードでまとめろ。

2行目のexistsは条件というより質問を意味している。
そして、２行目の質問「倉庫番号がC003でかつ、在庫数が100のもの」を求める３行目は常に以下のレコードが求まり、レコードが存在するのでTrueしか返さない。

常にTrueだから結局このSQLは以下の解釈になる。

1行目:SELECT 商品コード,SUM(在庫数) AS 在庫合計 FROM 倉庫別商品在庫集計
2行目:where EXISTS 
3行目:True
4行目:GROUP BY 商品コード;

3行目の回答は常にTrueなので取っ払っても同じ意味。
つまるところ、以下のSQLと同じ。

SELECT 商品コード,SUM(在庫数) AS 在庫合計 FROM 倉庫別商品在庫集計
GROUP BY 商品コード;

実際に動かしてみても、同じ結果が得られた。

ちなみにINを使ったウのSQLは以下だが、

1行目:SELECT 商品コード,SUM(在庫数) AS 在庫合計 FROM 倉庫別商品在庫集計
2行目:where 商品コード= 
3行目:(SELECT 商品コード FROM 倉庫別商品在庫集計 WHERE 倉庫番号='C003' AND 在庫数>=100)
4行目:GROUP BY 商品コード;

3行目がC003のうち、在庫数が100個以上の商品コードは1と2なので３行目は
以下のように書いているのと同じ。

1行目:SELECT 商品コード,SUM(在庫数) AS 在庫合計 FROM 倉庫別商品在庫集計
2行目:where 商品コード= 
3行目:('1' or '2')
4行目:GROUP BY 商品コード;

existsの解釈の違いはわかったけど

今度はexistsの使いどころがわからなくなるが、existsは相関副問い合わせで使う。
相関副問い合わせとは、副問い合わせで主問い合わせの列を参照することをいう。

ややこしいので別の問題を考える。（平成３０年春データベーススペシャリスト 午後II　問5)

答えはウ。

赤字のXが主問い合わせ、Yが副問い合わせのテーブルとなる。

このとき、existsの動きが変わる。
比較のために最初の問題のSQLを見直す。
最初の問題のSQLは以下だったが、３行目において倉庫番号C003で在庫数が100以上のレコードを返すだけなので、常にTrueを返すだけだった。

1行目:SELECT 商品コード,SUM(在庫数) AS 在庫合計 FROM 倉庫別商品在庫集計
2行目:where EXISTS 
3行目:(SELECT * FROM 倉庫別商品在庫集計 WHERE 倉庫番号='C003' AND 在庫数>=100)
4行目:GROUP BY 商品コード;

しかし、今度の問題の下線部分に注目するとXテーブルのレコードを１行ずつチェックする必要がある。
つまり、上記のSQLと異なり、毎回同じ結果が求まるわけではない。

1行目はX.社員コードS001とY.上司コードが一致しているからTrueを返す。

2行目X.社員コードS002はY.上司コードと一致しているものがないのでFalseを返す。

３行目X.社員コードS003とY.上司コードが一致しているからTrueを返す。

というように、以下の副問い合わせは主問い合わせのXテーブルのレコードごとにTrueまたはFalseに変わることになる。

select * from 社員 Y where X.社員コード=Y.上司