PR

【SQL】サブクエリ(副問い合わせ)を3分でわかりやすく解説

Database

SQLのサブクエリ(副問い合わせ)とは「SQL文中にまた別のSQLを入れ子にして記述すること」です。

例えば、以下のようなSQLがサブクエリの一例です。(SELECT文の中に別のSELECT文が記述されています。)

SELECT * FROM (SELECT ID FROM PointList) AS Sub;

サブクエリを利用できるようになると、単純にSELECT文を記述するだけではできない複雑なレコード抽出を行うことが可能になります。

このページではSQL初心者向けにサブクエリ(副問い合わせ)の使い方を1からわかりやすくサンプルコード付きで3分で解説。自分ひとりでサブクエリを使ったSQL文を記述できるようにご説明します。

このページで学べる内容
  • サブクエリ(副問い合わせ)とは?
    • サブクエリの構文ルール
  • サブクエリ(副問い合わせ)の利用方法

データベースエンジニアを目指す方であれば知らないと恥ずかしい超・基本知識です。是非最後までご覧ください。

スポンサーリンク

サブクエリ(副問い合わせ)をわかりやすく

図1:サブクエリのイメージ

サブクエリとは、SQLの中に記述する別のSQL。言い換えると一時的なビューのようなものです。

例えば以下のSQL文はSELECT文の中にSELECT文が記述されているため、サブクエリの一例です。

SELECT * FROM (SELECT ID FROM PointList) AS Sub;

と、説明してもこれだけではいまいち理解できないかと思いますので、具体例を見ながら1個1個わかりやすく説明していきます。

まずは、以下のようなテーブルを用意します。

サブクエリ,副問い合わせ,わかりやすく
サンプルテーブル「PointList」

参考までに上記のPointListテーブルを用意するためのCREATE TABLE文/INSERT文を掲載しておきます。

-- PointList定義
CREATE TABLE PointList (
ID       INT(3)       NOT NULL PRIMARY KEY,
Name     VARCHAR(50)  NOT NULL,
Class    VARCHAR(1),
Japanese INT(3)       DEFAULT '0',
Math     INT(3)       DEFAULT '0', 
English  INT(3)       DEFAULT '0'
);

/*PointList定義
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int(3)      | NO   | PRI | NULL    |       |
| Name     | varchar(50) | NO   |     | NULL    |       |
| Class    | varchar(1)  | YES  |     | NULL    |       |
| Japanese | int(3)      | YES  |     | 0       |       |
| Math     | int(3)      | YES  |     | 0       |       |
| English  | int(3)      | YES  |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
*/

-- PointListへレコード登録
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (1,'佐藤','A',100,100,85);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (2,'鈴木','A',90,70,70);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (3,'高橋','A',85,70,65);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (4,'中村','A',90,65,85);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (5,'小林','B',70,90,65);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (6,'山口','B',90,85,85);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (7,'田中','B',70,90,65);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (8,'伊藤','B',70,90,NULL);

-- 結果確認
SELECT * FROM PointList;
/*
+----+------+-------+----------+------+---------+
| ID | Name | Class | Japanese | Math | English |
+----+------+-------+----------+------+---------+
|  1 | 佐藤 | A     |      100 |  100 |      85 |
|  2 | 鈴木 | A     |       90 |   70 |      70 |
|  3 | 高橋 | A     |       85 |   70 |      65 |
|  4 | 中村 | A     |       90 |   65 |      85 |
|  5 | 小林 | B     |       70 |   90 |      65 |
|  6 | 山口 | B     |       90 |   85 |      85 |
|  7 | 田中 | B     |       70 |   90 |      65 |
|  8 | 伊藤 | B     |       70 |   90 |    NULL |
+----+------+-------+----------+------+---------+
*/
-- PointList定義
CREATE TABLE PointList (
ID       INT(3)       NOT NULL PRIMARY KEY,
Name     VARCHAR(50)  NOT NULL,
Class    VARCHAR(1),
Japanese INT(3)       DEFAULT '0',
Math     INT(3)       DEFAULT '0', 
English  INT(3)       DEFAULT '0'
);

/*PointList定義
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int(3)      | NO   | PRI | NULL    |       |
| Name     | varchar(50) | NO   |     | NULL    |       |
| Class    | varchar(1)  | YES  |     | NULL    |       |
| Japanese | int(3)      | YES  |     | 0       |       |
| Math     | int(3)      | YES  |     | 0       |       |
| English  | int(3)      | YES  |     | 0       |       |
+----------+-------------+------+-----+---------+-------+
*/

-- PointListへレコード登録
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (1,'佐藤','A',100,100,85);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (2,'鈴木','A',90,70,70);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (3,'高橋','A',85,70,65);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (4,'中村','A',90,65,85);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (5,'小林','B',70,90,65);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (6,'山口','B',90,85,85);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (7,'田中','B',70,90,65);
INSERT INTO PointList (ID,Name,Class,Japanese,Math,English) VALUES (8,'伊藤','B',70,90,NULL);

-- 結果確認
SELECT * FROM PointList;
/*
+----+------+-------+----------+------+---------+
| ID | Name | Class | Japanese | Math | English |
+----+------+-------+----------+------+---------+
|  1 | 佐藤 | A     |      100 |  100 |      85 |
|  2 | 鈴木 | A     |       90 |   70 |      70 |
|  3 | 高橋 | A     |       85 |   70 |      65 |
|  4 | 中村 | A     |       90 |   65 |      85 |
|  5 | 小林 | B     |       70 |   90 |      65 |
|  6 | 山口 | B     |       90 |   85 |      85 |
|  7 | 田中 | B     |       70 |   90 |      65 |
|  8 | 伊藤 | B     |       70 |   90 |    NULL |
+----+------+-------+----------+------+---------+
*/

サブクエリ(副問い合わせ)を理解できるように、まずはこのテーブルからClassごとの平均点を取得するようなビューを定義する場合を考えます。

CREATE VIEW文とAVG関数を活用して以下のようなビューを作成します。

CREATE VIEW AVG_PointList
 AS SELECT Class, AVG(Japanese), AVG(Math), AVG(English) FROM PointList
   GROUP BY Class;

-- ビューの確認
SELECT * FROM AVG_PointList;
/*
+-------+---------------+-----------+--------------+
| Class | AVG(Japanese) | AVG(Math) | AVG(English) |
+-------+---------------+-----------+--------------+
| A     |       91.2500 |   76.2500 |      76.2500 |
| B     |       75.0000 |   88.7500 |      71.6667 |
+-------+---------------+-----------+--------------+
*/

参考:CREATE VIEW文 / AVG関数

クラス A / B それぞれの平均点の一覧が分かるビューが完成です。

さらにこのとき、Class「A」だけの平均点を知りたいような場合を考えてみます。

出来上がるのが以下のSQLです。

-- Class[A]のみの平均点を取得
SELECT * FROM AVG_PointList WHERE Class = 'A';

/* 結果
+-------+---------------+-----------+--------------+
| Class | AVG(Japanese) | AVG(Math) | AVG(English) |
+-------+---------------+-----------+--------------+
| A     |       91.2500 |   76.2500 |      76.2500 |
+-------+---------------+-----------+--------------+
*/

先ほど定義したビュー「AVG_PointList」のClass列が「A」となるレコードをWHERE句で指定するSELECT文です。

実は、今回解説するサブクエリ(副問い合わせ)を使うことでここまで見てきたビュー定義とビューからのレコード抽出を1つのSELECT文で実行することができます。

早速、上記と同じレコード抽出文をサブクエリを使って記述した結果がこちら。

SELECT * FROM
(SELECT Class, AVG(Japanese), AVG(Math), AVG(English) FROM PointList
 GROUP BY Class)
AS Sub
WHERE Class = 'A';

/* 結果→先ほどと同じ結果が取得できる
+-------+---------------+-----------+--------------+
| Class | AVG(Japanese) | AVG(Math) | AVG(English) |
+-------+---------------+-----------+--------------+
| A     |       91.2500 |   76.2500 |      76.2500 |
+-------+---------------+-----------+--------------+

いかがでしょうか。先ほどと同じような結果を取得することができました。

なぜこんなことが可能になるのでしょう。先ほどのビューに対して発行したSQLと見比べてみるとその理由が一目瞭然です。

サブクエリ 難しい
サブクエリは一時的な使い捨てのビュー

簡単に説明するとサブクエリとは一時的な使い捨てのビューのようなもの。ビュー定義のSELECT文をそのまま別のSELECT文に入れてしまったのがサブクエリ(副問い合わせ)の正体です。

決して考え方は難しくはありません。

先ほどは、テーブルからビュー「AVG_PointList」を定義し、そのビューに対してSELECT文を発行していましたが、サブクエリを利用すれば、一時的に使い捨てのビュー(Sub)を作成し、同時にそのビューに対してSELECT文を発行することができるということ。

もう1度、サブクエリを利用したSQLとそうでないSQLを見比べてみましょう。

-- ①ビューに対するSQL
SELECT * FROM AVG_PointList WHERE Class = 'A';

-- ②サブクエリを利用したSQL
SELECT * FROM
(SELECT Class, AVG(Japanese), AVG(Math), AVG(English) FROM PointList
 GROUP BY Class)
AS Sub
WHERE Class = 'A';

/* 結果→先ほど同じ結果が取得できる
+-------+---------------+-----------+--------------+
| Class | AVG(Japanese) | AVG(Math) | AVG(English) |
+-------+---------------+-----------+--------------+
| A     |       91.2500 |   76.2500 |      76.2500 |
+-------+---------------+-----------+--------------+

サブクエリ構文ルール

サブクエリの構文ルールは簡単です。( )で閉じて直後にAS句でサブクエリ名称を指定するだけ。

~~~(SELECT句)AS サブクエリ名称~~~

SELECT * FROM
(SELECT A, B, C FROM Sample)
AS Sub;

サブクエリの名称に特に決まりはありませんが、サブクエリはあくまでも一時的に利用するだけの使い捨てなので「Sub」と指定することが多いです。

SELECT * FROM
(SELECT Class, AVG(Japanese), AVG(Math), AVG(English) FROM PointList
 GROUP BY Class)
-- サブクエリの名称はなんでもOK
AS TestName
WHERE Class = 'A'
ポイント
  • サブクエリは一時的な使い捨てのビューのようなもの
サブクエリ,副問い合わせ

サブクエリの利用方法

サブクエリの基本をより具体例にイメージできるようにもう1つだけ具体例を提示して解説を終えたいと思います。

今回はPointListテーブルに加えて「ClassMember」テーブルを用意します。

サブクエリ,副問い合わせ
サンプルテーブル②

テーブル定義:参考(CREATE TABLE

-- ClassMember定義
CREATE TABLE ClassMember (
ID       INT(3)       NOT NULL PRIMARY KEY,
Name     VARCHAR(50)  NOT NULL,
Class    VARCHAR(1),
Gender   VARCHAR(1),
Address  VARCHAR(4) 
);

/* ClassMember定義
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ID      | int(3)      | NO   | PRI | NULL    |       |
| Name    | varchar(50) | NO   |     | NULL    |       |
| Class   | varchar(1)  | YES  |     | NULL    |       |
| Gender  | varchar(1)  | YES  |     | NULL    |       |
| Address | varchar(4)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
*/

利用するダミーレコード:参考(INSERT文

-- ClassMemberへレコード登録
INSERT INTO ClassMember (ID,Name,Class,Gender,Address) VALUES (1,'佐藤','A','男','東京都');
INSERT INTO ClassMember (ID,Name,Class,Gender,Address) VALUES (2,'鈴木','A','女','東京都');
INSERT INTO ClassMember (ID,Name,Class,Gender,Address) VALUES (3,'高橋','A','男','東京都');
INSERT INTO ClassMember (ID,Name,Class,Gender,Address) VALUES (4,'中村','A','女','埼玉県');
INSERT INTO ClassMember (ID,Name,Class,Gender,Address) VALUES (5,'小林','B','男','埼玉県');
INSERT INTO ClassMember (ID,Name,Class,Gender,Address) VALUES (6,'山口','B','女','埼玉県');
INSERT INTO ClassMember (ID,Name,Class,Gender,Address) VALUES (7,'田中','B','男','神奈川県');
INSERT INTO ClassMember (ID,Name,Class,Gender,Address) VALUES (8,'伊藤','B','女','神奈川県');

-- 結果確認
SELECT * FROM ClassMember;
/*
+----+------+-------+--------+----------+
| ID | Name | Class | Gender | Address  |
+----+------+-------+--------+----------+
|  1 | 佐藤 | A     | 男     | 東京都   |
|  2 | 鈴木 | A     | 女     | 東京都   |
|  3 | 高橋 | A     | 男     | 東京都   |
|  4 | 中村 | A     | 女     | 埼玉県   |
|  5 | 小林 | B     | 男     | 埼玉県   |
|  6 | 山口 | B     | 女     | 埼玉県   |
|  7 | 田中 | B     | 男     | 神奈川県 |
|  8 | 伊藤 | B     | 女     | 神奈川県 |
+----+------+-------+--------+----------+
*/

サブクエリを利用して、2つのテーブルをもとに「東京都」出身の生徒の成績を出力してみたいと思います。

出来上がるSQL文がこちら。

-- 
SELECT * FROM
  (SELECT PointList.* ,ClassMember.Address FROM PointList
   LEFT OUTER JOIN ClassMember
   ON PointList.ID = ClassMember.ID) AS Sub
WHERE Address = '東京都';

/* 結果
+----+------+-------+----------+------+---------+---------+
| ID | Name | Class | Japanese | Math | English | Address |
+----+------+-------+----------+------+---------+---------+
|  1 | 佐藤 | A     |      100 |  100 |      85 | 東京都  |
|  2 | 鈴木 | A     |       90 |   70 |      70 | 東京都  |
|  3 | 高橋 | A     |       85 |   70 |      65 | 東京都  |
+----+------+-------+----------+------+---------+---------+
*/

今回もサブクエリをFROM句で利用しています。

このFROM句に記載されたSELECT文は、PointListテーブルとClassMemberテーブルを外部結合した結果を返します。

したがって、以下のようなビューが一時的に定義され、そこから「Address = 東京都」となるレコードを引っ張ってきているということです。

CREATE VIEW SampleView
  AS SELECT PointList.* ,ClassMember.Address FROM PointList
  LEFT OUTER JOIN ClassMember
               ON PointList.ID = ClassMember.ID;


SELECT * FROM SampleView;
/*
+----+------+-------+----------+------+---------+----------+
| ID | Name | Class | Japanese | Math | English | Address  |
+----+------+-------+----------+------+---------+----------+
|  1 | 佐藤 | A     |      100 |  100 |      85 | 東京都   |←先ほどのSQLはここだけ取得
|  2 | 鈴木 | A     |       90 |   70 |      70 | 東京都   |←先ほどのSQLはここだけ取得
|  3 | 高橋 | A     |       85 |   70 |      65 | 東京都   |←先ほどのSQLはここだけ取得
|  4 | 中村 | A     |       90 |   65 |      85 | 埼玉県   |
|  5 | 小林 | B     |       70 |   90 |      65 | 埼玉県   |
|  6 | 山口 | B     |       90 |   85 |      85 | 埼玉県   |
|  7 | 田中 | B     |       70 |   90 |      65 | 神奈川県 |
|  8 | 伊藤 | B     |       70 |   90 |    NULL | 神奈川県 |
+----+------+-------+----------+------+---------+----------+
*/
サブクエリ(副問い合わせ)のまとめ
  • サブクエリはSQL文中に記載された別のSQL文のこと
  • サブクエリは一時的に使い捨てされるビューのようなものなので必ず仮の名称を指定する必要がある
サブクエリの応用編

サブクエリ(副問い合わせ)を細かく分類していくと、「スカラ・サブクエリ」や「相関サブクエリ」なるサブクエリも存在します。

  • スカラ・サブクエリ
    →結果が1行1列となるサブクエリ
  • 相関サブクエリ
    →外側のSQLと「相関」して処理を実施するサブクエリ

システムエンジニアを目指したい方は

システムエンジニアを目指す方や、IT知識を1から身につけたい方は以下のページをご覧ください。

正直どこから学び始めればよいかわからない。どのように勉強していけば、エンジニアとしてのスキルが磨けるか?が分からない・・・という方は必見です。

このWebサイトは現役のエンジニアが以下3点を目的として運営しています。

  1. 勉強:一度理解した内容を忘れないように。
    → アウトプットは「最強のインプット」である! 
  2. 備忘:忘れたとしても後から見返せるように。
    → 未来の自分への「お手紙」を書いています。 
  3. 共有:〇〇ってこうだったんだ!の感動をシェアできるように。
    → あなたの知識は誰かにとっての「価値ある情報」です。 

副業ブログの始め方はこちらから

スポンサーリンク
DatabaseIT-Skills
シェアする
ビズドットオンラインをフォローする
blank
ビズドットオンライン
タイトルとURLをコピーしました