副問い合わせ(サブクエリ)
はじめに
ここまでテーブルを結合しての抽出、グループ分けしての抽出と色々な方法を実行してきました。
この章では、副問い合わせ(サブクエリ)を学んでいきます。
副問い合わせとは、 「SELECT文の中でSELECT文を使う」 方法です。
これを使えるようになると、いろいろな方法を組み合わせてSQLを実行できるので、
大抵のレコードは抽出できるようになります。
Step1 : サブクエリ
例えば、商品テーブルの商品のうち、平均単価より高い商品名を調べたい時はどうするのか…
副問い合わせを使用しない場合、最初のSELECT文で平均単価を調べたら、その値をどこかで覚えておいて
次のSELECT文で平均単価を条件として、求めるレコードを抽出しなければなりません。
それぞれ記述してみましょう。
【1】商品の平均単価を調べる
SELECT AVG(UnitPrice) FROM Goods
【2】【1】の結果より単価が高いもの
SELECT GoodsName, UnitPrice FROM Goods WHERE UnitPrice > 【1】の結果
↓ 副問い合わせを使うと一度で実行できます。
SELECT GoodsName, UnitPrice FROM Goods WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Goods)
解説
項番【2】の「【1】の結果」の部分に項番【1】のSELECT文を括弧でくくって記述しています。
括弧でくくったSELECT文がサブクエリです。 サブクエリに対しておおもとのSELECT文をメインクエリと呼びます。
次は、ある商品を購入した際の注文番号を調べます。商品名から注文番号を知りたいのですが、
注文詳細テーブルには商品名情報がありません。商品コードは存在するので、まず最初に商品テーブルで
商品名から商品コードを調べる必要があります。
【1】商品名から商品コードを調べる
SELECT GoodsCode FROM Goods WHERE GoodsName = 'ボレロ'
【2】【1】の結果から注文詳細テーブルで注文番号を調べる
SELECT OrderNo FROM Order_detail WHERE GoodsCode = 【1】の結果
↓ 副問い合わせにしてみる。
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と名前を付けています。
では、次は注文詳細テーブルに登録のある商品の全情報を商品テーブルから出力してみましょう。
【1】注文詳細テーブルの商品コードを調べる
SELECT GoodsCode FROM Order_detail
【2】【1】の結果から商品情報を調べる
SELECT * FROM Goods WHERE GoodsCode IN(【1】の結果)
↓ 副問い合わせにしてみる。
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句では使えなかった、不等号が使えます。
- サブクエリで小計が8000の小計を抽出
- 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')
解説
- サブクエリで注文番号が423の小計を抽出
- 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の小計の合計を抽出しています。
サブクエリで抽出した小計の合計より大きい値を持つ、注文番号と小計の合計を出力しています。