サブクエリーの応用「相関サブクエリー」SQL実践講座(8)

» 2001年03月08日 00時00分 公開
[篠原光太郎@IT]

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

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

【例1】

SELECT * 
FROM Products
WHERE UnitPrice = (
  SELECT MAX(UnitPrice)
  FROM Products
)
画面1 最も高価な(製品単価の高い)製品を抽出するSQL文の例(画面をクリックすると拡大表示します) 画面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 (すべての製品単価の)平均より安価な製品を抽出する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文(画面をクリックすると拡大表示します) 画面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文(画面をクリックすると拡大表示します) 画面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」キーワードを使っている(画面をクリックすると拡大表示します) 画面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行を抽出してくる(画面をクリックすると拡大表示します) 画面7 データ変更後の例6の抽出結果。データの内容いかんにかかわらず、あくまで先頭の5行を抽出してくる(画面をクリックすると拡大表示します)

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

今回のまとめ

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



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

Copyright © ITmedia, Inc. All Rights Reserved.

スポンサーからのお知らせPR

注目のテーマ

AI for エンジニアリング
「サプライチェーン攻撃」対策
1P情シスのための脆弱性管理/対策の現実解
OSSのサプライチェーン管理、取るべきアクションとは
Microsoft & Windows最前線2024
システム開発ノウハウ 【発注ナビ】PR
あなたにおすすめの記事PR

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。