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

[篠原光太郎，＠IT]

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

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

【例1】

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

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

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

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

【例2】

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

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

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

【例3】

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

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

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

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

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

(A) SELECT * FROM Products WHERE UnitPrice

= α（αは任意の数値）

の部分で、もう1つは

(B) SELECT MAX(UnitPrice) FROM Products

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

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

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

【例4】

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

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

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

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

(C) SELECT * FROM Products Prod1 WHERE Prod1.UnitPrice > ALL(α、β……)

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

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

(D) SELECT UnitPrice FROM Products Prod2 WHERE Prod1.ProductID Prod2.ProductID

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

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

(1) Prod1テーブルから、1行選択される。このときのProductIDを「A」とする……(C)

(2) Prod2テーブルから、ProductIDが「A」以外のすべての製品の単価（UnitPrice）のリストを抽出する。このときのUnitPriceのリストを（α、β……）とする……(D)

(3) Prod1テーブルで現在選択されている製品「A」のUnitPriceが、（α、β……）のどれよりも大きい場合、製品「A」の行は結果の行として抽出される。UnitPriceが（α、β……）のどれよりも小さい場合、製品「A」の行は結果の行から削除される……(C)

(4) Prod1の次の製品を処理するため、(A)から(C)を繰り返す

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

単価TOP5の製品

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

【例5】

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の相関サブクエリーの仕組みは、次のようになっています。

(1) Prod1テーブルから、1行選択される。このときのProductIDを「A」とする

(2) Prod2テーブルから、ProductIDが「A」の製品の単価（UnitPrice）よりも高い単価を持った製品の個数を求める。このときの個数を「α」とする

(3) αが5よりも小さければ、製品「A」は結果リストに抽出される

(4) Prod1の次の製品を処理するため、(1)から(3)を繰り返す

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

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

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

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

【例6】

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つ存在した場合を確認するため、データを変更してみましょう。

【例7】

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製品が、漏れずにリストアップされているのが分かります。

今回のまとめ

今回は、「相関副問い合わせ（相関サブクエリー）」を紹介しました。次回は、SELECTの応用とまとめについて解説予定です

「次回」へ

「SQL実践講座」バックナンバー

• SQL Serverで「デッドロック」を回避する
• トランザクションの一貫性を保証するロック
• トランザクションを用いて注文登録をする
• トランザクションでデータの不整合を防ぐ
• テーブルで複数の処理を実行させるトリガー
• ユーザー定義関数を作成するストアドファンクション
• ストアドプロシージャによる繰り返し処理
• 条件分岐のあるストアドプロシージャ
• ストアドプロシージャの作成
• システム・ストアドプロシージャを用いたロールの詳細設定
• ロールを利用したグループ単位での権限設定
• SQL Serverのオブジェクトに権限を設定する
• Enterprise Managerによるビューの作成
• 作成したSELECT文をDBに登録する「ビュー」
• データの更新と主キーの重要性
• テーブル中のデータ識別に必要な主キーを定義する
• データの登録を行うINSERT文
• CREATE文をさらに使いこなそう
• CREATE文でテーブルを作成する
• SELECT文を統合する「UNION」
• サブクエリーの応用「相関サブクエリー」
• SELECT文の結果を抽出条件に使う
• テーブル結合のバリエーションを増やす
• テーブル結合の仕組みを理解する
• 異なるテーブル同士を結合する「JOIN」句
• 集計を行う「GROUP BY」句
• SELECT文で並べ替えを行うには？
• SQLの基礎 「SELECT」文を覚えよう