ProgrammingSQL講座第3章　SQL構文
第3章　SQL構文

SELECT構文：サブクエリ構文

サブクエリ

サブクエリの基本的な使い方

サブクエリは、SELECT、INSERT、UPDATE、DELETEといった各ステートメントに含まれたSELECT句です。WHERE句やHAVING句の中、別のサブクエリの中など、様々な場所で利用できます。クエリに含まれるクエリなので、サブクエリと呼びます。

次に例を示します。

SELECT フィールド名 FROM テーブル名
WHERE フィールド名 演算子 ( SELECT フィールド名 FROM テーブル名2 ...);

WHERE句の「( SELECT 列名 FROM テーブル名2 ... )」という箇所がサブクエリで、サブクエリは、それ自身を括弧で囲む必要があります。ここでのサブクエリは、外側にあるSELECT句にネストされています。

等号や不等号といった演算子を使用するサブクエリは、返す値は1フィールドまでです。それ以上のフィールドを返す場合や、何も返す値がない場合は、ステートメントがエラーとなります。

下記のクエリは、購入履歴を対象に、[商品ID(id_g)]が一番小さい値のレコードを選択します。

SELECT id_p 
FROM purchase 
WHERE id_g = (SELECT MIN(id_g) FROM purchase); 
-> +------+
-> | id_p |
-> +------+
-> | 2 |
-> | 3 |
-> +------+

WHERE句の中のサブクエリは、MIN関数を使って購入履歴テーブルの中で最小の[商品ID(id_g)]の値を算出しています。その結果をWHERE句で受け取り、最小の[商品ID(id_g)]をもったレコードの[購入履歴ID(id_p)]を出力しています。このように、サブクエリを利用することで、SELECT文の結果を利用したSELECT文を1つのクエリで表現することがが可能です。

サブクエリを使用する主な利点は次のとおりです。

  • 本来なら複数のクエリを1つのクエリで表現することができる。
  • 複雑な結合や集合処理を行わずにクエリを記述できる。
  • 一般に可読性が高い。

SELECT句のサブクエリ

サブクエリはWHERE句のみだけではなく、SELECT句でもサブクエリを利用することができます。SELECT句でサブクエリを利用する場合、サブクエリの結果をメインクエリの結果として直接表示させることができます。

たとえば、下記の例では、顧客ごとの平均受注件数と全体の平均受注件数を出力しています。単一のSELECT文ではグループ化された結果と全体の結果を同時に取得できませんが、サブクエリは異なる値を同時に取得することが可能です。

SELECT id_c, AVG(quantity), ( SELECT AVG(quantity) FROM purchase) 
FROM purchase
GROUP BY id_c;
-> +------+---------------+---------------------------------------+
-> | id_c | AVG(quantity) | ( SELECT AVG(quantity) FROM purchase) |
-> +------+---------------+---------------------------------------+
-> | 1 | 3.0000 | 1.5000 |
-> | 2 | 1.0000 | 1.5000 |
-> | 3 | 1.0000 | 1.5000 |
-> | 4 | 1.5000 | 1.5000 |
-> +------+---------------+---------------------------------------+

FROM句のサブクエリ

FROM句のサブクエリは、そのサブクエリの結果をテーブルとして渡します。FROM句でのサブクエリの利用は応用性が高く、サブクエリの中でもっとも頻繁に利用される構文です。

次の例では、購入履歴テーブルを購入者でグループ化し、その平均値を算出しています。

SELECT AVG(detail_cnt) AS detail_avg
FROM ( SELECT COUNT(*) AS detail_cnt FROM purchase GROUP BY id_c ) AS t;
-> +------------+
-> | detail_avg |
-> +------------+
-> | 1.5000 |
-> +------------+

FROM句のサブクエリは、購入者テーブルを[購入者ID(id_c)]でグループ化し、SELETC句のAVG関数の引数に渡しています。サブクエリをFROM句に指定することで、その結果をテーブルとして扱うことが可能になります。

HAVING句でのサブクエリ

HAVING句にもサブクエリを使用することが可能です。使用方法はWHERE句でのサブクエリと同じです。

次の例は、[受注個数(quantity)]の平均が、顧客ごとの平均受注個数と同じか、それ以上の場合に選択するというクエリです。

SELECT id_c, AVG(quantity) AS quantity_avg FROM purchase
GROUP BY id_c
HAVING quantity_avg >= (SELECT AVG(quantity) FROM purchase);
-> +------+--------------+
-> | id_c | quantity_avg |
-> +------+--------------+
-> | 1 | 3.0000 |
-> | 4 | 1.5000 |
-> +------+--------------+

HAVINGでのサブクエリは、他のサブクエリと同様に、分割することができます。

先ほどクエリからサブクエリ部分だけを省いた、SELECT句部分だけのクエリを実行してみましょう。

SELECT id_c, AVG(quantity) AS quantity_avg FROM purchase
GROUP BY id_c;
-> +------+--------------+
-> | id_c | quantity_avg |
-> +------+--------------+
-> | 1 | 3.0000 |
-> | 2 | 1.0000 |
-> | 3 | 1.0000 |
-> | 4 | 1.5000 |
-> +------+--------------+

クエリの実行後、出力結果は顧客ごとの平均受注数です。ここでは平均受注数が1.0から3.0までの値があることがわかります。

次に、サブクエリ部分だけを実行してみましょう。サブクエリ部分は、顧客全体の平均受注数を算出しています。

SELECT AVG(quantity) FROM purchase;
-> 1.5000

以上で、顧客全体の平均受注数と、顧客ごとの平均受注数を算出できました。最初に紹介したサブクエリは、2つのクエリを1つにして、「[受注個数(quantity)]の平均が、顧客ごとの平均受注個数と同じか、それ以上の場合に選択」というクエリを実現しています。

 

関連記事