EnglishEnglish العربيةالعربية PortuguêsPortuguês 日本語日本語

サブクエリ

サブクエリ

スーパーマーケットにいて店員に「お店で一番高い商品は何ですか？」と質問する場面を想像してください。店員が確認して「MacBook Proです」と答えます。続けて「この商品を買ったお客さんは誰ですか？」と尋ねます。今あなたがやったことは サブクエリ です。ある質問の答えを使って次の質問をするのです。SQLのサブクエリも同じように動作します。あるクエリの結果を別のクエリの条件やデータソースとして使用する のです。

1. コアコンセプト

サブクエリとは？

サブクエリ（内部クエリまたはネストされたクエリとも呼ばれる）は、別のSQL文に埋め込まれたクエリです。外側のクエリを メインクエリ と呼び、内側のクエリを サブクエリ と呼びます。サブクエリが最初に実行され、その結果がメインクエリによって使用されます。

SQL 
-- サブクエリ：最も高い給与の社員を検索
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

実行順序：まず SELECT MAX(salary) FROM employees を実行して20000を取得し、次に外側のクエリを実行して給与が20000の社員を検索します。

サブクエリの分類

サブクエリが出現する位置に基づいて、主に3つのタイプがあります：

タイプ 場所 目的 返される結果
WHEREサブクエリ WHERE句内 データのフィルタリング 単一値またはリスト
FROMサブクエリ（派生テーブル） FROM句内 一時テーブルとして機能 結果セット
SELECTサブクエリ（スカラーサブクエリ） SELECTカラムリスト内 計算列として機能 単一値

WHEREサブクエリ

最も一般的な使い方です。サブクエリがWHERE句に出現し、フィルタ条件を動的に決定します。

SQL 
-- 平均以上の給与の社員を検索
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

サブクエリは以下を返すことができます：

SQL 
-- 複数値を返す：INで照合
SELECT name, salary
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = '北京'
);

FROMサブクエリ（派生テーブル）

サブクエリがFROM句に出現し、実質的に 一時テーブル（派生テーブルとも呼ばれる）を作成します。エイリアスを付ける必要があります。

SQL 
-- まず部門ごとの平均給与を計算し、そこからフィルタリング
SELECT dept_name, avg_salary
FROM (
    SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.id
    GROUP BY d.name
) AS dept_avg
WHERE avg_salary > 12000;
💡 ヒント： FROMサブクエリにはエイリアスが必要です（上記の AS dept_avg のように）。そうしないとSQLはエラーをスローします。

SELECTサブクエリ（スカラーサブクエリ）

サブクエリがSELECTカラムリストに 計算列 として出現します。メインクエリが行を処理するたびに、サブクエリが1回実行されます。

SQL 
-- 各社員の給与と会社の平均給与を表示
SELECT name, salary,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

出力：

TEXT 
name    salary    avg_salary
------  --------  ----------
張三 15000.00  14000.00
李四 18000.00  14000.00
王五 12000.00  14000.00
趙六 13000.00  14000.00
銭七 20000.00  14000.00
孫八 11000.00  14000.00
周九 9000.00   14000.00
呉十 14000.00  14000.00
💡 ヒント： スカラーサブクエリは 正確に1行1列 を返す必要があります。そうしないとエラーがスローされます。「集計情報」列を追加するのに適しています。

EXISTSとNOT EXISTS

EXISTS はサブクエリが少なくとも1行を返すかどうかをチェックします。返される値は気にしません。「結果があるかどうか」だけが重要です。

SQL 
-- 社員が所属する部門を検索
SELECT d.name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.department_id = d.id
);
SQL 
-- 社員が所属しない部門を検索
SELECT d.name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.department_id = d.id
);
💡 ヒント： EXISTS の中の SELECT 1 は慣例的な做法です。EXISTSは「行が存在するかどうか」だけを気にし、カラムの値は気にしません。SELECT * や SELECT NULL を使用しても同じ効果があります。

サブクエリとJOINのパフォーマンス比較

比較項目 サブクエリ JOIN
可読性 自然言語の思考に近い 結合ロジックの理解が必要
パフォーマンス 簡単なケースでは差がない。相関サブクエリは遅くなる可能性 一般的に良い。専用オプティマイザーあり
柔軟性 集計関数の結果を条件として使用可能 GROUP BYが必要
推奨用途 「最大値を検索」「存在しないものを検索」 「関連付けて表示」「複数テーブルの結合」
💡 ヒント： 最新のデータベース（PostgreSQL、MySQL 8.0など）にはインテリジェントなオプティマイザーがあり、多くのシナリオでサブクエリとJOINのパフォーマンス差は無視できます。可読性の向上 を優先し、パフォーマンスの問題が発生した場合は EXPLAIN で分析・最適化してください。

2. 基本構文／使い方

WHEREサブクエリの構文

SQL 
-- スカラーサブクエリ（単一値を返す）
SELECT カラム名 FROM テーブル名
WHERE カラム名 比較演算子 (SELECT 集計関数 FROM テーブル名);

-- 複数行サブクエリ（リストを返す）
SELECT カラム名 FROM テーブル名
WHERE カラム名 IN (SELECT カラム名 FROM テーブル名 WHERE 条件);

FROMサブクエリの構文

SQL 
SELECT カラム名
FROM (SELECT ... FROM ... WHERE ...) AS エイリアス
WHERE 条件;
💡 ヒント： FROMサブクエリのエイリアスは 必須 です。データベースによって要件が異なりますが、エイリアスを追加するのが最も安全な方法です。

SELECTスカラーサブクエリの構文

SQL 
SELECT カラム1, カラム2,
       (SELECT 集計関数 FROM テーブル名 WHERE 条件) AS エイリアス
FROM テーブル名;
💡 ヒント： スカラーサブクエリが外部クエリのカラムを参照する必要がある場合（相関サブクエリ）、外部クエリのテーブルエイリアスを直接使用できます。

EXISTSの構文

SQL 
SELECT カラム名 FROM テーブル_A a
WHERE EXISTS (SELECT 1 FROM テーブル_B b WHERE b.外部キー = a.主キー);
💡 ヒント： EXISTSは「相関サブクエリ」です。サブクエリが外部クエリのカラム（a.id など）を参照するため、外部クエリの各行に対してサブクエリが1回実行されます。

💡 ヒント： 大きなデータセットでは、INよりもEXISTSの方が通常効率的です。EXISTSは最初の一致を見つけるとすぐに停止しますが、INはすべての結果を返す必要があるためです。

例：平均以上の給与の社員を検索（難易度⭐）

SQL 
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;
▶ 試してみよう

出力：

TEXT 
name    salary
------  --------
銭七 20000.00
李四 18000.00
張三 15000.00
呉十 14000.00

まず平均給与（約14000）を計算し、次に平均以上の社員をフィルタリングします。

例：部門ごとの最高給与の社員を検索（難易度⭐⭐）

SQL 
SELECT e.name, d.name AS department, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);
▶ 試してみよう

出力：

TEXT 
name    department  salary
------  ----------  --------
銭七 技術部  20000.00
王五 営業部   12000.00
呉十 経理部     14000.00
💡 ポイント： これは 相関サブクエリ です。サブクエリ内の e.department_id が外部クエリの現在の行を参照しています。外部クエリの各行に対してサブクエリが1回実行され、その社員の部門での最高給与を見つけ、現在の社員が最高給与かどうかをチェックします。

例：派生テーブルを使用した部門給与レベルの分析（難易度⭐⭐⭐）

SQL 
SELECT dept_name, emp_count, avg_salary,
       CASE 
           WHEN avg_salary >= 15000 THEN '高給与'
           WHEN avg_salary >= 12000 THEN '中給与'
           ELSE '改善が必要'
       END AS level
FROM (
    SELECT d.name AS dept_name, 
           COUNT(e.id) AS emp_count,
           AVG(e.salary) AS avg_salary
    FROM departments d
    LEFT JOIN employees e ON d.id = e.department_id
    GROUP BY d.id, d.name
) AS dept_stats
WHERE emp_count > 0
ORDER BY avg_salary DESC;
▶ 試してみよう

出力：

TEXT 
dept_name  emp_count  avg_salary  level
---------  ---------  ----------  ----------
技術部 3          17666.67    高給与
経理部     2          13500.00    中給与
営業部   2          11500.00    改善が必要
💡 アプローチ： FROMサブクエリがまず部門ごとの社員数と平均給与を計算し、次に外側のクエリがこれらの統計を分類・フィルタリングします。この「まず集計してから分析」パターンは、レポート開発で非常に一般的です。

3. よくある使用ケース

ケース1：注文されたことのない商品を検索

SQL 
SELECT p.name, p.category, p.price
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.product_id = p.id
);

出力（データに依存）：

TEXT 
name      category  price
--------  --------  -------
Mac Mini  Computer  4499.00
💡 比較： 同じ要件をLEFT JOINで実装した場合：

SQL 
SELECT p.name, p.category, p.price
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.id IS NULL;

両方のアプローチは同様のパフォーマンスを持ちます。可読性の高い方を選択してください。

ケース2：注文金額が平均を超える顧客を検索

SQL 
SELECT customer_name, total_spent
FROM (
    SELECT o.customer_name, 
           SUM(o.quantity * p.price) AS total_spent
    FROM orders o
    JOIN products p ON o.product_id = p.id
    GROUP BY o.customer_name
) AS customer_totals
WHERE total_spent > (
    SELECT AVG(o.quantity * p.price)
    FROM orders o
    JOIN products p ON o.product_id = p.id
);

出力：

TEXT 
customer_name  total_spent
-------------  -----------
小李        14397.00
小剛      17998.00
小王      11998.00
💡 アプローチ： FROMサブクエリが各顧客の支出合計を計算し、WHEREサブクエリが平均注文金額を計算し、外側のクエリが平均以上の顧客をフィルタリングします。

❓ よくある質問

質問：サブクエリは最大何レベルまでネストできますか？ 回答： 理論的な制限はありませんが、実際には3レベルを超えないことをお勧めします。レベルが多すぎるとクエリロジックが複雑すぎることを示します。複数のステップに分割するか、JOINで書き換えることを検討してください。

質問：INとEXISTSのどちらを使用すべきですか？ 回答： サブクエリの結果セットが小さく、外側のテーブルが大きい場合、INの方が効率的です。外側のテーブルが小さく、サブクエリのテーブルが大きい場合、EXISTSの方が効率的です（EXISTSは最初の一致で停止するため）。簡単なシナリオでは差はほとんどありません。複雑なケースではEXPLAINで比較してください。

質問：スカラーサブクエリが複数行を返した場合はどうなりますか？ 回答： データベースはエラーをスローします。スカラーサブクエリは正確に1行1列を返す必要があります。行数が不明な場合は、LIMIT 1 または集計関数（MAX、MIN など）を使用して単一値を保証してください。

質問：相関サブクエリと非相関サブクエリの違いは何ですか？ 回答： 非相関サブクエリは独立して1回実行されます（例：「平均給与を検索」）。その結果はメインクエリによって使用されます。相関サブクエリは外部クエリのカラムを参照し、外部クエリの各行に対して1回実行されます（例：「部門ごとの最高給与を検索」）。相関サブクエリは大きなデータセットでは遅くなる可能性があるため、パフォーマンスに注意してください。

📖 まとめ

📝 演習

演習1（⭐）：サブクエリを使用して「技術部」のすべての社員を検索してください（ヒント：まずサブクエリで技術部のIDを検索します）。

演習2（⭐⭐）：FROMサブクエリ（派生テーブル）を使用して、各顧客の注文数と支出合計を計算し、支出合計が5000を超える顧客をフィルタリングしてください。

演習3（⭐⭐⭐）：EXISTSを使用して「すべての商品を注文した顧客」を検索してください。つまり、「注文されていない商品」が存在しない顧客を検索します（ヒント：二重NOT EXISTSロジック、または別の方法で実装）。

次のレッスン

👉 10-set-operations - 集合演算：UNION、UNION ALL、INTERSECT、EXCEPTを学び、複数のクエリ結果に対する集合演算をマスターしましょう！

Web-Tutorial.com

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

100%