SQLでサブクエリを使用して特定条件下の最大・最小値を取得する方法

最大・最小の値だけならMAX／MIN、その値を持つ行ならサブクエリとの比較を使います。重要なのは、集計する範囲と外側で返す行の範囲をそろえることです。部署10の最大給与を求めても、外側に部署条件がなければ同じ給与の別部署の人まで返ります。この記事では条件漏れ、同率、NULL、グループ別の取得を分けて説明します。

SQLにおいて、特定の条件に基づいてデータベース内の最大値や最小値を取得するためにサブクエリを使用する方法について詳しく解説します。サブクエリは、複雑なクエリをシンプルにし、ネストされたクエリ内で動的にデータを取得する強力な手法です。この記事では、サブクエリの基本概念から、具体的なクエリ例、実務での使用例までを網羅し、特定条件下でのデータ操作の理解を深めます。

サブクエリの基本概念

サブクエリとは、SQLクエリ内に含まれる別のクエリのことです。メインクエリに対して一時的なデータを提供し、複雑なデータ操作を行う際に役立ちます。サブクエリは、SELECT、INSERT、UPDATE、DELETE文の中で使用され、特定の条件を満たすレコードの抽出や計算を行うために使われます。

サブクエリの使い方

サブクエリは、次のような状況で使用されます：

1. 特定の条件に基づいてデータを絞り込む。
2. 集計関数と組み合わせて、データの最大値や最小値、平均値などを計算する。
3. 他のテーブルと結合して、条件に一致するデータを取得する。

サブクエリの基本構文

サブクエリは、通常、括弧で囲まれたクエリとして記述されます。以下はemployeesにemployee_id、department_id、salary列がある場合の具体例です。比較演算子の右側には、GROUP BYなしのMAXで1つの値を返すサブクエリを置きます：

SELECT employee_id, department_id, salary
FROM employees
WHERE department_id = 10
  AND salary = (
      SELECT MAX(salary) FROM employees WHERE department_id = 10
  );

次の項目では、具体的な例を通じて、サブクエリを使用して特定条件下の最大値と最小値を取得する方法を説明します。

最大値を取得するサブクエリ

サブクエリを使用して特定条件下で最大値を取得する方法を説明します。以下は、特定の条件に基づいて最大値を取得する具体的なSQLクエリの例です。

例: 従業員テーブルから最大給与を取得する

従業員のデータが格納されている employees テーブルから、特定の部署に所属する従業員の最大給与を取得するクエリを考えます。

以下は、department_id が 10 の部署に所属する従業員の最大給与の値だけを取得する集計クエリです（この単独SELECTはサブクエリではありません）：

SELECT MAX(salary)
FROM employees
WHERE department_id = 10;

このクエリは、department_id が 10 の従業員の salary 列から最大値を取得します。

サブクエリを使用した別の方法

場合によっては、サブクエリを使用して最大値を取得することが必要です。次のクエリは、サブクエリを使用して最大給与を持つ従業員の詳細を取得します：

SELECT *
FROM employees
WHERE department_id = 10
  AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 10);

このクエリは、department_id が 10 の部署に所属する従業員の中で最大給与を持つ従業員の全情報を取得します。内側と外側の両方に部署10の条件があるため、同じ給与の別部署の人を除外できます。同率の最大給与が複数人いれば全員を返します。表示順が必要ならORDER BYを追加します。

このようにして、サブクエリを活用することで、特定の条件に基づいたデータの最大値を柔軟に取得することができます。次の項目では、最小値を取得するためのサブクエリの使用方法について説明します。

最小値を取得するサブクエリ

サブクエリを使用して特定条件下で最小値を取得する方法を説明します。以下は、特定の条件に基づいて最小値を取得する具体的なSQLクエリの例です。

例: 従業員テーブルから最小給与を取得する

従業員のデータが格納されている employees テーブルから、特定の部署に所属する従業員の最小給与を取得するクエリを考えます。

以下は、department_id が 20 の部署に所属する従業員の最小給与の値だけを取得する集計クエリです（この単独SELECTはサブクエリではありません）：

SELECT MIN(salary)
FROM employees
WHERE department_id = 20;

このクエリは、department_id が 20 の従業員の salary 列から最小値を取得します。

サブクエリを使用した別の方法

場合によっては、サブクエリを使用して最小値を取得することが必要です。次のクエリは、サブクエリを使用して最小給与を持つ従業員の詳細を取得します：

SELECT *
FROM employees
WHERE department_id = 20
  AND salary = (SELECT MIN(salary) FROM employees WHERE department_id = 20);

このクエリは、department_id が 20 の部署に所属する従業員の中で最小給与を持つ従業員の全情報を取得します。内側と外側の両方に部署20の条件があるため、同じ給与の別部署の人を除外できます。同率の最低給与が複数人いれば全員を返します。

このようにして、サブクエリを活用することで、特定の条件に基づいたデータの最小値を柔軟に取得することができます。次の項目では、実際の使用例を通じてサブクエリの応用方法を紹介します。

実際の使用例

サブクエリを使用して特定条件下で最大値や最小値を取得する方法の実務での使用例を紹介します。これにより、サブクエリの応用方法について具体的なイメージを持っていただけるでしょう。

例1: 商品テーブルからカテゴリー別の最高価格を取得する

商品データが格納されている products テーブルから、各カテゴリーの最高価格の商品を取得するクエリを考えます。

以下は、各カテゴリーごとに最高価格の商品を取得するサブクエリです：

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

このクエリは、各カテゴリーに対して最高価格を持つ商品の詳細を取得します。サブクエリが各カテゴリー内の最高価格を計算し、その値をメインクエリが使用して特定の商品をフィルタリングします。

例2: 学生テーブルからクラス別の最低成績を取得する

学生データが格納されている students テーブルから、各クラスの最低成績を取得するクエリを考えます。

以下は、各クラスごとに最低成績を取得するサブクエリです：

SELECT class, student_name, score
FROM students s
WHERE score = (SELECT MIN(score) 
               FROM students 
               WHERE class = s.class);

このクエリは、各クラスに対して最低成績を持つ学生の詳細を取得します。サブクエリが各クラス内の最低成績を計算し、その値をメインクエリが使用して特定の学生をフィルタリングします。

例3: 売上テーブルから月別の最大売上額を取得する

売上データが格納されている sales テーブルから、各月の最大売上額を取得するクエリを考えます。

以下は、各月ごとに1明細あたりの最大売上額を取得する集計例です。month列は2026-01のように年を含む月キーである前提です。1〜12の月番号だけで複数年を混在させると、別年の同月まで合算対象になります：

SELECT month, MAX(amount) as max_amount
FROM sales
GROUP BY month;

このクエリは月別の最大のamountを返し、月間合計ではありません。月間合計が必要ならSUM(amount)を使います。サブクエリを使用せずに GROUP BY を使用する例ですが、サブクエリを組み合わせることでより複雑な条件にも対応できます。

これらの例から、サブクエリを用いて特定条件下で最大値や最小値を取得する方法が実務でどのように応用されるかを理解できるでしょう。次の項目では、サブクエリとJOINの比較について説明します。

サブクエリとJOINの比較

サブクエリとJOINは、データベースから必要な情報を取得するために使用される強力な手法ですが、それぞれの特性と適用場面が異なります。ここでは、サブクエリとJOINの違いと使い分けについて説明します。

サブクエリの特性

サブクエリは、別のクエリの内部にネストされたクエリです。以下の特徴があります：

• 相関の有無：外側の列を参照しないサブクエリと、p.categoryのように外側を参照する相関サブクエリがあります。相関サブクエリ部分はそのまま単独実行できません。
• 階層構造：複数のレベルにネストできるため、複雑なクエリをシンプルに記述できます。
• パフォーマンス：相関条件、索引、統計情報、DBMSの最適化に依存します。表の大小だけでは判断できません。

JOINの特性

JOINは、複数のテーブルを結合して単一の結果セットを生成するための手法です。以下の特徴があります：

• 直接結合：テーブル間のリレーションシップを明示的に指定してデータを結合します。
• フラットな結果セット：複数のテーブルからのデータを1つのフラットな結果セットにまとめます。
• パフォーマンス：結合条件と件数、索引に依存します。JOINへ書き換えるだけで高速になる保証はありません。

サブクエリとJOINの使い分け

それぞれの手法が適している状況について見ていきます：

• サブクエリを使用する場合：
• 単一の値を取得する場合（最大値、最小値、平均値など）。
• 階層的なデータを扱う場合。
• 独立したデータをフィルタリングする場合。

例：最大給与を持つ従業員を取得するサブクエリ

SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

• JOINを使用する場合：
• 複数のテーブルから関連するデータを結合する場合。
• 集計データを生成する場合。
• 集計した値と明細を明示的に結び付けたい場合。

例：従業員とその部署名を取得するJOIN

SELECT e.*, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

実際の選択基準

• 単純なフィルタリング：サブクエリを使用
• 複雑なリレーションシップ：JOINを使用
• パフォーマンス重視：同じ結果になることを確認して実行計画を比較

サブクエリとJOINを適切に使い分けることで、データベースクエリの効率と可読性を向上させることができます。次の項目では、この記事の内容をまとめます。

サンプルデータで条件漏れと同率を確認する

空の検証用データベースで使う例です。既存のemployeesテーブルがある本番環境へそのまま投入しないでください。給与は比較用の仮の数値です。

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    department_id INTEGER,
    salary INTEGER
);
INSERT INTO employees VALUES
    (1, 10, 500), (2, 10, 700), (3, 10, 700),
    (4, 20, 700), (5, 20, 300), (6, 10, 300),
    (7, 30, NULL);

上のデータでは部署10の最大給与は700で、返すemployee_idは2と3です。外側のdepartment_id = 10を省くと、部署20のID 4まで混入します。部署20の最小給与は300でID 5だけです。外側の部署条件がなければ部署10のID 6まで返ります。

NULL・対象なし・同率の扱い

MAX／MINはNULLでない値を集計します。対象行がない場合や値がすべてNULLの場合、GROUP BYなしの集計結果はNULLです。salary = NULLは真にならないため、対応する行の抽出結果は0件になります。NULLの行も返す設計なら、別途要件を定めてIS NULLなどを使います。0への置換は、実際の値が0なのか不明なのかを区別できなくするので慎重に扱います。

productsのcategory、studentsのclassがNULLの場合も、内外の「=」比較ではNULL同士は一致しません。これらの相関例はグループキーがNULLでない前提です。NULLを1グループとして扱うなら、DBMSのNULL安全な比較や「同値、または双方IS NULL」の条件を明示してください。NULL比較の公式説明も参照できます。

有効行だけ、指定期間だけ、特定テナントだけ、といった条件も内側と外側で一致させます。最大値だけでなく、どの集合の中の最大なのかを先に決めることが重要です。

グループ別最大の行を集計サブクエリとJOINで返す

SELECT e.employee_id, e.department_id, e.salary
FROM employees e
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) m ON m.department_id = e.department_id
   AND m.max_salary = e.salary
ORDER BY e.department_id, e.employee_id;

この例も同率をすべて返し、サンプルではID 2・3・4になります。部署30は給与がすべてNULLなので返りません。MySQLのグループ別最大行の公式例でも複数の書き方が紹介されています。単なる従業員と部署のJOINは別の処理なので、速度比較では同じ結果を返すSQLを比較します。

同率でも1件だけにしたい場合

ROW_NUMBERを利用できるDBMSでは、最後に一意なキーを並べ替え条件へ追加して選択を確定させます。次はNULL給与を除外し、部署ごとに最高給与、同率ならemployee_idが小さい1人を返す例です。

SELECT employee_id, department_id, salary
FROM (
    SELECT employee_id, department_id, salary,
           ROW_NUMBER() OVER (
               PARTITION BY department_id
               ORDER BY salary DESC, employee_id ASC
           ) AS rn
    FROM employees
    WHERE salary IS NOT NULL
) ranked
WHERE rn = 1
ORDER BY department_id;

サンプルの結果はID 2と4です。同率を全員返す目的とは異なります。ウィンドウ関数の公式説明のとおり、同じ順位条件だけでは同率行の順番は確定しません。ROW_NUMBERでrn = 1とする場合はタイブレークの業務ルールを決めます。

実データで速さを検討するときは、例えば部署と給与の複合索引が候補になりますが、列順、絞り込み条件、件数、更新負荷で効果は変わります。実行計画と代表データで測定し、「JOINのほうが常に速い」と断定しません。

検証範囲：このサンプルの最大・最小、同率、集計JOIN、ROW_NUMBER、対象なし・全NULLのケースはローカルのSQLite 3.53.1で実行して結果を確認しました。MySQL／PostgreSQL／SQL Serverの実機性能を測定したものではありません。利用DBMSの対応バージョン、型、NULLの仕様を確認してください。

まとめ

サブクエリを使用して特定条件下の最大値や最小値を取得する方法について解説しました。サブクエリは、複雑なクエリをシンプルにし、特定条件に基づいて動的にデータを取得する強力な手法です。具体的な使用例やJOINとの比較を通じて、適切な場面での使い分けが重要であることを理解していただけたと思います。

特に、サブクエリは単一の値を取得する場合や階層的なデータ操作に適しており、JOINは複数のテーブルから関連データを結合する場合に有効です。データベース操作において、これらの手法を適切に活用することで、効率的で効果的なクエリを実行できるようになります。

サブクエリとJOINを使い分け、複雑なデータ操作にも対応できるようにしましょう。SQLの理解を深め、データベース操作のスキルをさらに向上させるために、これらの手法を実践してみてください。