皆さんこんにちは！👋
しばらく投稿していませんでした。ここ数日、SQLを基礎から見直しています。ただ問題を解くだけでなく、さまざまなSQLの概念がなぜ存在し、いつ使うべきかを理解しようとしています。
特に混乱した（そして多くの初心者が混乱する）トピックが、相関サブクエリでした。
次のようなクエリを見たことがあるかもしれません：
SELECT ...
FROM table1 t1
WHERE EXISTS (
SELECT *
FROM table2 t2
WHERE t2.id = t1.id
);
Enter fullscreen mode Exit fullscreen mode
そして疑問に思ったかもしれません：
内部クエリは外部クエリの列にどのようにアクセスしているのか？
内部クエリはいつ実行されるのか？
通常のサブクエリとはどう違うのか？
この記事では、LeetCodeの実践的なSQL問題を解きながら、これらの質問すべてに答えます。
この記事を読み終える頃には、次のことを理解できるようになります：
- ✅ 相関サブクエリとは何か
- ✅ 相関サブクエリが内部でどのように動作するか
- ✅ SQLの論理的な実行順序
- ✅ 相関サブクエリをいつ使うべきか
- ✅ ウィンドウ関数との比較
それでは始めましょう！
前提条件
この記事では、次の知識を前提としています：
- 基本的な
SELECT文WHEREGROUP BY- 集計関数（
MIN、MAX、COUNTなど）これらに慣れていれば、相関サブクエリを学ぶ準備ができています。
目次
- なぜ相関サブクエリは混乱しやすいのか？
- 相関サブクエリをゼロから理解する
- 相関サブクエリと非相関サブクエリの違い
- SQLの論理的な実行順序（相関サブクエリの位置づけ）
- LeetCode 3421を例題として使用する
- SQLを書く前に...
- 完全注釈付きの解答
- 実データを使ったクエリのステップバイステップ解説
- パフォーマンスの議論：相関サブクエリとウィンドウ関数の比較
- 結論と練習問題
1. なぜ相関サブクエリは混乱しやすいのか？
SQLを学び始めた当初、相関サブクエリは魔法のように感じられました。
次のような疑問が常に頭に浮かびました：
- 内部クエリは外部クエリの列にどのようにアクセスできるのか？
- 内部クエリはいつ実行されるのか？
- 1回だけ実行されるのか、それとも複数回実行されるのか？
- なぜ単独で実行できないのか？
これらの質問を自分に問いかけたことがあるなら、心配はいりません。この記事を読み終える頃には、すべてに答えが出ているはずです。
サブクエリとは？
サブクエリとは、別のSQL文の中にネストされた単なる SELECT 文です。まだ怖くありません：
SELECT name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
Enter fullscreen mode Exit fullscreen mode
ここでは、内部クエリ (SELECT AVG(salary) FROM Employees) は1回だけ実行され、単一の数値（例：55000）を生成し、外部クエリはその数値を使用するだけです。内部クエリは外部クエリが現在どの行を見ているかに関心を持ちません。これは非相関（または「単純」）サブクエリであり、完全に独立しており、単独で実行できます。
サブクエリを「相関」させるものは何か？
💡 重要な考え方
相関サブクエリは外部クエリの値に依存します。
そのため、独立して実行することはできません。外部クエリの各行ごとに再実行され、その行の値を使用します。
SELECT e.name, e.salary, e.department_id
FROM Employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM Employees e2
WHERE e2.department_id = e.department_id -- 👈 外部行 `e` を参照
);
Enter fullscreen mode Exit fullscreen mode
この内部クエリを単独で読んでみましょう：
SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id
Enter fullscreen mode Exit fullscreen mode
e.department_id はこのサブクエリ自身の FROM Employees e2 の中には存在しません。外部クエリから「借りて」います。これが決定的な特徴です。概念的には、パラメータを持つ関数のように考えることができます：
find_avg_salary_for(department_id):
return AVG(salary) WHERE department_id = department_id
Enter fullscreen mode Exit fullscreen mode
そしてSQLはこの「関数」を、外部クエリが処理するすべての行に対して1回呼び出し、その行の department_id を代入します。これがこの概念のすべてです。その他のすべてはこの考え方の装飾にすぎません。
簡単なメンタルモデル
外部クエリを for ループ、相関サブクエリをそのループの内部で実行され、ループ変数を使用するコードとして考えてみましょう：
# これは実際のSQLではありません — 単なるメンタルモデル
for row in Employees:
dept_avg = AVG(salary WHERE department_id == row.department_id)
if row.salary > dept_avg:
emit(row.name, row.salary, row.department_id)
Enter fullscreen mode Exit fullscreen mode
このメンタルモデルが、相関サブクエリが高コストになり得る理由を正確に説明しています。データベースが最適化できない場合、実際の実行方法に非常に近いからです（詳細はパフォーマンスのセクションで説明します）。
まとめ
相関サブクエリは、外部クエリが生成した各行（またはグループ）ごとに1回実行され、その行の値を入力として使用します。
| 非相関サブクエリ | 相関サブクエリ | |
|---|---|---|
| 外部クエリを参照するか？ | ❌ しない | ✅ する |
| 単独で実行可能か？ | ✅ 可能 | ❌ 不可能 |
| 実行頻度 | 全体で1回 | 外部の各行ごとに1回（概念上） |
| 典型的な用途 | グローバルな値との比較（例：全体平均） | その行のグループに限定された値との比較（例：その部門の平均、その学生の最大日付） |
非相関の例 — 「会社全体の平均より多く稼ぐ従業員」：
SELECT name FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
Enter fullscreen mode Exit fullscreen mode
相関の例 — 「自分の部門の平均より多く稼ぐ従業員」：
SELECT e.name FROM Employees e
WHERE e.salary > (
SELECT AVG(salary) FROM Employees e2 WHERE e2.department_id = e.department_id
);
Enter fullscreen mode Exit fullscreen mode
同じ形状ですが、決定的な違いがあります：WHERE e2.department_id = e.department_id の行です。この1行が「グローバル」と「行ごとのスコープ」のすべての違いを生み出します。
まとめ
サブクエリが単独で実行でき、合理的な結果を返せるなら、それは非相関です。外部行からの値が必要でなければ意味をなさないなら、それは相関です。
4. SQLの論理的な実行順序（サブクエリの位置づけ）
これは多くの人を混乱させるので、しっかり理解しましょう。SELECT 文は上から下に書かれます（SELECT → FROM → WHERE → GROUP BY → ...）が、その順序で実行されるわけではありません。MySQL（およびほとんどのSQLエンジン）が実際に評価する論理的な順序は次のとおりです：
① FROM （およびJOIN）— 作業行セットを構築
↓
② WHERE — 個々の行をフィルタ
↓
③ GROUP BY — 行をグループにバケット化
↓
④ HAVING — グループをフィルタ
↓
⑤ SELECT — 出力列を計算
（SELECTリスト内のサブクエリがここで実行される）
↓
⑥ ORDER BY — 最終結果をソート
↓
⑦ LIMIT — 結果をトリム
Enter fullscreen mode Exit fullscreen mode
SELECT リスト内に存在する相関サブクエリ（以下の解答のものなど）は、ステップ⑤で評価されます。FROM → WHERE → GROUP BY → HAVING を通過した各行/グループごとに評価されます。これが私たちの問題の重要なポイントです：
💡 重要な考え方
SELECT内の相関サブクエリが実行される時点で、MySQLはすでにどの(student_id, subject)グループが存在するかを（GROUP BYのおかげで）把握しています。サブクエリは各グループに対して「この特定の学生と科目の最初のスコアと最新のスコアは何か？」と質問されるだけです。
それだけです。クエリ全体は実際には次のことを行っています：1) student+subjectでグループ化、2) 各グループに対して2つのサイドクエスチョンを実行。
まとめ
SELECT リスト内の相関サブクエリは、フィルタリングとグループ化をすでに通過した各グループに対して、ステップ⑤で実行されます。
5. LeetCode 3421を例題として使用：改善した学生を見つける
テーブル：Scores
| カラム名 | 型 |
|---|---|
| student_id | int |
| subject | varchar |
| score | int |
| exam_date | varchar |
(student_id, subject, exam_date) が主キーです。各行は1人の学生の1科目の1回の試験日におけるスコアを表します。score は0から100の範囲です。
タスク：改善した学生を見つけます。学生が改善したとみなされるのは、特定の科目について次の両方が真の場合です：
- その科目で少なくとも2つの異なる日付に試験を受けた
- その科目の最新スコアが最初のスコアより高い
student_id, subject, first_score, latest_score を返し、student_id、subject の昇順でソートします。
入力例：
| student_id | subject | score | exam_date |
|---|---|---|---|
| 101 | Math | 70 | 2023-01-15 |
| 101 | Math | 85 | 2023-02-15 |
| 101 | Physics | 65 | 2023-01-15 |
| 101 | Physics | 60 | 2023-02-15 |
| 102 | Math | 80 | 2023-01-15 |
| 102 | Math | 85 | 2023-02-15 |
| 103 | Math | 90 | 2023-01-15 |
| 104 | Physics | 75 | 2023-01-15 |
| 104 | Physics | 85 | 2023-02-15 |
期待される出力：
| student_id | subject | first_score | latest_score |
|---|---|---|---|
| 101 | Math | 70 | 85 |
| 102 | Math | 80 | 85 |
| 104 | Physics | 75 | 85 |
除外されたものに注目してください：
- 101 / Physics — 2回受験したが、スコアが低下した（65 → 60）。改善とはみなされない。
- 103 / Math — 試験を1回しか受けていない。「少なくとも2つの異なる日付」の条件を満たさない。
状態としては単純ですが、核心的な難しさがあります：各 (student_id, subject) ペアについて、同じテーブルに戻ってその特定のグループの最小日付と最大日付を見つけ、それらの日付に結びついたスコアを取得する必要があります。この「同じテーブルに戻るが、現在の行のグループにスコープされる」という操作は、相関サブクエリがまさにそのために作られています。
6. SQLを書く前に...
一旦SQLのことは忘れましょう。
C++、Java、Pythonでこれを書くとしたらどうしますか？
おそらく次のようなものになるでしょう：
各学生について
各科目について
earliest_exam
latest_exam
first_score
latest_score
if latest_score > first_score
print()
Enter fullscreen mode Exit fullscreen mode
SQLは単にこのアルゴリズムを表現する別の方法です。GROUP BY が 各学生について / 各科目について の部分を提供し、相関サブクエリが earliest_exam、latest_exam、first_score、latest_score のルックアップを提供します。このループを頭の中に留めておいてください。以下のクエリがずっと抽象的でなくなるはずです。
7. 完全注釈付きの解答
完全なクエリ
WITH t AS (
SELECT
student_id,
subject,
-- 相関サブクエリ #1：このグループの最も早いexam_dateに結びついたスコアを取得
(SELECT score
FROM Scores s
WHERE s.student_id = t.student_id
AND s.subject = t.subject
AND s.exam_date = (
-- ネストされた相関サブクエリ：このstudent+subjectのMIN(exam_date)を見つける
SELECT MIN(exam_date)
FROM Scores ss
WHERE ss.student_id = t.student_id
AND ss.subject = t.subject
)
) AS first_score,
-- 相関サブクエリ #2：このグループの最も遅いexam_dateに結びついたスコアを取得
(SELECT score
FROM Scores s
WHERE s.student_id = t.student_id
AND s.subject = t.subject
AND s.exam_date = (
-- ネストされた相関サブクエリ：このstudent+subjectのMAX(exam_date)を見つける
SELECT MAX(exam_date)
FROM Scores ss
WHERE ss.student_id = t.student_id
AND ss.subject = t.subject
)
) AS latest_score
FROM Scores t
GROUP BY student_id, subject
)
SELECT *
FROM t
WHERE latest_score > first_score
ORDER BY student_id ASC, subject ASC;
Enter fullscreen mode Exit fullscreen mode
各部分の役割
-
FROM Scores t GROUP BY student_id, subject— 生の試験行を(student_id, subject)コンボごとに1行に集約します。これが評価対象の「候補」のセットです。 -
first_score— 別の相関サブクエリの中にネストされた相関サブクエリ。内部のもの（MIN(exam_date)、WHERE ss.student_id = t.student_id AND ss.subject = t.subjectでスコープ）は、現在のグループtにスコープされた最も早い日付を見つけます。外部のものは、その正確な日付のscoreを、その正確な学生と科目のために取得します。 -
latest_score— 同じ考え方ですが、MIN(exam_date)の代わりにMAX(exam_date)を使用します。 -
「少なくとも2つの異なる日付」という条件 は暗黙的に処理されます：学生/科目に試験日が1つしかない場合、
first_scoreとlatest_scoreは同じ値になります（同じ日付 → 同じスコア）。したがってlatest_score > first_scoreはfalseとなり、その行は外部のWHEREによって自然に除外されます。ロジックの素晴らしい副作用です — 追加のHAVING COUNT(DISTINCT exam_date) >= 2は必要ありません。 -
最終的な
WHERE latest_score > first_score— 実際の「改善したかどうか」のチェック。 -
ORDER BY student_id, subject— 問題で要求される出力順序に一致します。
相関関係を生み出す行
すべてはネストされたサブクエリ内のこの条件ペアにかかっています：
WHERE
ss.student_id = t.student_id
AND ss.subject = t.subject
Enter fullscreen mode Exit fullscreen mode
この2つの条件がすべての魔法です。これらが内部クエリの MIN/MAX 計算を外部クエリ t のこの特定の行に結びつけ、テーブル全体で MIN/MAX を計算するのではなく、この特定の行のグループにスコープします。これらを削除すると、サブクエリは相関ではなくなり、「すべての学生と科目の最も早い日付」になってしまい、私たちが望むものではなくなります。
まとめ
ネストされた MIN(exam_date) / MAX(exam_date) サブクエリは、外部グループの student_id と subject でフィルタリングされるため相関しています。これが「この学生、この科目」にスコープし、テーブル全体ではなくします。
8. 実データを使ったクエリのステップバイステップ実行
例のデータを使って、student 101, subject Math についてトレースしてみましょう：
| exam_date | score |
|---|---|
| 2023-01-15 | 70 |
| 2023-02-15 | 85 |
外部クエリ
----------------------------------
student_id = 101
subject = 'Math'
----------------------------------
Enter fullscreen mode Exit fullscreen mode
│
▼
相関サブクエリ
----------------------------------
Find MIN(exam_date)
WHERE
student_id = 101
AND subject = 'Math'
----------------------------------
Enter fullscreen mode Exit fullscreen mode
│
▼
2023-01-15
│
▼
exam_date = 2023-01-15 の score を見つける
│
▼
first_score = 70
latest_score のブランチも全く同じパスをたどりますが、MIN(exam_date) を MAX(exam_date) に置き換え、2023-02-15 → score = 85 に解決されます。
ステップバイステップでまとめると：
-
GROUP BYがグループ(101, Math)を生成。 first_scoreの内部サブクエリ：(101, Math)のMIN(exam_date)→2023-01-15。first_scoreの外部サブクエリ：exam_date = 2023-01-15で student=101, subject=Math の score →70。latest_scoreの内部サブクエリ：(101, Math)のMAX(exam_date)→2023-02-15。latest_scoreの外部サブクエリ：exam_date = 2023-02-15の score →85。- この時点での行：
(101, Math, 70, 85)。 - 最終フィルタ：
85 > 70か？ はい → 行は残る。
次に student 103, subject Math （試験は1回のみ）をトレース：
| exam_date | score |
|---|---|
| 2023-01-15 | 90 |
- グループ
(103, Math)。 -
MIN(exam_date)→2023-01-15→first_score = 90。 -
MAX(exam_date)→2023-01-15（同じ日付、行は1つだけ） →latest_score = 90。 - 最終フィルタ：
90 > 90か？ いいえ → 行は削除。これが「少なくとも2回の試験日」のルールが無料で強制される仕組みです。
そして student 101, subject Physics （スコアが65から60に低下）：
-
first_score = 65、latest_score = 60。 60 > 65か？ いいえ → 削除。
これが手作業でトレースした完全なアルゴリズムです。
まとめ
各グループの first_score と latest_score は、同じ2ステップの質問を「尋ねる」ことで独立して解決されます：境界となる日付を見つけ、その日付のスコアを見つける。
9. パフォーマンスの議論：サブクエリとウィンドウ関数の比較
MySQL 8.0+ ではウィンドウ関数が利用でき、「グループごとの最初と最後の値」をはるかに効率的かつ読みやすく表現できます：
ウィンドウ関数版
WITH ranked AS (
SELECT
student_id,
subject,
score,
exam_date,
FIRST_VALUE(score) OVER (
PARTITION BY student_id, subject ORDER BY exam_date ASC
) AS first_score,
FIRST_VALUE(score) OVER (
PARTITION BY student_id, subject ORDER BY exam_date DESC
) AS latest_score,
COUNT(*) OVER (PARTITION BY student_id, subject) AS exam_count
FROM Scores
)
SELECT DISTINCT student_id, subject, first_score, latest_score
FROM ranked
WHERE exam_count >= 2 AND latest_score > first_score
ORDER BY student_id, subject;
Enter fullscreen mode Exit fullscreen mode
💡 重要な考え方
ウィンドウ関数は通常、エンジンがデータを1回ソート/パーティション化し、その単一のパスですべてのパーティションの値を計算する必要があります。一方、相関サブクエリ版は、各グループごとに
MIN/MAX/ルックアップ サブクエリを再実行します。最悪の場合（または適切なインデックスがない場合）、これらの内部ルックアップは、単一の統合パスではなく、各グループごとに該当する行を再スキャンする方向に劣化する可能性があります。
ただし、元の解答についての公平な注意点： GROUP BY のため、相関サブクエリは生の行ごとではなく、各一意な (student_id, subject) ペアごとに1回だけ実行されます。(student_id, subject, exam_date) に適切な複合インデックスがあれば、MySQLは各 MIN/MAX ルックアップと score のポイントルックアップをほぼ瞬時に満たすことができるため、実際には現実的なデータセットサイズでこのクエリは非常にうまく動作します。人々が時々相関サブクエリが常に O(n²) であると想定する最悪のシナリオではありません。ウィンドウ関数版は依然としてこれを表現するより「モダンなSQL」の方法であり、より予測可能にスケールしますが、インデックスが適切に配置されていれば、違いは人々が期待するよりも小さいことが多いです。
経験則：
- 「各行/グループに対して、関連するスコープについてターゲットを絞った質問をする」必要がある場合に相関サブクエリに手を伸ばしましょう。そのような場合、非常に読みやすいです。
- 全体のパーティション化されたデータセットにわたってこの種の first/last/rank/running-total ロジックを計算する場合にウィンドウ関数に手を伸ばしましょう。通常はスケールが良く、グループごとの再実行を避けられます。
- どちらが「速い」と仮定する前に、実際のデータで
EXPLAINを確認してください。データサイズ、インデックス、MySQLのバージョンは、教科書の複雑さの話よりも重要です。
まとめ
相関サブクエリは、読みやすさとターゲットを絞ったグループごとのルックアップで優れています。ウィンドウ関数は、同じ計算を何度も再実行しないため、スケールで優れています。
10. 結論とさらなる練習
この記事全体から得られる核心的なアイデアは、1文に収まるほどシンプルです：
相関サブクエリとは、外部行から1つの値を一度に渡され、その値にスコープされた質問に答えるミニクエリにすぎません。
これが腑に落ちれば、ほとんどの「難しい」相関サブクエリの問題は難しくなくなり、「各行/グループごとに尋ねる必要があるミニクエスチョンは何か、そして外部クエリから必要な値は何か？」という単なる問題になります。
このスキルをさらに鍛えたい場合、同じスキルに直接依存する問題がいくつかあります：
- Second Highest Salary（古典的な相関サブクエリのウォームアップ）
- Department Top Three Salaries
- Rank Scores
- Consecutive Numbers
- Employees Earning More Than Their Managers
ここで私がしたように、各問題を2回解いてみてください：1回は相関サブクエリ（またはウィンドウ関数）で、もう1回は紙の上でループのように手でトレースします。ループのトレースが実際に直感を構築するものです。SQLの構文は、その直感のエンコーディングにすぎません。
最後の考え
相関サブクエリはしばしばSQLの概念の中で最もトリッキーなものの1つと考えられます。構文が難しいからではなく、どのクエリが実行され、どの行が参照されているかを追跡しにくいからです。
内部クエリが単に外部クエリの現在の行から値を「借りている」ことを理解すれば、概念ははるかに直感的になります。
この記事がそのメンタルモデルを少しでも明確にする助けになれば幸いです。
Happy learning! 🚀
0 Comments
Log in to join the conversation.No comments yet. Be the first to share your thoughts.