SELECT文 – 副問い合わせ（サブクエリ）

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

はじめに

ここまでテーブルを結合しての抽出、グループ分けしての抽出と色々な方法を実行してきました。
この章では、副問い合わせ（サブクエリ）を学んでいきます。
副問い合わせとは、 「SELECT文の中でSELECT文を使う」 方法です。
これを使えるようになると、いろいろな方法を組み合わせてSQLを実行できるので、
大抵のレコードは抽出できるようになります。

Step1 : サブクエリ

例えば、商品テーブルの商品のうち、平均単価より高い商品名を調べたい時はどうするのか…
副問い合わせを使用しない場合、最初のSELECT文で平均単価を調べたら、その値をどこかで覚えておいて
次のSELECT文で平均単価を条件として、求めるレコードを抽出しなければなりません。

それぞれ記述してみましょう。
【１】商品の平均単価を調べる

SELECT AVG(UnitPrice) FROM Goods

【２】【１】の結果より単価が高いもの

SELECT GoodsName, UnitPrice FROM Goods
    WHERE UnitPrice > 【１】の結果

↓　副問い合わせを使うと一度で実行できます。

SELECT GoodsName, UnitPrice FROM Goods
    WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Goods)


解説
項番【２】の「【１】の結果」の部分に項番【１】のSELECT文を括弧でくくって記述しています。
括弧でくくったSELECT文がサブクエリです。 サブクエリに対しておおもとのSELECT文をメインクエリと呼びます。

次は、ある商品を購入した際の注文番号を調べます。商品名から注文番号を知りたいのですが、
注文詳細テーブルには商品名情報がありません。商品コードは存在するので、まず最初に商品テーブルで
商品名から商品コードを調べる必要があります。

【１】商品名から商品コードを調べる

SELECT GoodsCode FROM Goods WHERE GoodsName = 'ボレロ'

【２】【１】の結果から注文詳細テーブルで注文番号を調べる

SELECT OrderNo FROM Order_detail WHERE GoodsCode = 【１】の結果

↓　副問い合わせにしてみる。

SELECT OrderNo FROM Order_detail WHERE GoodsCode =
    (SELECT GoodsCode FROM Goods WHERE GoodsName = 'ボレロ')

最初の例題と同じパターンです。使い方がわかってきましたか？
少々パターンを変えてみましょう。次のSQL文は上と同じ結果を返します。

SELECT TBL1.OrderNo FROM Order_detail TBL1,
    (SELECT GoodsCode FROM Goods WHERE GoodsName = 'ボレロ') TBL2 
    WHERE TBL1.GoodsCode = TBL2.GoodsCode


解説
先ほどはWHERE句にサブクエリを記述しましたが、今度はFROM句に書きました。
副問い合わせの結果を一時的にテーブルのように保持 し、
FROM句で指定した他のテーブルと等価結合して結果を引き出します。

FROM句にサブクエリを記述した場合、テーブル名はサブクエリ内のGoodsではなくなるため別名として
TBL2を指定しています。Order_detailはそのままでも構いません。
ここではわかりやすいようにTBL1という別名にしました。

SELECT TBL1.OrderNo, TBL2.GoodsName FROM Order_detail TBL1,
    (SELECT GoodsCode, GoodsName FROM Goods WHERE GoodsName = 'ボレロ') TBL2
    WHERE TBL1.GoodsCode = TBL2.GoodsCode


解説
FROM句の場合、一時テーブルを作成するイメージなので、
メインクエリのSELECTにサブクエリの結果を出力することができます。

またSELECT句にもサブクエリを使うことが出来ます。

SELECT SUM(SubTotal),
    (SELECT SUM(SubTotal) FROM Order_detail WHERE OrderNo = '423') AS SUM
        FROM Order_detail WHERE OrderNo = '420'


解説
注文番号420、注文番号423の小計の合計を出力しています。
副問い合わせで抽出した列には名前が付いていないので、SUMと名前を付けています。

では、次は注文詳細テーブルに登録のある商品の全情報を商品テーブルから出力してみましょう。

【１】注文詳細テーブルの商品コードを調べる

SELECT GoodsCode FROM Order_detail

【２】【１】の結果から商品情報を調べる

SELECT * FROM Goods WHERE GoodsCode IN(【１】の結果)

↓　副問い合わせにしてみる。

SELECT * FROM Goods WHERE GoodsCode IN
    (SELECT GoodsCode FROM Order_detail)

この場合、商品テーブルで条件となるのは注文詳細テーブルに登録があるかないかです。
存在有無を判定するためのEXISTSという記述も使うことができます。

SELECT * FROM Goods TBL1 WHERE EXISTS
    (SELECT GoodsCode FROM Order_detail TBL2
        WHERE  TBL1.GoodsCode  = TBL2.GoodsCode)


解説

・IN句の場合
1: サブクエリで抽出した内容がIN句へ
2: メインクエリのSELECT文の条件となり結果を抽出する
という流れとなります。
・EXISTSの場合
1: メインクエリのテーブルレコードを一行ずつサブクエリへ渡す
2: サブクエリ内のテーブルレコードと等価条件による比較を行う
3: 一致したかどうかを TRUE、FALSE でメインクエリに返し、TRUE ならば出力する
という流れとなります。

SQL文を見ると、サブクエリ内にメインクエリのテーブルである TBL1 という記述がありますね。
このように「サブクエリでメインクエリを参照するもの」を「 相関サブクエリ 」と呼びます。

Step 2 : ANY句

サブクエリに不等号を使用することが出来ます。

SELECT TBL1.OrderNo FROM Order_header TBL1, Order_detail TBL2
    WHERE TBL1.OrderNo = TBL2.OrderNo AND TBL2.SubTotal > ANY (SELECT SubTotal
        FROM Order_detail WHERE SubTotal = 8000)


解説
ANY句ではIN句では使えなかった、不等号が使えます。

  1. サブクエリで小計が8000の小計を抽出
  2. ANY句を使い、抽出した小計より大きい小計の注文番号を取得

ANY句では サブクエリで生成した値のいずれかの値を評価対象 としています。

SELECT TBL1.OrderNo, TBL2.SubTotal FROM Order_header TBL1, Order_detail TBL2
   WHERE TBL1.OrderNo = TBL2.OrderNo AND TBL2.SubTotal > ANY (SELECT SubTotal
        FROM Order_detail WHERE OrderNo = '423')


解説

  1. サブクエリで注文番号が423の小計を抽出
  2. ANY句を使い抽出した小計より大きい小計の注文番号を出力

サブクエリで取得した値は｛4000. 5500｝です。
この値のうちいずれかについて、注文詳細テーブルの各行の受注個数が大きくなる行を出力します。
今回の場合は4000より大きい値を出力しています。

HAVING句でもサブクエリを使用することが出来ます。

SELECT OrderNo, SUM(SubTotal) FROM Order_detail GROUP BY OrderNo
    HAVING SUM(SubTotal) > (SELECT SUM(SubTotal) FROM Order_detail WHERE OrderNo = '422')

注文番号ごとに小計の合計を出力します。サブクエリで注文番号が422の小計の合計を抽出しています。
サブクエリで抽出した小計の合計より大きい値を持つ、注文番号と小計の合計を出力しています。

カテゴリー

アーカイブ

Close Bitnami banner
Bitnami