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

相関サブクエリとは、サブクエリの応用版で小分けにしたグループ内での比較処理を行う際に用いるテクニックの１つです。

このページでは、サブクエリってそもそも何？という方でも相関サブクエリについてよく理解できるよう順を追って１から図解付き・サンプルコード付きで解説します。

このページで学べる内容

相関サブクエリとは？
- 【前提】サブクエリ（副問い合わせ）とは？
相関サブクエリの仕組みを１からわかりやすく
相関サブクエリの使い方・サンプルコード

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

SQL文の基本ルール（大文字/小文字の区別、コメントの付与など）を始めに学んでおきたい方は以下の記事を先にご覧ください。

【前提】サブクエリ（副問い合わせ）とは？
相関サブクエリとは？
1. 【図解】相関サブクエリの動作
システムエンジニアを目指したい方は

【前提】サブクエリ（副問い合わせ）とは？

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

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

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

サブクエリを利用することで、以下のようなテーブルからクラスAの平均点を取得するようなことも可能になります。

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 |
+-------+---------------+-----------+--------------+
*/

参考：AVG関数/GROUP BY句

相関サブクエリとは？

まずは、相関サブクエリの実例をお見せします。

下記のテーブルから赤字部分（＝各クラスごとでJapanese列が最大のレコード）だけを抽出するSQL文です。

SELECT * FROM PointList AS T1
WHERE Japanese = (SELECT MAX(Japanese)
                  FROM PointList AS T2
                  WHERE T1.Class = T2.Class); #←ここの記載がポイント（外側のSQLと内側のSQLを比較）

/* 結果(各クラスで「Japanese列」が最大のレコードだけが抽出される)
+----+------+-------+----------+------+---------+
| ID | Name | Class | Japanese | Math | English |
+----+------+-------+----------+------+---------+
|  1 | 佐藤 | A     |      100 |  100 |      85 |
|  6 | 山口 | B     |       90 |   85 |      85 |
+----+------+-------+----------+------+---------+
*/

参考：MAX関数

４行目の記載が相関サブクエリを理解するポイントです。

【図解】相関サブクエリの動作

相関サブクエリは、その名の通り「相関」してサブクエリが動作します。言い換えると、外側のSQLと内側のSQL（サブクエリ）が「相関」して動くのが相関サブクエリです。

この意味は先ほどのSQLの動きを１レコード単位で確認していくと理解することができます。

SELECT * FROM PointList AS T1
WHERE Japanese = (SELECT MAX(Japanese)
                  FROM PointList AS T2
                  WHERE T1.Class = T2.Class);

まず最初の１レコードを取得する条件を考えます。

１行目のレコードを処理する際のWHERE句の値は「SELECT MAX(Japanese) FROM PointList AS T2 WHERE T1.Class = T2.Class;」となっています。したがって、外側のSQLの「ClassがAなので」サブクエリも「Class＝A」となります（＝相関します）。

SELECT MAX(Japanese)
  FROM PointList AS T2
 WHERE T1.Class = T2.Class;

/* １レコード目を処理する場合の結果
100
*/

内部的には以下のようなSQL文が発行されている考えることができ、結果としてJapanese列が「100」（＝Class AのJapanese列の最大値）となっていればWHERE条件に合致し取得対象に。そうでなければ、WHERE条件に合致しないため取得対象外になります。

SELECT * FROM PointList AS T1
WHERE Japanese = 100; 

# SELECT * FROM PointList AS T1
# WHERE Japanese = (SELECT MAX(Japanese)
#                   FROM PointList AS T2
#                   WHERE T1.Class = T2.Class);

１行目のレコードはJapanese列が「100」であるため取得対象となりますね。

では、続いて２行目のレコードについて考えてみます。

２行目のレコードについても考え方は同様です。

「SELECT MAX(Japanese) FROM PointList AS T2 WHERE T1.Class = T2.Class;」となっているので、外側のSQLの「Class」とサブクエリの「Class」が一致するもの（Class＝A）のMAX値が返されます。

ただし、２行目のレコードについてはJapanese列が「100」ではないため取得対象にはなりません。

/* 内部的な処理を可視化すると・・・
+----+------+-------+----------+--------------+
| ID | Name | Class | Japanese = MAX(Japanese |
+----+------+-------+----------+--------------+
|  1 | 佐藤 | A     |      100 =          100 |←Class「A」のMAX値と比較 〇条件に合致
|  2 | 鈴木 | A     |       90 =          100 |←Class「A」のMAX値と比較
+----+------+-------+----------+------+---------+

続いて、Classが「B」のレコードを考えてみます。

５行目のクラスBのレコードを処理する際には、今度は外側のSQLが「Class＝B」となるため、相関サブクエリの中でも「Class＝B」の中でのMAX値を求めることになります。

SELECT MAX(Japanese)
  FROM PointList AS T2
 WHERE T1.Class = T2.Class;

/* 5レコード目を処理する場合の結果(「Class = B」の中での最大値)
90
*/

したがって、相関サブクエリは5レコード目を処理する際は内部的には以下のようなSQL文が実行されていると考えることができます。

SELECT * FROM PointList AS T1
WHERE Japanese = 90; 

# SELECT * FROM PointList AS T1
# WHERE Japanese = (SELECT MAX(Japanese)
#                   FROM PointList AS T2
#                   WHERE T1.Class = T2.Class);

つまり、外側のSQLを処理する際に、WHERE条件の中身が変動する！（相関して値が変わる）と説明することが可能。

# Class Aの場合
SELECT * FROM PointList AS T1
WHERE Japanese = 100;  # ←相関サブクエリが内部的に返す結果 

# Class Bの場合
SELECT * FROM PointList AS T1
WHERE Japanese = 90;  # ←相関サブクエリが内部的に返す結果

# もとの相関サブクエリ
# SELECT * FROM PointList AS T1
# WHERE Japanese = (SELECT MAX(Japanese)
#                   FROM PointList AS T2
#                   WHERE T1.Class = T2.Class);

再度、先ほど見た相関サブクエリの例を確認してみます。

SELECT * FROM PointList AS T1
WHERE Japanese = (SELECT MAX(Japanese)
                  FROM PointList AS T2
                  WHERE T1.Class = T2.Class); #←ここの記載がポイント

/* 結果(各クラスで「Japanese列」が最大のレコードだけが抽出される)
+----+------+-------+----------+------+---------+
| ID | Name | Class | Japanese | Math | English |
+----+------+-------+----------+------+---------+
|  1 | 佐藤 | A     |      100 |  100 |      85 |
|  6 | 山口 | B     |       90 |   85 |      85 |
+----+------+-------+----------+------+---------+
*/

/* 内部的な処理を可視化すると・・・
+----+------+-------+----------+--------------+
| ID | Name | Class | Japanese = MAX(Japanese |
+----+------+-------+----------+--------------+
|  1 | 佐藤 | A     |      100 =          100 |←Class「A」のMAX値と比較 〇条件に合致
|  2 | 鈴木 | A     |       90 =          100 |←Class「A」のMAX値と比較
|  3 | 高橋 | A     |       85 =          100 |←Class「A」のMAX値と比較
|  4 | 中村 | A     |       90 =          100 |←Class「A」のMAX値と比較
|  5 | 小林 | B     |       70 =           90 |←Class「A」のMAX値と比較
|  6 | 山口 | B     |       90 =           90 |←Class「A」のMAX値と比較〇条件に合致
|  7 | 田中 | B     |       70 =           90 |←Class「A」のMAX値と比較
|  8 | 伊藤 | B     |       70 =           90 |←Class「A」のMAX値と比較
+----+------+-------+----------+------+---------+

相関サブクエリのまとめ