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

Database

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

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

このページで学べる内容
  • 相関サブクエリとは?
    • 【前提】サブクエリ(副問い合わせ)とは?
  • 相関サブクエリの仕組みを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文です。

相関サブクエリとは わかりやすく
サンプルテーブル「PointList」
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関数

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

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

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

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

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

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

相関サブクエリとは
1行目のレコード

1行目のレコードを処理する際の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;

/* 1レコード目を処理する場合の結果
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);

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

相関サブクエリとは

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

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

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

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

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

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

5行目のクラス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値と比較
+----+------+-------+----------+------+---------+
相関サブクエリのまとめ
  • 相関サブクエリ(相関副問い合わせ)はサブクエリの一種
  • 外側のSQLと内側のSQL(サブクエリ)が「相関」して動くのが相関サブクエリ
    • レコード1行1行に対する処理ロジックを考えることでわかりやすく理解することが可能

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

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

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

タイトルとURLをコピーしました