🔄 Step 4: 相関サブクエリ

外側のクエリと連動するサブクエリをマスターしよう!

📋 このステップで学ぶこと

相関サブクエリとは何か、通常のサブクエリとの違い
相関サブクエリの実行の仕組み
EXISTS、NOT EXISTSの活用方法
パフォーマンスへの影響と注意点
JOINとの使い分け
実践的なパターン（ランキング、累積計算など）

🎯 1. 相関サブクエリとは?

1-1. これまでのサブクエリの復習

Step 2とStep 3で学んだサブクエリは、独立して実行されるものでした。サブクエリ単体で実行しても、正常に結果が返ってきます。

※横にスクロールできます

— 通常のサブクエリ（独立して実行できる）
SELECT product_name, price
FROM products
WHERE price > (
    SELECT AVG(price) FROM products
    — このサブクエリは単体で実行できる
    — 結果: 例えば 1500 という1つの値
);

このサブクエリ SELECT AVG(price) FROM products は、外側のクエリとは関係なく、単独で実行できます。1回だけ実行され、その結果（例: 1500）がメインクエリに渡されます。

1-2. 相関サブクエリとは

相関サブクエリは、これとは違い、外側のクエリの各行を参照するサブクエリです。外側のクエリと「相関（関連）」しているので、この名前がついています。

相関サブクエリは単独では実行できません。なぜなら、外側のクエリの値を参照しているからです。

※横にスクロールできます

— 相関サブクエリ（外側のクエリを参照している）
SELECT 
    p1.product_name,
    p1.price,
    p1.category
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p1.category  — ★ここが重要!
    — p1.category は外側のクエリの値を参照している
);

📌 ポイント：外側の値を参照している

WHERE p2.category = p1.category の部分で、外側のクエリ（p1）の category 列を参照しています。これが「相関」の意味です。サブクエリの結果は、外側のクエリの各行によって変わります。

1-3. 相関サブクエリのイメージ

相関サブクエリは、日常生活で例えると「各クラスの平均点より高い生徒を探す」ようなものです。

Aクラスの生徒を見るとき → Aクラスの平均点と比較
Bクラスの生徒を見るとき → Bクラスの平均点と比較
Cクラスの生徒を見るとき → Cクラスの平均点と比較

クラスごとに「比較する基準（平均点）」が変わりますよね。相関サブクエリも同じで、外側のクエリの各行ごとに、サブクエリが計算する値が変わります。

💡 通常のサブクエリと相関サブクエリの違い

項目	通常のサブクエリ	相関サブクエリ
実行回数	1回だけ	外側の各行ごとに実行
外側の参照	参照しない	参照する
単独実行	可能	不可能
結果	固定値（全行で同じ）	行によって変わる

1-4. 相関サブクエリの実行の仕組み

相関サブクエリがどのように実行されるか、具体的に見てみましょう。

例：各カテゴリの平均価格より高い商品を取得

※横にスクロールできます

SELECT 
    p1.product_name,
    p1.price,
    p1.category
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p1.category
);

productsテーブルのデータ例:

product_id	product_name	category	price
1	テレビA	家電	50,000
2	テレビB	家電	30,000
3	冷蔵庫	家電	80,000
4	コーヒー豆A	食品	1,500
5	コーヒー豆B	食品	800

家電の平均: (50,000 + 30,000 + 80,000) / 3 = 53,333円
食品の平均: (1,500 + 800) / 2 = 1,150円

実行の流れ:

1行目（テレビA）を処理:
- p1.category = ‘家電’
- サブクエリ: 家電カテゴリの平均価格 = 53,333円
- 50,000 > 53,333? → FALSE（含まない）
2行目（テレビB）を処理:
- p1.category = ‘家電’
- サブクエリ: 家電カテゴリの平均価格 = 53,333円
- 30,000 > 53,333? → FALSE（含まない）
3行目（冷蔵庫）を処理:
- p1.category = ‘家電’
- サブクエリ: 家電カテゴリの平均価格 = 53,333円
- 80,000 > 53,333? → TRUE（含める!）
4行目（コーヒー豆A）を処理:
- p1.category = ‘食品’
- サブクエリ: 食品カテゴリの平均価格 = 1,150円
- 1,500 > 1,150? → TRUE（含める!）
5行目（コーヒー豆B）を処理:
- p1.category = ‘食品’
- サブクエリ: 食品カテゴリの平均価格 = 1,150円
- 800 > 1,150? → FALSE（含まない）

実行結果:

product_name	price	category
冷蔵庫	80,000	家電
コーヒー豆A	1,500	食品

📌 重要なポイント

サブクエリは5回実行されました（外側のクエリの行数分）。そして、カテゴリによって比較する平均価格が異なります。これが相関サブクエリの特徴です。

🔍 2. 相関サブクエリの基本パターン

2-1. パターン1：グループ内での比較（平均との比較）

最も一般的なパターンは、「各グループの平均より高い/低いものを探す」です。

やりたいこと：各カテゴリの平均価格より高い商品を取得する

※横にスクロールできます

SELECT 
    product_name,
    category,
    price
FROM products p1
WHERE price > (
    SELECT AVG(price)
    FROM products p2
    WHERE p2.category = p1.category
);

クエリの解説:

FROM products p1 → 外側のクエリ。テーブルに「p1」という別名をつける
FROM products p2 → サブクエリ内。同じテーブルに「p2」という別名をつける
WHERE p2.category = p1.category → サブクエリで「p1と同じカテゴリ」に絞り込む
AVG(price) → そのカテゴリの平均価格を計算

なぜ別名（p1, p2）が必要か:

同じテーブル（products）を外側とサブクエリの両方で使っているため、区別するために別名が必要です。別名がないと、どちらのproductsを参照しているか分かりません。

2-2. パターン2：顧客別の比較

やりたいこと：各顧客の平均注文額より高い注文を取得する

「田中さんの平均より高い注文」「鈴木さんの平均より高い注文」のように、顧客ごとに基準が変わります。

※横にスクロールできます

SELECT 
    o1.order_id,
    o1.customer_id,
    o1.total_amount
FROM orders o1
WHERE o1.total_amount > (
    SELECT AVG(o2.total_amount)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

クエリの解説:

FROM orders o1 → 外側のクエリ（各注文を1行ずつ見ていく）
WHERE o2.customer_id = o1.customer_id → 「同じ顧客の注文」に絞り込む
AVG(o2.total_amount) → その顧客の平均注文額を計算

例:

顧客Aの平均注文額が5,000円、顧客Bの平均が10,000円の場合、顧客Aの注文は5,000円と比較され、顧客Bの注文は10,000円と比較されます。

2-3. パターン3：日付による比較（時系列）

やりたいこと：各顧客について、過去最高額より高い注文を取得する

「前回までの最高額を超えた注文」を探すパターンです。時系列データの分析でよく使います。

※横にスクロールできます

SELECT 
    o1.order_id,
    o1.customer_id,
    o1.order_date,
    o1.total_amount
FROM orders o1
WHERE o1.total_amount > (
    SELECT COALESCE(MAX(o2.total_amount), 0)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
      AND o2.order_date < o1.order_date
);

クエリの解説:

AND o2.order_date < o1.order_date → 「今回の注文日より前の注文」に絞り込む
MAX(o2.total_amount) → 過去の注文の中で最高額を取得
COALESCE(..., 0) → 過去の注文がない場合（初回注文）はNULLになるので、0に置き換える

この条件が意味すること:

「過去最高額を更新した注文」を抽出できます。初回注文は必ず含まれます（過去がないので0と比較）。

活用シーン:

顧客の購買行動の変化を分析
「自己ベスト更新」した注文を特定
優良顧客の発見（購入額が増加傾向の顧客）

✅ 3. EXISTS演算子と相関サブクエリ

3-1. EXISTSとは

EXISTSは、サブクエリの結果が「存在するかどうか」をチェックする演算子です。Step 2でも少し触れましたが、EXISTSは相関サブクエリと組み合わせて使うことが非常に多いです。

EXISTSの特徴は、サブクエリが「何を返すか」ではなく、「結果があるかないか」だけを見ることです。

💡 EXISTSの動作イメージ

日常生活で例えると、「この人は注文したことがありますか？」という質問に対して：

EXISTS = 「はい/いいえ」だけを答える（内容は関係ない）
IN = 「この人の注文ID一覧を調べて、その中にあるか」を答える

EXISTSは「存在する」ことが分かった時点で処理を終了できるので、効率的です。

3-2. EXISTSの基本的な使い方

やりたいこと：注文履歴がある顧客を取得する

まず、EXISTSを使わない方法（INを使う方法）を見てみましょう。

※横にスクロールできます

-- INを使った方法
SELECT customer_id, customer_name, email
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
);

次に、EXISTSを使った方法を見てみましょう。

※横にスクロールできます

-- EXISTSを使った方法（相関サブクエリ）
SELECT 
    customer_id,
    customer_name,
    email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

クエリの解説:

FROM customers c → customersテーブルに「c」という別名をつける
WHERE EXISTS (...) → サブクエリの結果が存在すればTRUE
SELECT 1 → 「何か1つの値」を返す（EXISTSは値の内容を見ないので、1でも*でも何でもOK）
WHERE o.customer_id = c.customer_id → 相関部分! 外側の顧客IDを参照

📌 EXISTSの実行イメージ

customersテーブルの1行目（顧客ID: 1001）を取得
サブクエリ: 「ordersテーブルにcustomer_id = 1001の注文はあるか?」
1件でも見つかれば → TRUE（この顧客を結果に含める）
見つからなければ → FALSE（この顧客を結果から除外）
customersテーブルの2行目を取得して同様に繰り返す...

3-3. SELECT 1 の意味

EXISTSのサブクエリでは、SELECT 1 や SELECT * など、何を選択しても結果は同じです。

※横にスクロールできます

-- これらはすべて同じ結果になる
WHERE EXISTS (SELECT 1 FROM orders WHERE ...)
WHERE EXISTS (SELECT * FROM orders WHERE ...)
WHERE EXISTS (SELECT order_id FROM orders WHERE ...)
WHERE EXISTS (SELECT 'anything' FROM orders WHERE ...)

EXISTSは「行が存在するか」だけをチェックするので、SELECTで何を選んでも関係ありません。慣習的に SELECT 1 を使うことが多いです。

3-4. NOT EXISTS（存在しない）

NOT EXISTS を使うと、「関連データが存在しない」レコードを取得できます。

やりたいこと：一度も注文していない顧客を取得する

※横にスクロールできます

SELECT 
    customer_id,
    customer_name,
    email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

クエリの解説:

NOT EXISTS → サブクエリの結果が「存在しない」場合にTRUE
この顧客IDの注文が1件もない場合に、結果に含まれる

活用シーン:

初回購入キャンペーンの対象者リストを作成
休眠顧客（長期間注文がない顧客）の抽出
データの整合性チェック（孤立したデータの発見）

3-5. 期間を指定したEXISTS

やりたいこと：2024年に一度も注文していない顧客を取得する

※横にスクロールできます

SELECT 
    customer_name,
    email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= '2024-01-01'
      AND o.order_date < '2025-01-01'
);

クエリの解説:

サブクエリのWHERE句に追加の条件（日付範囲）を入れている
「2024年の注文が存在しない」顧客を抽出
この顧客が2023年以前に注文していても、2024年に注文がなければ結果に含まれる

3-6. 複雑な条件を含むEXISTS

やりたいこと：特定の商品（商品ID 101）を購入したことがある顧客を取得する

EXISTSのサブクエリ内でJOINを使うこともできます。

※横にスクロールできます

SELECT 
    customer_name,
    email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    INNER JOIN order_details od ON o.order_id = od.order_id
    WHERE o.customer_id = c.customer_id
      AND od.product_id = 101
);

クエリの解説:

サブクエリ内で orders と order_details をJOIN
WHERE o.customer_id = c.customer_id → 相関部分（外側の顧客を参照）
AND od.product_id = 101 → 商品ID 101が含まれる注文
「この顧客が商品101を購入した注文が1件でもあるか」をチェック

3-7. HAVINGを含むEXISTS

やりたいこと：3回以上注文している顧客（リピーター）を取得する

※横にスクロールできます

SELECT 
    customer_name,
    email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    GROUP BY o.customer_id
    HAVING COUNT(*) >= 3
);

クエリの解説:

サブクエリ内で GROUP BY と HAVING を使用
GROUP BY o.customer_id → この顧客の注文をグループ化
HAVING COUNT(*) >= 3 → 注文が3件以上ある場合にのみ結果を返す
EXISTS は「その条件を満たす結果があるか」をチェック

3-8. EXISTSのメリット

💡 EXISTSを使うメリット

効率的：1件見つかった時点で処理を終了できる
NULLに強い：INとは違い、NULLが含まれていても正しく動作する
読みやすい：「〜が存在する場合」という意図が明確
複雑な条件：JOINやHAVINGを含む複雑な条件も書ける

🔀 4. INとEXISTSの違い

4-1. 基本的な違い

INとEXISTSは、同じ結果を得られる場合が多いですが、動作の仕組みが異なります。

📊 IN vs EXISTS 比較

項目	IN	EXISTS
動作	値のリストを作成し、比較	存在チェック（TRUE/FALSE）
サブクエリの種類	通常のサブクエリ	相関サブクエリ
NULLの扱い	問題が起きる場合がある	問題なし
効率	サブクエリ結果が少ない時に有利	サブクエリ結果が多い時に有利

4-2. 同じ結果を得る2つの書き方

例：注文履歴がある顧客を取得

※横にスクロールできます

-- INを使った書き方
SELECT customer_name, email
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
);

-- EXISTSを使った書き方
SELECT customer_name, email
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.customer_id
);

どちらも同じ結果になりますが、動作の仕組みが異なります。

4-3. NULLの問題（INの注意点）

INを使う場合、サブクエリの結果にNULLが含まれていると、予期せぬ結果になることがあります。

※横にスクロールできます

-- NOT INでNULLが含まれる場合の問題
SELECT customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders
    -- もしcustomer_idにNULLが含まれていると、
    -- 結果が空になってしまう可能性がある
);

EXISTSではこの問題は起きません。NULLに関わらず、「存在するかどうか」だけをチェックするためです。

4-4. 使い分けの指針

📌 どちらを使うべき?

状況	推奨	理由
存在チェックのみ	EXISTS	意図が明確、効率的
値リストが小さい	IN	シンプルで読みやすい
値リストが大きい	EXISTS	1件見つかれば終了できる
NOT INを使いたい	NOT EXISTS	NULLの問題を回避
複雑な条件	EXISTS	JOINやHAVINGも使える

💡 実務でのアドバイス

迷ったらEXISTSを使うことをおすすめします。NULLの問題がなく、パフォーマンスも一般的に良好です。ただし、読みやすさも重要なので、チームのコーディング規約に従うことも大切です。

⚡ 5. パフォーマンスへの影響

5-1. 相関サブクエリは遅くなりやすい?

相関サブクエリは、外側のクエリの各行ごとに実行されるため、データ量が多いと遅くなる可能性があります。

⚠️ パフォーマンスの注意点

外側のクエリが10,000行あれば、サブクエリも最大10,000回実行されます。各実行でテーブルスキャンが発生すると、非常に遅くなります。

5-2. パフォーマンスが悪い例

例：各商品の注文回数を表示

※横にスクロールできます

-- 遅い可能性がある書き方
SELECT 
    product_name,
    price,
    (
        SELECT COUNT(*)
        FROM order_details od
        WHERE od.product_id = p.product_id
    ) as order_count
FROM products p;

なぜ遅くなるのか:

productsテーブルに1,000商品あれば、サブクエリが1,000回実行される
各実行でorder_detailsテーブルをスキャンする
order_detailsに100万件あれば、合計で10億回の比較が発生する可能性

5-3. JOINを使った改善版

同じ結果をJOINで書き換えると、通常は高速になります。

※横にスクロールできます

-- JOINを使った高速版
SELECT 
    p.product_name,
    p.price,
    COUNT(od.detail_id) as order_count
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name, p.price;

なぜ速くなるのか:

JOINは1回の実行で完了する
データベースの最適化機能が効きやすい
インデックスを効率的に使える

5-4. EXISTSは比較的高速

EXISTSは、1件見つかった時点で処理を終了できるため、相関サブクエリの中では効率的です。

※横にスクロールできます

-- EXISTSは1件見つかれば即終了
SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    -- 1件見つかった時点でTRUEを返して終了
);

💡 EXISTSが効率的な理由

EXISTSは「存在するかどうか」だけをチェックするので、条件を満たす最初の1件が見つかった時点で処理を終了できます。INのように「すべてのリストを作成してから比較」する必要がありません。

5-5. パフォーマンス改善のポイント

📌 パフォーマンスを改善する方法

インデックスを設定する
- 相関部分で使う列（例: customer_id）にインデックスを作成
- WHERE句で使う列にもインデックスを検討
サブクエリ内で絞り込む
- WHERE句で不要なデータを早めに除外
- 日付範囲やステータスなどで絞り込む
EXISTSを活用する
- 存在チェックならINよりEXISTSを使う
JOINへの書き換えを検討
- 可能ならJOINで書き換えると高速なことが多い
実行計画を確認する
- EXPLAINで実際の実行計画を確認（後のステップで学習）

🔀 6. 相関サブクエリ vs JOIN

6-1. 同じ結果を得る2つの方法

相関サブクエリとJOINは、多くの場合で同じ結果を得られます。どちらを使うべきかは、状況によって異なります。

例：注文履歴がある顧客を取得

※横にスクロールできます

-- 方法1: EXISTS（相関サブクエリ）
SELECT customer_name, email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

※横にスクロールできます

-- 方法2: JOIN
SELECT DISTINCT c.customer_name, c.email
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

違いに注目:

EXISTS版: 顧客テーブルから取得、注文の存在だけをチェック
JOIN版: DISTINCTが必要（1人の顧客が複数注文していると重複するため）

6-2. 使い分けの基準

📊 相関サブクエリ vs JOIN の使い分け

状況	推奨	理由
存在チェックのみ	EXISTS	意図が明確、DISTINCTが不要
結合先の列も必要	JOIN	注文日や金額など、他の列も取得できる
集計が必要	JOIN	GROUP BYと相性が良い
グループ内比較	相関サブクエリ	各カテゴリの平均との比較など
複雑な条件	EXISTS	条件を明確に表現できる

6-3. JOINが適している例

例：顧客名と注文情報を両方取得

※横にスクロールできます

-- JOINが適している（結合先の列も必要）
SELECT 
    c.customer_name,
    o.order_date,
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC;

なぜJOINが適しているか:

顧客名だけでなく、注文日や金額も取得したい場合は、JOINを使う方が自然です。相関サブクエリで同じことをするのは複雑になります。

6-4. 相関サブクエリが適している例

例：各カテゴリ内での順位付け

※横にスクロールできます

-- 相関サブクエリが適している
SELECT 
    product_name,
    category,
    price,
    (
        SELECT COUNT(*) + 1
        FROM products p2
        WHERE p2.category = p1.category
          AND p2.price > p1.price
    ) as rank_in_category
FROM products p1
ORDER BY category, price DESC;

なぜ相関サブクエリが適しているか:

各商品について、同じカテゴリ内での順位を計算したい
JOINで同じことをするのは複雑
ウィンドウ関数（後のステップで学習）でも可能だが、相関サブクエリも有効

🎨 7. 実践的な相関サブクエリのパターン

7-1. パターン1：最新データの取得

やりたいこと：各顧客の最新注文を取得する

これは実務でよく使うパターンです。「各グループで最新の1件だけ」を取得します。

※横にスクロールできます

SELECT 
    o1.customer_id,
    o1.order_id,
    o1.order_date,
    o1.total_amount
FROM orders o1
WHERE o1.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

クエリの解説:

外側: ordersテーブルの各行を見ていく
サブクエリ: 「同じ顧客の注文の中で、最新の注文日」を取得
条件: 「この注文の日付 = その顧客の最新注文日」なら結果に含める

注意点:

同じ日に複数の注文がある場合、両方が結果に含まれます。1件だけ欲しい場合は追加の条件（order_idで絞るなど）が必要です。

活用シーン:

各顧客の最新の注文情報を表示
各商品の最新レビューを取得
各従業員の最新の評価を取得

7-2. パターン2：累積計算

やりたいこと：各注文時点での、その顧客の累積購入額を計算する

※横にスクロールできます

SELECT 
    o1.order_id,
    o1.customer_id,
    o1.order_date,
    o1.total_amount,
    (
        SELECT SUM(o2.total_amount)
        FROM orders o2
        WHERE o2.customer_id = o1.customer_id
          AND o2.order_date <= o1.order_date
    ) as cumulative_amount
FROM orders o1
ORDER BY o1.customer_id, o1.order_date;

クエリの解説:

AND o2.order_date <= o1.order_date → 「この注文日まで」の注文に絞る
SUM(o2.total_amount) → それらの注文の合計額
各注文行に対して、「その時点での累計」が計算される

実行結果のイメージ:

order_id	customer_id	order_date	total_amount	cumulative_amount
101	1001	2024-01-15	5,000	5,000
105	1001	2024-02-20	8,000	13,000
112	1001	2024-03-10	3,000	16,000

7-3. パターン3：ランキング（トップNの抽出）

やりたいこと：各カテゴリで価格トップ3の商品を取得する

※横にスクロールできます

SELECT 
    product_name,
    category,
    price
FROM products p1
WHERE (
    SELECT COUNT(*)
    FROM products p2
    WHERE p2.category = p1.category
      AND p2.price > p1.price
) < 3
ORDER BY category, price DESC;

クエリの解説:

サブクエリ: 「同じカテゴリで、自分より高い価格の商品は何個あるか」をカウント
< 3 → 自分より高い商品が2個以下 = トップ3に入る
1位: 自分より高い商品が0個
2位: 自分より高い商品が1個
3位: 自分より高い商品が2個

📌 同じ価格の商品がある場合

同じ価格の商品がある場合、両方とも同じ順位になります。例えば、2位が2つあると、3位は存在しません（次は4位になる）。これを「デンスランク」と呼びます。

7-4. パターン4：前回との比較

やりたいこと：各注文について、前回注文からの経過日数を計算する

※横にスクロールできます

SELECT 
    o1.order_id,
    o1.customer_id,
    o1.order_date,
    (
        SELECT MAX(o2.order_date)
        FROM orders o2
        WHERE o2.customer_id = o1.customer_id
          AND o2.order_date < o1.order_date
    ) as previous_order_date
FROM orders o1
ORDER BY o1.customer_id, o1.order_date;

クエリの解説:

AND o2.order_date < o1.order_date → 「今回より前の注文」に絞る
MAX(o2.order_date) → その中で最も新しい日付 = 前回の注文日
初回注文の場合、前回がないのでNULLになる

活用シーン:

顧客の購買頻度を分析
休眠顧客の特定（前回注文から〇日以上経過）
リピート率の計算

💡 ウィンドウ関数との関係

累積計算、ランキング、前回との比較は、ウィンドウ関数（LAG、RANK、SUM OVER など）を使うとより簡潔に書けることが多いです。ウィンドウ関数はStep 6以降で詳しく学びます。相関サブクエリで概念を理解しておくと、ウィンドウ関数の理解もスムーズになります!

📝 練習問題

相関サブクエリを使って、実際に手を動かして確認しましょう!

問題 1基本

カテゴリ平均より高い商品

各商品が属するカテゴリの平均価格より高い商品を取得してください。

※横にスクロールできます

SELECT 
    product_name,
    category,
    price
FROM products p1
WHERE price > (
    SELECT AVG(price)
    FROM products p2
    WHERE p2.category = p1.category
);

解説:

FROM products p1 → 外側のクエリ。productsテーブルに「p1」という別名をつける
FROM products p2 → サブクエリ内。同じテーブルに「p2」という別名をつける
WHERE p2.category = p1.category → 相関部分。p1（外側）と同じカテゴリの商品だけを対象にする
AVG(price) → そのカテゴリの平均価格を計算
各商品について「自分のカテゴリの平均」と比較するので、カテゴリによって比較する値が変わる

問題 2基本

EXISTSで注文履歴チェック

EXISTSを使って、注文履歴がある顧客を取得してください。

※横にスクロールできます

SELECT 
    customer_id,
    customer_name,
    email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

解説:

FROM customers c → customersテーブルに「c」という別名をつける
WHERE EXISTS (...) → サブクエリの結果が1件以上あればTRUE
SELECT 1 → EXISTSは存在チェックだけなので、何を選んでもOK
WHERE o.customer_id = c.customer_id → 相関部分。外側の顧客IDと一致する注文を探す
EXISTSは「存在する」ことが分かった時点で処理を終了するので効率的

問題 3応用

NOT EXISTSで未注文顧客

2024年に一度も注文していない顧客を取得してください。

※横にスクロールできます

SELECT 
    customer_name,
    email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= '2024-01-01'
      AND o.order_date < '2025-01-01'
);

解説:

NOT EXISTS → サブクエリの結果が「存在しない」場合にTRUE
AND o.order_date >= '2024-01-01' → 2024年以降
AND o.order_date < '2025-01-01' → 2025年より前（= 2024年中）
「2024年の注文が存在しない顧客」を抽出
2023年以前に注文していても、2024年に注文がなければ結果に含まれる

活用シーン: 休眠顧客へのキャンペーン対象者リストの作成

問題 4応用

自分の平均より高い注文

各顧客の平均注文額より高い注文を取得してください。

※横にスクロールできます

SELECT 
    o1.order_id,
    o1.customer_id,
    o1.total_amount
FROM orders o1
WHERE o1.total_amount > (
    SELECT AVG(o2.total_amount)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

解説:

ordersテーブルの各注文を見ていく
サブクエリで「同じ顧客の注文」の平均額を計算
「この注文の金額 > その顧客の平均」なら結果に含める
顧客Aの平均が5,000円、顧客Bの平均が10,000円の場合、比較する基準が顧客によって異なる

活用シーン: 各顧客の「いつもより高額な注文」を特定し、購買行動を分析

問題 5応用

特定商品を購入した顧客

商品ID 101を購入したことがある顧客を、EXISTSを使って取得してください。

※横にスクロールできます

SELECT 
    customer_name,
    email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    INNER JOIN order_details od ON o.order_id = od.order_id
    WHERE o.customer_id = c.customer_id
      AND od.product_id = 101
);

解説:

EXISTSのサブクエリ内で orders と order_details をJOIN
WHERE o.customer_id = c.customer_id → 相関部分（外側の顧客を参照）
AND od.product_id = 101 → 商品ID 101が含まれる注文
「この顧客が商品101を購入した注文が1件でもあるか」をチェック

活用シーン: 特定商品の購入者に関連商品をおすすめするためのリスト作成

問題 6チャレンジ

カテゴリ内トップ3

各カテゴリで価格が高い順にトップ3に入る商品を取得してください。

※横にスクロールできます

SELECT 
    product_name,
    category,
    price
FROM products p1
WHERE (
    SELECT COUNT(*)
    FROM products p2
    WHERE p2.category = p1.category
      AND p2.price > p1.price
) < 3
ORDER BY category, price DESC;

解説:

サブクエリ: 「同じカテゴリで、自分より高い価格の商品は何個あるか」をカウント
< 3 → 自分より高い商品が2個以下 = トップ3に入る
ランキングの考え方:
- 1位 → 自分より高い商品が0個
- 2位 → 自分より高い商品が1個
- 3位 → 自分より高い商品が2個
同じ価格の商品がある場合は、両方とも同じ順位になる

問題 7チャレンジ

累積購入額

各注文時点での、その顧客の累積購入額を計算してください。

※横にスクロールできます

SELECT 
    o1.order_id,
    o1.customer_id,
    o1.order_date,
    o1.total_amount,
    (
        SELECT SUM(o2.total_amount)
        FROM orders o2
        WHERE o2.customer_id = o1.customer_id
          AND o2.order_date <= o1.order_date
    ) as cumulative_amount
FROM orders o1
ORDER BY o1.customer_id, o1.order_date;

解説:

AND o2.order_date <= o1.order_date → 「この注文日まで」の注文に絞る
SUM(o2.total_amount) → それらの注文の合計額
各注文行に対して、「その時点での累計」が計算される
例: 1/1に5000円、2/1に3000円、3/1に4000円の場合
- 1/1時点: 5,000円
- 2/1時点: 8,000円（5,000 + 3,000）
- 3/1時点: 12,000円（5,000 + 3,000 + 4,000）

活用シーン: 顧客のLTV（生涯価値）の推移を分析

問題 9チャレンジ

リピーター判定

3回以上注文している顧客を、EXISTSを使って取得してください。

※横にスクロールできます

SELECT 
    customer_name,
    email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    GROUP BY o.customer_id
    HAVING COUNT(*) >= 3
);

解説:

サブクエリ内で GROUP BY と HAVING を使用
GROUP BY o.customer_id → この顧客の注文をグループ化
HAVING COUNT(*) >= 3 → 注文が3件以上ある場合にのみ結果を返す
EXISTSは「その条件を満たす結果があるか」をチェック
注文が3件以上ある場合のみ、EXISTSがTRUEになる

活用シーン: リピーター顧客への特別オファーの対象者抽出

問題 10チャレンジ

前回注文日の取得

各注文について、前回注文日を取得してください。

※横にスクロールできます

SELECT 
    o1.order_id,
    o1.customer_id,
    o1.order_date,
    (
        SELECT MAX(o2.order_date)
        FROM orders o2
        WHERE o2.customer_id = o1.customer_id
          AND o2.order_date < o1.order_date
    ) as previous_order_date
FROM orders o1
ORDER BY o1.customer_id, o1.order_date;

解説:

AND o2.order_date < o1.order_date → 「今回より前の注文」に絞る
MAX(o2.order_date) → その中で最も新しい日付 = 前回の注文日
初回注文の場合、前回がないのでNULLになる

発展: 経過日数を計算したい場合は、日付の差分を計算します

※横にスクロールできます

-- 経過日数も計算する場合（SQLiteの場合）
SELECT 
    o1.order_id,
    o1.customer_id,
    o1.order_date,
    (
        SELECT MAX(o2.order_date)
        FROM orders o2
        WHERE o2.customer_id = o1.customer_id
          AND o2.order_date < o1.order_date
    ) as previous_order_date,
    CAST(
        JULIANDAY(o1.order_date) - JULIANDAY((
            SELECT MAX(o2.order_date)
            FROM orders o2
            WHERE o2.customer_id = o1.customer_id
              AND o2.order_date < o1.order_date
        )) AS INTEGER
    ) as days_since_last_order
FROM orders o1
ORDER BY o1.customer_id, o1.order_date;

問題 11チャレンジ

高額商品購入者

1万円以上の商品を購入したことがある顧客を取得してください。

※横にスクロールできます

SELECT 
    customer_name,
    email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    INNER JOIN order_details od ON o.order_id = od.order_id
    INNER JOIN products p ON od.product_id = p.product_id
    WHERE o.customer_id = c.customer_id
      AND p.price >= 10000
);

解説:

サブクエリ内で3つのテーブルをJOIN: orders → order_details → products
WHERE o.customer_id = c.customer_id → 相関部分
AND p.price >= 10000 → 1万円以上の商品
「この顧客が1万円以上の商品を購入した履歴があるか」をチェック

活用シーン: 高額商品の購入者に新商品案内を送る

問題 12チャレンジ

カテゴリ別順位付け

各商品について、そのカテゴリ内での価格順位を計算してください。

※横にスクロールできます

SELECT 
    product_name,
    category,
    price,
    (
        SELECT COUNT(*) + 1
        FROM products p2
        WHERE p2.category = p1.category
          AND p2.price > p1.price
    ) as price_rank
FROM products p1
ORDER BY category, price DESC;

解説:

SELECT COUNT(*) → 自分より高い価格の商品数をカウント
+ 1 → 自分より高い商品が0個なら1位、1個なら2位...
ランキングの仕組み:
- 自分より高い商品が0個 → 0 + 1 = 1位
- 自分より高い商品が1個 → 1 + 1 = 2位
- 自分より高い商品が2個 → 2 + 1 = 3位
同じ価格の商品がある場合は、同じ順位になる

補足: この方法は「デンスランク」と呼ばれます。ウィンドウ関数の RANK() や DENSE_RANK() でも同様の結果が得られます（Step 6以降で学習）

📝 Step 4 のまとめ

✅ 学んだこと

相関サブクエリは外側のクエリの各行ごとに実行される
外側のクエリの値を参照できる（WHERE p2.category = p1.category など）
EXISTS/NOT EXISTSが相関サブクエリで威力を発揮
パフォーマンスに注意が必要（インデックス、JOINとの比較）
JOINとの使い分けを理解した
カテゴリ内比較、累積計算、ランキングなど実践的なパターンを学んだ

📌 相関サブクエリの基本形

※横にスクロールできます

-- 基本形（集計関数との比較）
SELECT 列名
FROM テーブル1 t1
WHERE 条件 演算子 (
    SELECT 集計関数(列名)
    FROM テーブル2 t2
    WHERE t2.列名 = t1.列名  -- ★相関部分
);

-- EXISTS版（存在チェック）
SELECT 列名
FROM テーブル1 t1
WHERE EXISTS (
    SELECT 1
    FROM テーブル2 t2
    WHERE t2.列名 = t1.列名  -- ★相関部分
      AND その他の条件
);

-- NOT EXISTS版（存在しないことをチェック）
SELECT 列名
FROM テーブル1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM テーブル2 t2
    WHERE t2.列名 = t1.列名
);

💡 実務での使いどころ

グループ内比較: カテゴリ別、顧客別、期間別など
存在チェック: 関連データの有無確認（EXISTS/NOT EXISTS）
累積計算: 時系列での累積値（累計売上など）
ランキング: グループ内順位付け
最新データ: 各グループの最新レコード取得
前回との比較: 前回の値、経過日数の計算

⚠️ 注意点

外側の各行ごとに実行されるため遅くなりやすい
インデックスを適切に設定することが重要
可能ならJOINやウィンドウ関数を検討
実行計画でパフォーマンス確認（後のステップで学習）
NOT INはNULLに弱いので、NOT EXISTSを推奨

❓ よくある質問

Q1: 相関サブクエリは常に遅いですか?

いいえ、適切なインデックスがあれば十分高速です。特にEXISTSは効率的で、1件見つかった時点で処理を終了します。

ただし、データ量が多い場合は、JOINやウィンドウ関数と比較して、どちらが速いか確認することをおすすめします。

Q2: JOINと相関サブクエリ、どちらを使うべきですか?

目的による違いです。

存在チェックだけならEXISTS：意図が明確で、DISTINCTが不要
結合先の列も必要ならJOIN：注文日や金額など、他のテーブルの列も取得できる
集計が必要ならJOIN：GROUP BYと相性が良い
グループ内比較なら相関サブクエリ：「各カテゴリの平均と比較」など

Q3: EXISTSとINの違いは?

EXISTSは存在チェック（TRUE/FALSE）、INは値のリストチェックです。

EXISTS：1件見つかれば即終了するため効率的
EXISTS：NULLにも強い（NOT INはNULLがあると問題が起きる）
IN：値のリストが小さい場合はシンプルで読みやすい

迷ったらEXISTSを使うことをおすすめします。

Q4: 相関サブクエリは何回実行されますか?

外側のクエリの行数分実行されます。外側が10,000行なら、サブクエリも最大10,000回実行されます。

ただし、EXISTSは早期終了する場合があり、データベースの最適化機能によって実際の実行回数が減ることもあります。

Q5: SELECT句でも相関サブクエリは使えますか?

はい、使えます。今回の練習問題でも「累積購入額」や「カテゴリ別順位」でSELECT句に相関サブクエリを使いました。

ただし、SELECT句で使う場合は各行ごとに計算されるため、パフォーマンスには特に注意が必要です。次のステップ（Step 5）で詳しく学びます。

Q6: ウィンドウ関数と相関サブクエリの違いは?

ウィンドウ関数は、集計しながら全ての行を保持できる機能です。

ウィンドウ関数：SUM(amount) OVER (ORDER BY date) のように書ける
ウィンドウ関数：相関サブクエリより効率的で読みやすいことが多い
相関サブクエリ：概念を理解しておくと、ウィンドウ関数の理解もスムーズ

ウィンドウ関数はStep 6以降で詳しく学びます!

🎓 次のステップでは

Step 5: スカラーサブクエリと応用では、SELECT句やFROM句で1つの値を返すサブクエリを学びます。計算列の追加や、動的な値の取得ができるようになります!

📝

学習メモ

SQL応用・パフォーマンス最適化 - Step 4

📋 過去のメモ一覧 ▼

🔄 Step 4: 相関サブクエリ

🎯 1. 相関サブクエリとは?

1-1. これまでのサブクエリの復習

1-2. 相関サブクエリとは

1-3. 相関サブクエリのイメージ

1-4. 相関サブクエリの実行の仕組み

🔍 2. 相関サブクエリの基本パターン

2-1. パターン1：グループ内での比較（平均との比較）

2-2. パターン2：顧客別の比較

2-3. パターン3：日付による比較（時系列）

✅ 3. EXISTS演算子と相関サブクエリ

3-1. EXISTSとは

3-2. EXISTSの基本的な使い方

3-3. SELECT 1 の意味

3-4. NOT EXISTS（存在しない）

3-5. 期間を指定したEXISTS

3-6. 複雑な条件を含むEXISTS

3-7. HAVINGを含むEXISTS

3-8. EXISTSのメリット

🔀 4. INとEXISTSの違い

4-1. 基本的な違い

4-2. 同じ結果を得る2つの書き方

4-3. NULLの問題（INの注意点）

4-4. 使い分けの指針

⚡ 5. パフォーマンスへの影響

5-1. 相関サブクエリは遅くなりやすい?

5-2. パフォーマンスが悪い例

5-3. JOINを使った改善版

5-4. EXISTSは比較的高速

5-5. パフォーマンス改善のポイント

🔀 6. 相関サブクエリ vs JOIN

6-1. 同じ結果を得る2つの方法

6-2. 使い分けの基準

6-3. JOINが適している例

6-4. 相関サブクエリが適している例

🎨 7. 実践的な相関サブクエリのパターン

7-1. パターン1：最新データの取得

7-2. パターン2：累積計算

7-3. パターン3：ランキング（トップNの抽出）

7-4. パターン4：前回との比較

📝 練習問題

カテゴリ平均より高い商品

EXISTSで注文履歴チェック

NOT EXISTSで未注文顧客

自分の平均より高い注文

特定商品を購入した顧客

カテゴリ内トップ3

最新注文の取得

累積購入額

リピーター判定

前回注文日の取得

高額商品購入者

カテゴリ別順位付け

📝 Step 4 のまとめ

❓ よくある質問

Q1: 相関サブクエリは常に遅いですか?

Q2: JOINと相関サブクエリ、どちらを使うべきですか?

Q3: EXISTSとINの違いは?

Q4: 相関サブクエリは何回実行されますか?

Q5: SELECT句でも相関サブクエリは使えますか?

Q6: ウィンドウ関数と相関サブクエリの違いは?

学習メモ