相関副問い合わせが良く分かりません

質問

はじめて投稿させていただきます。
データベースの勉強をしていて、相関副問い合わせのところで躓いてしまいました。
例えば、商品ごとの販売数量の平均を上回った日と商品名を一覧で出すというものがあるとします。テーブルはSales（カラムはQuantity、ProductID、SaleDate）とProducts（カラムはProductID、ProductName）を使用します。
そうすると、
SELECT p.ProductName, s1.SaleDate
FROM
Sales AS s1 JOIN Products AS p
ON s1.ProductID = p.ProductID
WHERE
s1.Quantity >
(
SELECT AVG(Quantity)
FROM Sales AS s2
WHERE s1.ProductID = s2.ProductID
)
;
となるらしいのですが、いまいちわかりません。
副問い合わせ内では、SalesテーブルのQuantityの平均を出し、それを副問い合わせを呼び出す側のテーブルから1行ずつ取り出してきたレコード内のQuantityの値と比較し、平均より多ければ抽出していくのだと思ったんですが、そうなると後の　WHERE s1.ProductID = s2.ProductID　が何故必要なんだろう？となってしまい今に至ります。

それと、AVG(Quantity)はAVG(s2.Quantity)とした方がいいのでしょうか？（テーブル名による修飾はRDBMSの内部動作の負担を軽減させると書いてあったので）

分かりにくい文章で申し訳ないのですが、よろしくお願いします。

回答No.3

jamshid6 · ベストアンサー

No.1です。

>相関副問い合わせは、副問い合わせを呼び出す側のSELECT文と、副問い合わせ側のSELECT文が結合しながら結果を作るものであると書いてあるのですが

これは文字通りの意味だと思います。
今回の例だと、
SELECT * FROM Sales AS s1
INNER JOIN Products AS p ON s1.ProductID = p.ProductID
から１件ずつ読みだして（仮にそのデータのProductIDが@ProductIDとします）、
サブクエリ側の
SELECT AVG(Quantity) FROM Sales WHERE ProductID=@ProductID
を実行し、２つの結果を結合して抽出するか判断するということだからです。

相関サブクエリは外側のクエリから実行し、サブクエリは繰り返し実行される構造なのでコストがかかるのは確かです。私もほとんどのケースではJOINで書いています。が、相関サブクエリで書いた方が分かりやすい場合もあります。実際のコーディングはパフォーマンスがすべてではないことが多いです。

お礼

何度も質問してすいませんでした。
よく分かりました、ありがとうございます。
分かりやすさも重要なんですね。

回答No.2

jjon-com

通常のサブクエリと相関サブクエリとでは実行の方法が違います。

>副問い合わせ内では、SalesテーブルのQuantityの平均を出し……

というのは，通常の副問合せの実行方法であり，相関副問合せのそれではありません。
http://www.jjon.com/oracle/OracleSilver/06/CorrelSubquery.html

お礼

ありがとうございます。
何故「相関」なのかが良く分かりました。

回答No.1

jamshid6

SalesテーブルのQuantityの平均を出して、それと各レコードのQuantityと比較していく、というのは合っているのですが、ここで比較する平均というのは「Salesテーブル全体の平均」ではなくて、「Salesテーブルの商品単位での平均」です。
したがって、今読みだしたレコードの商品に絞って平均を出さなければいけないので、最後の条件が必要になるわけです。
そうすると、１件読み出す毎にその商品の平均を算出して比較する、を繰り返すことになるので、非常にコストがかかるようにみえます。
実際、相関副問い合わせはかなりコストがかかります。

ただ、RDBMSにはオプティマイザというものがついていて、そのクエリがよりコストの低い別のクエリで実現できると判断すれば、クエリを置き換えて実行します。
結果、以下のクエリと同じ方法に組み替えて実行したりします（RDBMSによります）。
SELECT p.ProductName, s1.SaleDate
FROM
Sales AS s1
INNER JOIN Products AS p ON s1.ProductID = p.ProductID
INNER JOIN
(SELECT ProductID, AVG(Quantity) AvgQty
FROM Sales
GROUP BY ProductID) s2 ON s2.ProductID=s1.ProductID
WHERE s1.Quantity > s2.AvgQty

Quantityをs2.Quantityにした方がいいかという点については、「その方が早いか」という意味ではほとんど効果はないと思います。むしろ、「その方がわかりやすいか」という意味でつけてもいいかなと思います。
（実務ではそもそも全くs2を使わない書き方も一般的です）

補足

とてもわかりやすい回答ありごとうございます。
集合関数を使用する場合はグループ化という間違った固定概念がありましたので混乱していたようです。また、オプティマイザと呼ばれる機能も始めて知りました。大変勉強になります。
特に速いわけではないんですね。今まで几帳面にすべてつけて来ました（笑）このような実務上の話も大変参考になります。

確かに集合関数を一回一回計算して比較するのはコストが高そうです。では、なるべくSQLを記述する際には相関副問い合わせは使用しない方が良いのでしょうか？

もう1つ疑問に思ったことなのですが、相関副問い合わせは、副問い合わせを呼び出す側のSELECT文と、副問い合わせ側のSELECT文が結合しながら結果を作るものであると書いてあるのですが、これはどのような意味なのかわかりますか？副問い合わせ内のWHERE句で結合するとあるのですが、これは指定された行を抽出するために使われているような気がするのですが。

度々質問してすいません。
よろしくお願いします。