サブクエリ

本章では、複数の検索を組み合わせて実行することができるサブクエリについて解説します。
本章の目標
  • サブクエリの概念を理解する
  • 「WHERE」句でのサブクエリの使い方をおぼえる
  • 「FROM」句でのサブクエリの使い方をおぼえる
  • 「SELECT」句でのサブクエリの使い方をおぼえる
  • 「HAVING」句でのサブクエリの使い方をおぼえる
ページの先頭へ
ページの先頭へ

1.サブクエリの基本

まずはサブクエリの考え方と基本的な使用方法を説明します。

1. サブクエリとは

データベースに対する処理の命令要求(DML文の実行)をクエリといいます。 SQLでは、あるクエリ(サブクエリ)の実行結果を基に、別のクエリ(メインクエリ)を制御することができます。
下図を見てください。


この例では、サブクエリのSELECT文の検索結果を、メインクエリの検索条件としています。
このように、クエリを入れ子にしてサブクエリの実行結果を基にメインクエリを処理することができるのです。
サブクエリには様々な使い方がありますので、次項以降で具体的な例で説明していきます。

2. サブクエリの具体例

まずサブクエリの中では最も基本となる、SELECT文の「WHERE」句で使う方法を、具体的な例を見ていきながら説明します。
基本的な構文は下記のようになります。

SELECT 列名 FROM テーブル名 WHERE 列名 演算子 サブクエリ

「bookinfo」を例にして説明します。
ISBN番号「00001」の「SQL入門テキスト」と同じ価格の書籍を検索したい場合を考えます。
まず「SQL入門テキスト」の価格を検索する必要があります。
DML文は下記のようになりますね。

SELECT price FROM bookinfo WHERE isbn = '00001';

これで「SQL入門テキスト」の価格が抽出できます。


さらに同一価格の書籍を抽出するには、

SELECT * FROM bookinfo WHERE price = 1050;

となります。


この2つのDML文

SELECT price FROM bookinfo WHERE isbn = '00001';→SQL①
SELECT * FROM bookinfo WHERE price = 1050;→SQL②

を、「SQL①」をサブクエリ、「SQL②」をメインクエリとしてひとつにまとめると、下記のようなDML文になります。

SELECT * FROM bookinfo WHERE price = (SELECT price FROM bookinfo WHERE isbn = '00001');





ではISBN番号「00002」の「Java Struts基礎テキスト」より価格の高い書籍を抽出したい場合はどうなるでしょうか?
DML文は下記のようになります。

SELECT * FROM bookinfo WHERE price > (SELECT price FROM bookinfo WHERE isbn = '00002');






ページの先頭へ

2.サブクエリの発展

ここからは、サブクエリの発展的な使い方を説明します。

1. 「FROM」句でのサブクエリ

前節では「WHERE」句の条件としてサブクエリを使用しましたが、サブクエリは「WHERE」句だけで使われるわけではありません。
まず「FROM」句での使用例から見ていきます。
「FROM」句でサブクエリを使用する場合の書式は下記のようになります。

SELECT 列名 FROM サブクエリ

例えば「bookinfo」と「orderinfo」を結合して注文金額を算出し、ユーザーごとの受注金額を集計した後にその受注金額の最大値を取得するケースを考えてみましょう。
受注金額の集計までは、これまで学習したDDL文で取得することができます。

SELECT B.user,SUM(A.price*B.quantity)
FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn
GROUP BY B.user;





この集計した値を元に最大の注文金額を取得するには、先に示したDDL文をサブクエリとして「FROM」句に入れ、テーブルと見做すことで実現できます。
DDL文は下記のようになります。

SELECT MAX(C.amount) FROM
(SELECT B.user,SUM(A.price*B.quantity) AS amount
FROM bookinfo A INNER JOIN orderinfo B ON A.isbn=B.isbn
GROUP BY B.user
) C;






2. 「SELECT」句でのサブクエリ

次に「SELECT」句でのサブクエリの使用方法について見てみましょう。
書式は下記のようになります。

SELECT サブクエリ FROM テーブル名;

「orderinfo」でユーザー毎の受注数量と全体の受注数量を対比して見たい場合などは下記のようなサブクエリで取得することができます。

SELECT user,SUM(quantity),(SELECT SUM(quantity) FROM orderinfo) FROM orderinfo GROUP BY user;






3. 「HAVING」句でのサブクエリ

「HAVING」句でもサブクエリが使えます。
書式は下記のようになります。

SELECT グループ化キー,集計関数(列名) FROM テーブル名 GROUP BY グループ化キー
HAVING 集計関数(列名) 演算子 サブクエリ;

「orderinfo」でユーザー毎の受注数量と全体の受注数量を対比して見たい場合などは下記のようなサブクエリで取得することができます。
「orderinfo」で、注文数が平均注文数を下回っているユーザーを取得したい場合などは、下記のようなDDL文で取得可能です。

SELECT user,SUM(quantity) FROM orderinfo GROUP BY user
HAVING SUM(quantity) < (SELECT AVG(quantity) FROM orderinfo);








ページの先頭へ

3.本章のまとめ

サブクエリはプログラムを作成する上で必須というわけではありませんし、イメージが湧きにくく、少し取っ付きにくいところがありますので、 敬遠されがちですが、理解してものにできればより複雑な検索を可能にする強力な武器になります。
ぜひともマスターして、開発工数の短縮を目指してください。

ページの先頭へ