サブクエリーの応用「相関サブクエリー」：SQL実践講座（8）

　今回は、副問い合わせ（サブクエリー）から主問い合わせのカラムを参照する相関副問い合わせ（相関サブクエリー）を説明します。

　まず初めに、もう少しサブクエリーの例を見ながら、サブクエリーの癖をつかんでいきましょう。次の例を実行します。

SELECT * 
FROM Products
WHERE UnitPrice = (
  SELECT MAX(UnitPrice)
  FROM Products
)

画面1　最も高価な（製品単価の高い）製品を抽出するSQL文の例（画面をクリックすると拡大表示します）

　これは、「最も高価な製品」を問い合わせるSELECT文です。2つ目のSELECT文で製品マスタテーブルである「Products」において、単価の最高金額を MAX(UnitPrice) で求めています。1つ目のSELECT文では、各製品の単価（UnitPrice）を2つ目のSELECTで求めた単価の最高金額と比べて、同じであればその製品が最も高価な製品である、という条件を設定しています。

　では、次の例はどうでしょうか。

SELECT * 
FROM Products
WHERE UnitPrice >= ALL (
  SELECT UnitPrice
  FROM Products
)

　結果は、例1と同じですね。2つ目のSELECT文で、すべての製品の単価（UnitPrice）をリストし、1つ目のSQL文は、単価がすべての製品の単価以上である製品が、最も高価な製品である、という条件設定になっています。

　同様に、「単価が平均よりも安い製品」は、以下のSELECT文で求められます。

SELECT * 
FROM Products
WHERE UnitPrice < (
  SELECT AVG(UnitPrice)
  FROM Products
)

画面2　（すべての製品単価の）平均より安価な製品を抽出するSQL文の例（画面をクリックすると拡大表示します）

　2番目のSELECT文で製品単価の平均をAVG（UnitPrice）で求め、1番目のSELECT文では各製品の単価が製品単価の平均よりも安価であることが、条件として指定されています。

相関サブクエリー（相関副問い合わせ）

　これまでの例で紹介したサブクエリーは、それぞれのSELECT文が独立して実行できるものでした。例えば、例1のSQL文には、2つのSELECT文が含まれます。1つは、

の部分で、もう1つは

の部分です。2つのSELECT文は独立していて、それぞれを単独で実行することができます（αに適切な数値をセットすることは必要ですが）。

　今回紹介するサブクエリーでは、内側のSELECT文（上記の(B)）から、外側のSELECT文（上記の(A)）を参照して条件を組み立てます。

　例えば、例1と同様、「一番高い製品」を相関サブクエリーを使用して求める場合、次のようなSELECT文になります。

SELECT * 
FROM Products Prod1
WHERE Prod1.UnitPrice > ALL(
  SELECT UnitPrice
  FROM Products Prod2
  WHERE Prod1.ProductID <> Prod2.ProductID
)

画面3　相関サブクエリーを用いたSQL文（画面をクリックすると拡大表示します）

　結果は例1と同じですね。このサブクエリーの仕組みを見てみましょう。

　まず、外側のSELECT文を分解すると、次の(C)のとおりになります。

　これは、(α、β……)などのどの値よりも単価（UnitPrice）が高い製品を抽出するSELECT文です。次の(D)で参照するために、Productsテーブルは、Prod1という別名が付けられています。

　次に、内側のSELECT文を分解すると、(D)のとおりになります。

　これは、(C)の各行の製品以外のすべての製品の単価を抽出するSELECT文です。(C)で選択された製品IDを、WHERE句の中で「Prod1.ProductID」として参照しています。このProductID以外の製品を求めるため、「Prod1.ProductID <> Prod2.ProductID」という条件式をWHERE句に指定しています。

　(C)と(D)を併せると、各行に対して、次のようなプロセスで結果の行が抽出されます。

　結果として、(3)で抽出される製品は、ほかのどの製品よりも高い単価を持った製品なので、「一番高い製品」が抽出されることになります。

単価TOP5の製品

　では、例4の応用として、「単価TOP5の製品リスト」を抽出してみます。次の例題を実行してみましょう。

SELECT ProductID, ProductName, UnitPrice
FROM Products Prod1
WHERE 5 > (
  SELECT Count(*)
  FROM Products Prod2
  WHERE Prod1.UnitPrice < Prod2.UnitPrice
)
ORDER BY UnitPrice DESC

　この例が正しいかどうかを確認するには、次のSQL文を実行すればよいですね。

SELECT ProductID, ProductName, UnitPrice
FROM Products
ORDER BY UnitPrice DESC

画面4　製品単価のTOP5を求めるSQL文（画面をクリックすると拡大表示します）

　例5の相関サブクエリーの仕組みは、次のようになっています。

　ここで(3)の処理は結果的に、「上位の5つの単価を持った製品」を抽出することになることが分かるでしょうか？

一番高い単価の製品は、(2)の処理の結果「0」となります。一番高い製品なので、それよりも高い製品の数は0だからですね。続いて、2番目に高い製品の場合の(2)の処理の結果は「1」となります。2番目に高い製品なので、それよりも高い製品は一番高い製品1つだけだからですね。同様にして、5番目に高い製品の場合の(2)の処理の結果は「4」となります。

　よって、単価TOP5の製品リストを求めるには、(2)の結果が5よりも小さい、という条件を指定すればよいことになります。

　ただし、TOP5を求めるには、もっと便利なSQL文もあるので、覚えておきましょう。次の例題を実行してください。

SELECT TOP 5 ProductID, ProductName, UnitPrice
FROM Products Prod1
ORDER BY UnitPrice DESC

画面5　製品単価のTOP5を求めるSQL文。こんどはサブクエリーは使用せずに、「TOP」キーワードを使っている（画面をクリックすると拡大表示します）

　同じ結果が得られましたね。SELECT句に続く「TOP」キーワードは、結果の行数を制限する、という指示をSQL文に与えます。よって、「TOP5」と指定すると、「最初の5行」を結果として返します。例6では、ORDER BY句によってUnitPriceが高いものから順に並べ替えが行われているので、結果として「高いものから順に5行」が返されます。

　ただし、同じ順位が存在すると例5と例6で結果が違ってくるので、注意が必要です。例えば、上記の例6でUnitPriceが「81.000」である製品が4つ存在した場合を確認するため、データを変更してみましょう。

UPDATE Products SET UnitPrice = 81
WHERE ProductID IN (18,59,51)

　このSQL文は、ProductIDが18、59、51の製品の単価を「81」に更新します（UPDATE文の詳細は、回を改めて解説します）。

　データを更新したら、例5と例6をもう一度実行してみましょう。

画面6　データ変更後の例5の抽出結果。TOP5に同じ単価の製品が存在する場合、すべてリストアップしているのがわかる（画面をクリックすると拡大表示します）

画面7　データ変更後の例6の抽出結果。データの内容いかんにかかわらず、あくまで先頭の5行を抽出してくる（画面をクリックすると拡大表示します）

　TOPキーワードを使用した場合は、データの変更にかかわらず最初の5行が抽出されているのが分かりますね。相関サブクエリーを使用した例5の場合は、同一順位で4位のProductIDが18、20、51、59の4製品が、漏れずにリストアップされているのが分かります。