🔍 Step 2: サブクエリの基礎

クエリの中にクエリを入れて、複雑な条件を簡単に表現しよう!

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

サブクエリとは何か、なぜ便利なのか
WHERE句でのサブクエリの使い方
単一行サブクエリと複数行サブクエリの違い
IN演算子、EXISTS演算子との組み合わせ
実践的なサブクエリのパターン

🎯 1. サブクエリとは何か?

1-1. サブクエリの基本概念

サブクエリ（副問い合わせ）とは、SQLクエリの中に別のSQLクエリを入れる技術です。

日常生活で例えると、「今月の平均気温より暑い日を教えて」と聞くようなものです。この質問に答えるには、まず「今月の平均気温」を計算してから、それより暑い日を探しますよね。サブクエリを使えば、この2つのステップを1つのクエリで実現できます。

💡 サブクエリのイメージ

サブクエリは「箱の中に箱を入れる」ようなものです。外側の箱（メインクエリ）の中に、内側の箱（サブクエリ）があり、内側の箱の中身が先に確認されてから、外側の箱が処理されます。

1-2. サブクエリを使わない場合と使う場合の比較

「平均価格より高い商品を探したい」という場合を考えてみましょう。

■ サブクエリを使わない場合（2ステップ必要）

まず、平均価格を調べるクエリを実行します。

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

— ステップ1: まず平均価格を調べる
SELECT AVG(price) FROM products;
— 結果: 1500円だったとします

次に、その結果（1500円）を手動で使って、検索クエリを実行します。

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

— ステップ2: その値を手動で入力して検索
SELECT * FROM products
WHERE price > 1500;

この方法だと、2回クエリを実行する必要があり、平均価格が変わるたびに手動で値を変更しなければなりません。

■ サブクエリを使う場合（1ステップで完了!）

サブクエリを使えば、1つのクエリで同じことができます。

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

— 平均価格より高い商品を一度に取得
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

命令の意味:

SELECT * FROM products → productsテーブルから全ての列を取得
WHERE price > → 価格が〜より大きいという条件
(SELECT AVG(price) FROM products) → これがサブクエリ!productsテーブルの平均価格を計算して返す

実行順序:

まず括弧内のサブクエリ SELECT AVG(price) FROM products が実行される
サブクエリの結果（例: 1500）がメインクエリに渡される
メインクエリが WHERE price > 1500 として実行される

1-3. サブクエリのメリット

✅ サブクエリを使う4つのメリット

1つのクエリで完結できる：複数回のクエリ実行が不要
動的な値が使える：データが変わっても自動的に最新の値で計算される
複雑な条件を簡潔に表現できる：「平均より上」「最大値と同じ」などの条件が書きやすい
コードの可読性が向上：何をしたいかが1つのクエリで分かる

1-4. サブクエリが使える3つの場所

サブクエリは、SQLの中の様々な場所で使うことができます。このコースでは主に3つの場所を学びます。

📍 サブクエリが使える場所

場所	用途	学ぶステップ
WHERE句	条件の中で使う（最も一般的）	このステップ（Step 2）
FROM句	テーブルの代わりに使う	Step 3
SELECT句	列の値として使う	Step 5

📊 2. WHERE句でのサブクエリ

2-1. 基本的な使い方

WHERE句の条件部分でサブクエリを使うのが、最も基本的で一般的なパターンです。「〜より大きい」「〜と等しい」「〜に含まれる」といった条件の「〜」の部分をサブクエリで動的に計算できます。

📝 基本構文

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

SELECT 列名
FROM テーブル名
WHERE 列名 演算子 (サブクエリ);

2-2. 例1：平均価格より高い商品を取得する

最も基本的な例として、平均価格より高い商品を取得してみましょう。

やりたいこと：productsテーブルから、全商品の平均価格より高い価格の商品を探す

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

SELECT 
    product_name,
    price,
    category
FROM products
WHERE price > (SELECT AVG(price) FROM products);

クエリの解説:

SELECT product_name, price, category → 商品名、価格、カテゴリを取得
FROM products → productsテーブルから
WHERE price > → 価格が〜より大きいという条件
(SELECT AVG(price) FROM products) → 全商品の平均価格を計算するサブクエリ

実行結果のイメージ:

product_name	price	category
高級ヘッドホン	25,000	家電
プレミアムコーヒー	3,200	食品
デザイナーズチェア	45,000	家具

※ 平均価格が1,500円の場合、1,500円より高い商品が表示される

2-3. 例2：最高価格の商品を取得する

最も高い価格の商品を取得したい場合、MAX関数とサブクエリを組み合わせます。

やりたいこと：productsテーブルから、最も高い価格の商品を探す

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

SELECT 
    product_name,
    price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

クエリの解説:

WHERE price = → 価格が〜と等しいという条件（> ではなく =）
(SELECT MAX(price) FROM products) → 最高価格を計算するサブクエリ

なぜ「=」を使うのか:

最高価格「より高い」商品は存在しないので、最高価格「と等しい」商品を探します。

2-4. 例3：特定カテゴリの平均価格より高い商品

「家電」カテゴリの平均価格を基準にして、それより高い全カテゴリの商品を探してみましょう。

やりたいこと：「家電」カテゴリの平均価格より高い商品を、全カテゴリから探す

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

SELECT 
    product_name,
    price,
    category
FROM products
WHERE price > (
    SELECT AVG(price) 
    FROM products 
    WHERE category = ‘家電’
);

クエリの解説:

サブクエリ内の WHERE category = '家電' → 家電カテゴリだけを対象に平均を計算
メインクエリには WHERE category = '家電' がない → 全カテゴリが検索対象

ポイント:

サブクエリとメインクエリは独立しています。サブクエリで「家電」に絞っても、メインクエリは全商品を検索します。

2-5. 例4：特定の顧客より多く購入している顧客を探す

「顧客ID 1001さんの購入金額より多く買っている顧客は誰か？」を調べてみましょう。

やりたいこと：顧客ID 1001の総購入額より多く購入している顧客を探す

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

SELECT 
    customer_id,
    customer_name,
    total_amount
FROM customers
WHERE total_amount > (
    SELECT total_amount 
    FROM customers 
    WHERE customer_id = 1001
);

クエリの解説:

サブクエリ: 顧客ID 1001の total_amount（総購入額）を取得
メインクエリ: その金額より大きい total_amount を持つ顧客を検索

2-6. 例5：日付に関する比較

最新の注文日より前の注文を取得してみましょう。これは「最後の1件以外を取得する」という意味になります。

やりたいこと：最新の注文日より前（古い）の注文を取得する

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

SELECT 
    order_id,
    order_date,
    total_amount
FROM orders
WHERE order_date < (SELECT MAX(order_date) FROM orders);

クエリの解説:

(SELECT MAX(order_date) FROM orders) → 最新の注文日を取得
WHERE order_date < → その日付より前（小さい = 古い）

💡 サブクエリの実行順序をもう一度確認

サブクエリが最初に実行される（括弧の中）
サブクエリの結果が1つの値として確定する
その値を使ってメインクエリが実行される

この順序を理解しておくと、サブクエリの動作が分かりやすくなります!

🔢 3. 単一行サブクエリ

3-1. 単一行サブクエリとは

単一行サブクエリとは、1つの値だけを返すサブクエリのことです。「平均価格」「最高価格」「件数」など、計算結果が1つの数値や文字列になるものが該当します。

この種類のサブクエリは、比較演算子（=, >, <, >=, <=, <>）と一緒に使います。

📌 単一行を返す代表的な集計関数

関数	意味	返す値の例
`AVG()`	平均値	1500.00
`SUM()`	合計	1250000
`MAX()`	最大値	50000
`MIN()`	最小値	100
`COUNT()`	件数	500

3-2. 例1：平均より高い商品（詳細解説）

単一行サブクエリの最も基本的な例を、もう少し詳しく見てみましょう。

やりたいこと：平均価格より高い商品を取得する

まず、サブクエリ部分だけを確認します。

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

-- サブクエリ部分だけを実行してみる
SELECT AVG(price) FROM products;
-- 結果: 1500（1つの値が返る）

このサブクエリは「1500」という1つの値を返します。これが「単一行サブクエリ」です。

次に、完成形のクエリを見てみましょう。

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

SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

実行の流れ:

(SELECT AVG(price) FROM products) が実行され、結果「1500」が得られる
クエリは WHERE price > 1500 と同じ意味になる
価格が1500より大きい商品が取得される

3-3. 例2：最安値の2倍以上の商品

サブクエリの結果に計算を加えることもできます。

やりたいこと：最安値の2倍以上の価格の商品を取得する

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

SELECT product_name, price
FROM products
WHERE price >= (SELECT MIN(price) FROM products) * 2;

クエリの解説:

(SELECT MIN(price) FROM products) → 最安値を取得（例: 500円）
* 2 → サブクエリの結果に2を掛ける（例: 1000円）
WHERE price >= ... * 2 → 最安値の2倍以上（1000円以上）の商品を取得

ポイント:

サブクエリの結果は数値なので、通常の数値と同じように四則演算ができます。

3-4. 例3：全体の売上に対する割合を計算

SELECT句の中でもサブクエリを使うことで、各注文が全体の何%を占めるかを計算できます。

やりたいこと：全体の売上の10%以上を占める大口注文を探す

まず、何をしたいかを分解して考えましょう。

Step 1: 全体の売上合計を取得する

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

-- 全体の売上合計
SELECT SUM(total_amount) FROM orders;
-- 結果: 10000000（1000万円）

Step 2: 各注文の割合を計算する式を考える

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

-- 割合の計算式
-- 各注文金額 × 100.0 ÷ 全体の売上 = 割合（%）

Step 3: 完成形のクエリ

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

SELECT 
    order_id,
    total_amount,
    ROUND(total_amount * 100.0 / (SELECT SUM(total_amount) FROM orders), 2) as percentage
FROM orders
WHERE total_amount >= (SELECT SUM(total_amount) FROM orders) * 0.1;

クエリの解説:

total_amount * 100.0 / (サブクエリ) → 各注文が全体の何%かを計算
ROUND(..., 2) → 小数点以下2桁に丸める
(SELECT SUM(total_amount) FROM orders) * 0.1 → 全体の10%の金額
WHERE total_amount >= → その10%以上の注文だけを取得

3-5. 例4：特定の顧客と同じ都道府県に住む顧客

数値だけでなく、文字列を返すサブクエリも作れます。

やりたいこと：顧客ID 1005と同じ都道府県に住む顧客を探す

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

SELECT 
    customer_id,
    customer_name,
    prefecture
FROM customers
WHERE prefecture = (
    SELECT prefecture 
    FROM customers 
    WHERE customer_id = 1005
);

クエリの解説:

サブクエリ: 顧客ID 1005の prefecture（都道府県）を取得（例: '東京都'）
メインクエリ: prefecture = '東京都' の顧客を検索

なぜこれが単一行サブクエリ?

customer_id = 1005 という条件で、1人の顧客だけが特定されるため、サブクエリは1つの値（都道府県名）だけを返します。

3-6. 単一行サブクエリの注意点：エラーになるケース

比較演算子（=, >, <など）を使う場合、サブクエリは必ず1つの値を返す必要があります。複数の値を返すとエラーになります。

⚠️ エラーになる例

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

-- これはエラーになる!
SELECT product_name, price
FROM products
WHERE price = (
    SELECT price FROM products WHERE category = '家電'
);
-- エラー! '家電'カテゴリには複数の商品があるため、複数の価格が返ってくる

なぜエラーになるのか:

「家電」カテゴリには複数の商品があります。例えば、テレビ（50,000円）、冷蔵庫（80,000円）、掃除機（30,000円）など。サブクエリが3つの価格を返すため、「price = 50000, 80000, 30000」という意味になり、SQLは処理できません。

解決方法:

集計関数（AVG、MAXなど）を使って1つの値にする
または、IN演算子を使う（次のセクションで説明）

📋 4. 複数行サブクエリとIN演算子

4-1. 複数行サブクエリとは

複数行サブクエリとは、複数の値（行）を返すサブクエリのことです。「注文された商品のID一覧」「東京都の顧客ID一覧」など、リスト形式の結果が該当します。

複数行サブクエリは、比較演算子ではなくIN演算子と一緒に使います。

4-2. IN演算子の復習

まず、IN演算子の基本を復習しましょう。IN演算子は「〜のどれかに一致する」という条件を表します。

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

-- IN演算子の基本（値を直接指定する場合）
SELECT * FROM products
WHERE category IN ('家電', '食品', '本');

命令の意味:

WHERE category IN ('家電', '食品', '本')
→ categoryが「家電」「食品」「本」のいずれかに一致する商品を取得
これは WHERE category = '家電' OR category = '食品' OR category = '本' と同じ意味

4-3. IN演算子とサブクエリの組み合わせ

IN演算子の括弧の中に、値を直接書く代わりにサブクエリを書くことができます。

やりたいこと：実際に注文されたことがある商品だけを取得する

まず、注文された商品のIDを取得するサブクエリを作ります。

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

-- 注文された商品のIDを取得（サブクエリ部分）
SELECT DISTINCT product_id 
FROM order_details;
-- 結果: 101, 105, 203, 301, 402, ...（複数の値）

次に、このサブクエリをIN演算子と組み合わせます。

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

SELECT 
    product_id,
    product_name,
    price
FROM products
WHERE product_id IN (
    SELECT DISTINCT product_id 
    FROM order_details
);

クエリの解説:

SELECT DISTINCT product_id FROM order_details → 注文された商品IDのリストを取得
DISTINCT → 重複を除去（同じ商品が複数回注文されていても1回だけカウント）
WHERE product_id IN (サブクエリ) → そのリストに含まれる商品IDを持つ商品を取得

実行結果のイメージ:

product_id	product_name	price
101	ワイヤレスマウス	2,500
105	USBケーブル	800
203	コーヒー豆	1,200

4-4. 例2：東京都の顧客が購入した商品（入れ子サブクエリ）

サブクエリの中にさらにサブクエリを入れる「入れ子（ネスト）」も可能です。複雑な条件を段階的に絞り込むことができます。

やりたいこと：東京都に住む顧客が購入した商品を取得する

これを実現するには、以下の3段階で考えます。

Step 1: 東京都の顧客IDを取得する

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

-- 東京都の顧客ID
SELECT customer_id
FROM customers
WHERE prefecture = '東京都';
-- 結果: 1001, 1005, 1012, ...（東京都の顧客IDリスト）

Step 2: その顧客の注文IDを取得する

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

-- 東京都の顧客の注文ID
SELECT order_id
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE prefecture = '東京都'
);
-- 結果: 10001, 10015, 10023, ...（注文IDリスト）

Step 3: その注文に含まれる商品IDを取得する

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

-- 東京都の顧客が注文した商品ID
SELECT DISTINCT product_id
FROM order_details
WHERE order_id IN (
    SELECT order_id
    FROM orders
    WHERE customer_id IN (
        SELECT customer_id
        FROM customers
        WHERE prefecture = '東京都'
    )
);
-- 結果: 101, 203, 305, ...（商品IDリスト）

完成形: 商品の詳細情報を取得する

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

SELECT 
    product_name,
    category,
    price
FROM products
WHERE product_id IN (
    SELECT DISTINCT product_id
    FROM order_details
    WHERE order_id IN (
        SELECT order_id
        FROM orders
        WHERE customer_id IN (
            SELECT customer_id
            FROM customers
            WHERE prefecture = '東京都'
        )
    )
);

入れ子サブクエリの実行順序:

最も内側のサブクエリから実行される（東京都の顧客ID）
その結果を使って次のサブクエリが実行される（注文ID）
さらにその結果を使って次のサブクエリが実行される（商品ID）
最後にメインクエリが実行される（商品の詳細）

4-5. NOT INで除外する

NOT INを使うと、「含まれない」条件を指定できます。

やりたいこと：一度も注文されていない商品を取得する

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

SELECT 
    product_name,
    price,
    stock
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_details
);

クエリの解説:

NOT IN → リストに含まれないものを取得
サブクエリ: 注文されたことがある商品IDのリスト
結果: そのリストに含まれない商品 = 一度も注文されていない商品

📌 IN vs NOT IN

演算子	意味	使用例
`IN`	リストに含まれるものを取得	注文された商品を探す
`NOT IN`	リストに含まれないものを取得	注文されていない商品を探す

4-6. 例5：売れ筋商品で在庫が少ないものを探す

サブクエリと通常の条件を組み合わせることで、より複雑な条件を表現できます。

やりたいこと：10回以上注文された商品で、かつ在庫が50個未満の商品を探す

まず、売れ筋商品（10回以上注文された商品）を取得するサブクエリを作ります。

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

-- 10回以上注文された商品ID
SELECT product_id
FROM order_details
GROUP BY product_id
HAVING COUNT(*) >= 10;

次に、完成形のクエリを作ります。

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

SELECT 
    product_name,
    stock,
    price
FROM products
WHERE product_id IN (
    -- 10回以上注文された商品
    SELECT product_id
    FROM order_details
    GROUP BY product_id
    HAVING COUNT(*) >= 10
)
AND stock < 50;

クエリの解説:

GROUP BY product_id → 商品ごとにグループ化
HAVING COUNT(*) >= 10 → 注文回数が10回以上のグループだけを残す
AND stock < 50 → さらに在庫が50個未満という条件を追加

活用シーン:

このようなクエリは「補充が必要な人気商品」を見つけるのに役立ちます。売れているのに在庫が少ない商品は、早急に発注する必要がありますね!

✅ 5. EXISTS演算子との組み合わせ

5-1. EXISTS演算子とは

EXISTSは、サブクエリの結果が存在するかどうかをチェックする演算子です。INとは考え方が少し異なります。

日常生活で例えると、INは「この人は名簿に載っていますか？」と名簿をチェックするイメージ。EXISTSは「この人は何か注文したことがありますか？」と履歴があるかどうかをチェックするイメージです。

💡 EXISTSの特徴

TRUE/FALSEを返す（データの値ではない）
結果が1件でもあればTRUE（全件調べる必要がない）
大量データの場合、パフォーマンスが良いことが多い
相関サブクエリとして使うことが多い

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

EXISTSを理解する前に、「相関サブクエリ」という概念を知っておきましょう。

これまで見てきたサブクエリは、メインクエリとは独立して実行できました。しかし相関サブクエリは、メインクエリの各行を参照しながら実行されます。

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

種類	特徴	例
通常のサブクエリ	サブクエリ単体で実行できる	`SELECT AVG(price) FROM products`
相関サブクエリ	メインクエリの値を参照する	`WHERE orders.customer_id = customers.customer_id`

5-3. 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
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id
);

クエリの解説:

SELECT 1 → 「何か1つの値」を返すという意味。EXISTSは値の中身を見ないので、1でも*でも何でもOK
WHERE orders.customer_id = customers.customer_id → 相関サブクエリ!メインクエリのcustomersテーブルの各行を参照している
EXISTS (...) → サブクエリの結果が1行でもあればTRUE

💡 EXISTSの実行イメージ

customersテーブルの1行目（顧客ID: 1001）を見る
「ordersテーブルにcustomer_id = 1001の注文はあるか？」を確認
あればTRUE → この顧客を結果に含める
customersテーブルの2行目（顧客ID: 1002）を見る
同様に繰り返す...

つまり、メインクエリの各行に対して、サブクエリが実行されるイメージです。

5-4. NOT EXISTSで「存在しない」をチェック

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

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

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

クエリの解説:

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

活用シーン:

一度も注文していない顧客に対して、初回購入キャンペーンのメールを送りたい場合などに使えます。

5-5. 複数の条件を含むEXISTS

やりたいこと：2024年に1万円以上の注文をした顧客を取得する

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

SELECT 
    customer_id,
    customer_name
FROM customers
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id
      AND orders.order_date >= '2024-01-01'
      AND orders.total_amount >= 10000
);

クエリの解説:

サブクエリの中に複数の条件（AND）を追加できる
order_date >= '2024-01-01' → 2024年以降の注文
total_amount >= 10000 → 1万円以上の注文
これらすべての条件を満たす注文が存在する顧客だけを取得

5-6. INとEXISTSの違い

INとEXISTSは、多くの場合同じ結果を返しますが、使い方や性能に違いがあります。

📊 IN vs EXISTS 比較表

項目	IN	EXISTS
動作の仕組み	値のリストを作って比較	存在するかどうかをチェック
サブクエリの種類	通常のサブクエリ	相関サブクエリ
NULLの扱い	NULLがあると予期せぬ結果になる場合がある	NULLに強い
パフォーマンス	サブクエリの結果が少ない場合に有利	サブクエリの結果が多い場合に有利
読みやすさ	シンプルで直感的	少し複雑だが表現力が高い

📌 どちらを使うべき?

IN: シンプルで読みやすい。初心者はまずINから使ってOK
EXISTS: 大量データで高速。複雑な条件を書きたいとき
どちらでも動く場合は、読みやすい方を選びましょう!
パフォーマンスが気になる場合は、両方試して速い方を使う

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

ここでは、実務でよく使われるサブクエリのパターンを紹介します。これらのパターンを覚えておくと、様々な場面で応用できます。

6-1. パターン1：トップN件を除外する

やりたいこと：価格トップ10の商品を除いた残りの商品を取得する

活用シーン:

高級品を除いた「お手頃価格の商品」リストを作りたい
上位の外れ値を除いて分析したい

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

SELECT product_name, price
FROM products
WHERE product_id NOT IN (
    SELECT product_id
    FROM products
    ORDER BY price DESC
    LIMIT 10
);

クエリの解説:

サブクエリ: ORDER BY price DESC LIMIT 10 で価格トップ10の商品IDを取得
メインクエリ: NOT IN でそれらを除外した商品を取得

6-2. パターン2：同じカテゴリ内での比較

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

活用シーン:

カテゴリ内で「高価格帯」の商品を特定したい
各グループの中で突出したデータを見つけたい

これは相関サブクエリを使います。メインクエリの各商品について、その商品と同じカテゴリの平均価格を計算します。

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

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

クエリの解説:

products p1 と products p2 → 同じテーブルに別名をつけて区別
WHERE p2.category = p1.category → メインクエリの商品と同じカテゴリだけを対象に平均を計算
商品ごとに「自分のカテゴリの平均」と比較している

例:

「家電」カテゴリの平均が20,000円、「食品」カテゴリの平均が500円の場合、家電は20,000円以上、食品は500円以上のものがそれぞれ抽出されます。

6-3. パターン3：複数の集計値を条件に使う

やりたいこと：平均より高く、かつ最高価格の50%以下の商品（中価格帯）を取得する

活用シーン:

「ちょうどいい価格帯」の商品を探したい
極端な値を除いた中間層を分析したい

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

SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
  AND price <= (SELECT MAX(price) FROM products) * 0.5;

クエリの解説:

price > (SELECT AVG(price) FROM products) → 平均より高い
price <= (SELECT MAX(price) FROM products) * 0.5 → 最高価格の半分以下
2つの条件を AND で組み合わせ

6-4. パターン4：期間による絞り込み

やりたいこと：直近30日間に注文があった商品を取得する

活用シーン:

最近売れている商品を把握したい
季節商品の動向を確認したい

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

SELECT 
    product_name,
    price
FROM products
WHERE product_id IN (
    SELECT DISTINCT product_id
    FROM order_details
    WHERE order_id IN (
        SELECT order_id
        FROM orders
        WHERE order_date >= DATE('now', '-30 days')
    )
);

クエリの解説:

DATE('now', '-30 days') → SQLiteで「今日から30日前」を計算（MySQLでは DATE_SUB(NOW(), INTERVAL 30 DAY)）
内側から外側に向かって、「30日以内の注文」→「その注文に含まれる商品」→「商品の詳細」と辿る

6-5. パターン5：OR条件での組み合わせ

やりたいこと：高評価（4.5以上）または売れ筋（20回以上注文）の商品を取得する

活用シーン:

「おすすめ商品」リストを作りたい
複数の基準のどれかを満たすものを探したい

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

SELECT 
    product_name,
    rating,
    price
FROM products
WHERE product_id IN (
    -- 高評価商品（評価4.5以上）
    SELECT product_id
    FROM products
    WHERE rating >= 4.5
)
OR product_id IN (
    -- 売れ筋商品（20回以上注文）
    SELECT product_id
    FROM order_details
    GROUP BY product_id
    HAVING COUNT(*) >= 20
);

クエリの解説:

2つのサブクエリを OR で結合
「高評価」または「売れ筋」のどちらかに該当すれば取得される
両方に該当する商品は重複せず1回だけ表示される

💡 サブクエリパターンのまとめ

パターン	使う演算子	主な用途
平均との比較	`>`, `<`	平均より上/下のデータを探す
最大/最小との比較	`=`, `>=`	極端な値を持つデータを探す
リストとの照合	`IN`	関連するデータを探す
除外	`NOT IN`	特定のデータを除く
存在確認	`EXISTS`	関連データがあるか確認
不存在確認	`NOT EXISTS`	関連データがないものを探す

📝 練習問題

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

問題 1基本

平均価格より安い商品

productsテーブルから、平均価格より安い商品の名前と価格を取得してください。

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

SELECT product_name, price
FROM products
WHERE price < (SELECT AVG(price) FROM products);

解説:

サブクエリ (SELECT AVG(price) FROM products) で平均価格を計算
WHERE price < で平均より小さい（安い）商品を取得

問題 2基本

最高価格の商品

productsテーブルから、最も高い価格の商品を取得してください。

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

SELECT product_name, price, category
FROM products
WHERE price = (SELECT MAX(price) FROM products);

解説:

サブクエリ (SELECT MAX(price) FROM products) で最高価格を取得
WHERE price = でその価格と等しい商品を取得
同じ最高価格の商品が複数あれば、すべて表示される

問題 3基本

注文された商品

productsテーブルから、実際に注文されたことがある商品（order_detailsに存在する）を取得してください。

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

SELECT product_name, price
FROM products
WHERE product_id IN (
    SELECT DISTINCT product_id
    FROM order_details
);

解説:

サブクエリで注文された商品IDのリストを取得
DISTINCT で重複を除去（同じ商品が複数回注文されていても1回だけ）
IN 演算子でリストに含まれる商品を取得

問題 4応用

一度も注文されていない商品

一度も注文されていない商品を取得してください。

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

SELECT product_name, price, stock
FROM products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id
    FROM order_details
);

解説:

NOT IN を使って、リストに含まれない商品を取得
注文されたことがある商品IDのリストに含まれない = 一度も注文されていない

問題 5応用

カテゴリ別の平均より高い

「家電」カテゴリの平均価格より高い、すべてのカテゴリの商品を取得してください。

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

SELECT product_name, category, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
    WHERE category = '家電'
);

解説:

サブクエリで「家電」カテゴリの平均価格を計算
メインクエリには WHERE category = '家電' がないので、全カテゴリが対象
家電以外のカテゴリでも、家電の平均より高ければ取得される

問題 6応用

注文履歴がある顧客（EXISTS版）

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

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

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

解説:

EXISTS は存在するかどうかをチェック
SELECT 1 は「何か値を返す」という意味（1でなくても*でもOK）
orders.customer_id = customers.customer_id で相関サブクエリを実現

問題 7応用

東京都の顧客が購入した商品

東京都に住む顧客が購入したことがある商品を取得してください。

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

SELECT DISTINCT product_name, price
FROM products
WHERE product_id IN (
    SELECT product_id
    FROM order_details
    WHERE order_id IN (
        SELECT order_id
        FROM orders
        WHERE customer_id IN (
            SELECT customer_id
            FROM customers
            WHERE prefecture = '東京都'
        )
    )
);

解説:

サブクエリを入れ子（ネスト）にして、段階的に絞り込む
最も内側: 東京都の顧客ID → その顧客の注文ID → 注文された商品ID
最終的にその商品IDを持つ商品の詳細を取得

問題 8チャレンジ

高額注文をした顧客

全注文の平均金額より高い注文をしたことがある顧客を取得してください。

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

SELECT DISTINCT customer_name, email
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE total_amount > (
        SELECT AVG(total_amount)
        FROM orders
    )
);

解説:

最も内側のサブクエリ: 全注文の平均金額を計算
中間のサブクエリ: 平均より高い注文をした顧客IDを取得
メインクエリ: その顧客の詳細情報を取得
DISTINCT で同じ顧客が複数回表示されるのを防ぐ

問題 9チャレンジ

リピーターが購入している商品

3回以上注文をしている顧客（リピーター）が購入している商品を取得してください。

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

SELECT DISTINCT product_name, price
FROM products
WHERE product_id IN (
    SELECT product_id
    FROM order_details
    WHERE order_id IN (
        SELECT order_id
        FROM orders
        WHERE customer_id IN (
            -- 3回以上注文している顧客
            SELECT customer_id
            FROM orders
            GROUP BY customer_id
            HAVING COUNT(*) >= 3
        )
    )
);

解説:

最も内側: GROUP BY と HAVING で3回以上注文した顧客を特定
その顧客の注文 → 注文された商品 → 商品詳細、と辿る
リピーターが好む商品を分析するのに使える

問題 10チャレンジ

売れ筋かつ在庫少

10回以上注文されている商品で、かつ在庫が30個未満の商品を取得してください。

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

SELECT product_name, stock, price
FROM products
WHERE stock < 30
  AND product_id IN (
      SELECT product_id
      FROM order_details
      GROUP BY product_id
      HAVING COUNT(*) >= 10
  );

解説:

サブクエリ: 10回以上注文された商品IDを取得
メインクエリ: 在庫30未満 AND 売れ筋リストに含まれる
補充が必要な人気商品を特定するのに使える

問題 11チャレンジ

一度も注文していない顧客（NOT EXISTS版）

NOT EXISTSを使って、一度も注文していない顧客を取得してください。

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

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

解説:

NOT EXISTS は「存在しない」をチェック
その顧客IDの注文が1件も存在しない顧客を取得
初回購入キャンペーンの対象者を探すのに使える

問題 12チャレンジ

複合条件

平均価格より高く、かつ実際に注文されたことがあり、在庫が50個以上ある商品を取得してください。

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

SELECT product_name, price, stock
FROM products
WHERE price > (SELECT AVG(price) FROM products)
  AND stock >= 50
  AND product_id IN (
      SELECT DISTINCT product_id
      FROM order_details
  );

解説:

3つの条件を AND で組み合わせ
条件1: 平均価格より高い（単一行サブクエリ）
条件2: 在庫50個以上（通常の条件）
条件3: 注文されたことがある（複数行サブクエリ + IN）

📝 Step 2 のまとめ

✅ 学んだこと

サブクエリは、クエリの中にクエリを入れる技術
WHERE句でサブクエリを使って、動的な条件を指定できる
単一行サブクエリは1つの値を返す（=, >, < で比較）
複数行サブクエリは複数の値を返す（IN, NOT IN で使用）
EXISTSで存在するかどうかをチェックできる
サブクエリを入れ子（ネスト）にして複雑な条件を表現できる

📌 サブクエリの基本パターン

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

-- 単一行サブクエリ（比較演算子）
WHERE 列名 > (SELECT AVG(列名) FROM テーブル)

-- 複数行サブクエリ（IN演算子）
WHERE 列名 IN (SELECT 列名 FROM テーブル WHERE 条件)

-- 複数行サブクエリ（NOT IN演算子）
WHERE 列名 NOT IN (SELECT 列名 FROM テーブル WHERE 条件)

-- EXISTS演算子
WHERE EXISTS (
    SELECT 1 FROM テーブル WHERE 条件
)

-- NOT EXISTS演算子
WHERE NOT EXISTS (
    SELECT 1 FROM テーブル WHERE 条件
)

💡 実務での使いどころ

平均との比較: 平均より上/下のデータを探す
存在チェック: 関連データがあるかを確認
除外処理: NOT INやNOT EXISTSで特定のデータを除く
動的な条件: データに応じて条件を変える
複数テーブルの連携: JOINを使わずに関連データを取得

⚠️ 注意点

比較演算子（=, >, <）は単一行サブクエリで使う
複数行を返す場合はIN演算子を使う（そうしないとエラー）
サブクエリの入れ子は3段階までを目安に（読みやすさ優先）
大量データではパフォーマンスに注意（JOINやEXISTSを検討）

❓ よくある質問

Q1: サブクエリとJOINはどちらを使うべきですか?

どちらでも書ける場合が多いです!一般的な使い分けの目安：

複数の列が必要な場合 → JOIN
存在チェックだけの場合 → サブクエリ（EXISTS）
値の比較をしたい場合 → サブクエリ
迷ったら読みやすい方を選びましょう!

Q2: INとEXISTSの違いは何ですか?

INは値のリストをチェックし、EXISTSは存在をチェックします。

INは「このリストに含まれていますか？」
EXISTSは「関連するデータがありますか？」
大量データの場合、EXISTSの方が高速なことが多い
ただし、多くの場合は結果は同じになります

Q3: 「サブクエリが複数行を返す」エラーが出ます

比較演算子（=, >, <）を使っている場合、サブクエリは必ず1つの値を返す必要があります。

解決方法：

IN演算子に変更する
集計関数（AVG、MAXなど）を使って1つの値にする
LIMIT 1を追加して1行だけ取得する

Q4: サブクエリの中でさらにサブクエリを使えますか?

はい、入れ子（ネスト）にできます!ただし、深くなりすぎると読みにくくなるので、3段階までを目安にしましょう。それ以上複雑になる場合は、WITH句（後のステップで学習）やJOINを検討してください。

Q5: サブクエリは遅いと聞きましたが...

必ずしも遅くありません!

適切に使えば高速に動作します
ただし、大量データで複雑な入れ子になると遅くなる場合があります
遅い場合は、JOINやウィンドウ関数（後のステップで学習）を検討しましょう
実際に両方試して、速い方を使うのがベストです

Q6: DISTINCTはいつ使うべきですか?

INでサブクエリを使う場合、重複を削除するためにDISTINCTを使うことが多いです。

同じ商品が複数回注文されていても、商品IDは1回だけあればOK
DISTINCTを入れると、サブクエリの結果が小さくなり、処理が速くなることも
EXISTSの場合は不要（存在チェックのため）

🎓 次のステップでは

Step 3: FROM句のサブクエリでは、サブクエリをテーブルのように使う「派生テーブル」を学びます。集計結果をさらに加工したり、複雑な計算を段階的に行ったりできるようになります!

📝

学習メモ

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

📋 過去のメモ一覧 ▼