7. サブクエリ
SQL 7章 サブクエリ
7.1. サブクエリの基本複数のクエリを組み合わせて、1つのクエリが生成した出力で、他のクエリの出力を制御することができます。少し分かりやすく言うと、クエリを入れ子にして、内側のクエリが値を生成し、それを外側のクエリの述語が評価して TRUE かどうか判断します。 次の図はサブクエリの最も標準的な形です。図では背景がオレンジ色の部分が内側のクエリで、ピンクの部分が外側のクエリということになります。内側のクエリで生成された値を外側のクエリが受け取り、WHERE 句で TRUE となるものについて、値を参照します。そして、内側のクエリのことをサブクエリといいます。 
前の図からも予想がつくように、等号や不等号といった関係演算子をベースとする述語に使用するサブクエリは、必ず出力として 1行のみを生成するものでなければなりません。サブクエリが値をまったく生成しない場合、述語は UNKNOWN と評価するが、複数の値を生成する場合、メインクエリ (外側のクエリ) は評価不能に陥ってしまい、ステートメントがエラーを生成するという状況になります。 sample.18-1 は、商品名が 'JUICE' である受注番号を参照するものです。 -- sample.18-1 --
/* サブクエリ */
SELECT 受注番号 FROM 受注表
WHERE 商品コード =
(SELECT 商品コード FROM 商品表
WHERE 商品名 = 'JUICE');
/* 実行結果 */
受注番号
────
10001
10004
10007
7.2. WHERE 句でのサブクエリ前節の図でも示したように、基本的なサブクエリは WHERE 句の述語によく用いられます。先ほどの説明で、等号や不等号といった関係演算子をベースとする述語では、サブクエリは 1 行のみを生成するものでなければならないことを示しました。しかし、IN 演算子を使用すれば任意数の行を生成するサブクエリを使用することができるようになります。IN 演算子は含まれる値と含まれない値の集合を明示的に定義するものです。 sample.18-2 は受注した商品の単価が 400 円である受注番号を参照するものです。 -- sample.18-2 --
/* サブクエリ */
SELECT 受注番号 FROM 受注表
WHERE 商品コード IN
(SELECT 商品コード FROM 商品表
WHERE 単価 = 400);
/* 実行結果 */
受注番号
────
10001
10004
10006
10007
10009
10011
実習課題 1販売管理データベースから、支払い金額が全ての受注 (受注番号が 1001〜1018) に関する支払い金額の平均より大きい顧客の氏名と住所を表示する SQL 文を記述しなさい。支払い金額については、6章 1節の実習課題 2 を参照すること。 7.3. HAVING 句でのサブクエリHAVING 句にもサブクエリを使用することが可能です。使用方法は WHERE 句で使用していたのと同じです。 sample.18-3 は受注個数が顧客コード '002' の最小値よりも小さい注文をした顧客コードとその件数を参照するものです。 -- sample.18-3 --
/* サブクエリ */
SELECT 顧客コード,COUNT(DISTINCT 受注番号) FROM 受注表
GROUP BY 顧客コード
HAVING 受注個数 <
(SELECT MIN(受注個数) FROM 受注表
WHERE 顧客コード = '002');
/* 実行結果 */
顧客コード COUNT
───── ───
004 1
003 3
7.4. その他の場所でのサブクエリサブクエリは、条件句である WHERE 句や HAVING 句のみだけでなく、SELECT のすぐ後ろに続けて使用することもできます。サブクエリの結果を、メインクエリで直接表示させることができます。たとえば、受注表における全受注個数の平均値と、各顧客の受注個数の平均値を比べて見たい場合は、sample.18-4 のように記述します。 -- sample.18-4 --
/* サブクエリ */
SELECT 顧客コード,AVG(受注個数),
(SELECT AVG(受注個数) FROM 受注表)
FROM 受注表
GROUP BY 顧客コード;
/* 実行結果 */
顧客コード AVG(受注個数) AVG(受注個数)
───── ─────── ───────
001 280 155
002 155 155
003 57 155
004 135 155
また、サブクエリは FROM 句の後に続けて使用することもできます。サブクエリの結果セットを一つのテーブルとして、利用するものです。sample.18-5 は、受注表における各顧客の受注個数の平均の中で、最も大きい値を出力するものです。 -- sample.18-5 --
/* サブクエリ */
SELECT MAX(AVG_J) AS MAX_AVG
FROM (SELECT AVG(受注個数) AS AVG_J
FROM 受注表 GROUP BY 顧客コード) ;
/* 実行結果 */
MAX_AVG
────
280
実習課題 2販売管理データベースから、各営業所の売上ランキング表を作成する SQL 文を記述しなさい。各営業所が管理する顧客の支払い金額を合計したものを各営業所の売上とする。各営業所が管理する顧客は、customer テーブルを参照すること。表示する項目は、営業所名及び売上金額とする。 7.5. 相関サブクエリSQL にサブクエリを使用する場合、内部クエリから外部クエリの FROM 句のテーブルを参照する、相関サブクエリを構成することができます。この場合、メインクエリのテーブルの行ごとに、サブクエリが繰り返し実行されます。 sample.18-6 では納品日が '20010401' の商品を、相関サブクエリを用いて参照します。 -- sample.18-6 --
/* 相関サブクエリ */
SELECT * FROM 商品表 AA
WHERE '20010401' IN
(SELECT 納品日
FROM 受注表 BB
WHERE AA.商品コード = BB.商品コード);
/* 実行結果 */
商品コード 商品名 単価
───── ─── ──
101 BEER 500
102 JUICE 400
sample.18-6は少し複雑なので簡単に説明します。
このような手続きの結果、重複するデータがあっても表示されなくなります。 メインクエリとサブクエリで同じテーブルを使用する相関サブクエリというものもあります。テーブルの自己相関といいますが、複雑な形式の派生情報を抽出することができます。 sample.18-7 では、受注したものの中から受注個数が平均個数を超えるものを検索します。ちなみに、平均個数は 155個です。 -- sample.18-7 --
/* サブクエリ */
SELECT 受注番号,受注個数 FROM 受注表 XX
WHERE 受注個数 >
(SELECT AVG(受注個数)
FROM 受注表 YY);
/* 実行結果 */
受注番号 受注個数
──── ────
10001 300
10002 200
10006 250
10010 290
10012 175
HAVING 句にも相関サブクエリを用いることができます。sample.18-8 では、受注個数を納品日別に集計し、それぞれの合計が受注個数の最大値を 150 上回るものだけを参照するものです。 -- sample.18-8 --
/* サブクエリ */
SELECT SUM(受注個数),納品日
FROM 受注表 PP
GROUP BY 納品日
HAVING SUM(受注個数) >
(SELECT 150 + MAX(受注個数)
FROM 受注表 QQ
WHERE PP.納品日 = QQ.納品日);
/* 実行結果 */
SUM(受注個数) 納品日
─────── ────
650 20010401
485 20010402
490 20010403
実習課題 3販売管理データベースから、3台以上の自動車を販売した従業員の名前を表示する SQL 文を記述しなさい。
|
|
