【Oracle】サブクエリ完全ガイド｜スカラー・相関・EXISTS vs IN・インラインビュー・LATERAL・実務パターン

【Oracle】サブクエリ完全ガイド｜スカラー・相関・EXISTS vs IN・インラインビュー・LATERAL・実務パターン Oracle

サブクエリとは SQL のクエリの中に埋め込まれたクエリのことです。Oracle では SELECT・FROM・WHERE・HAVING のどこにでもサブクエリを置くことができ、それぞれで動作と使いどころが異なります。正しく使い分けることで可読性・保守性・パフォーマンスの3つを同時に改善できます。

この記事でわかること

  • スカラーサブクエリ（SELECT句）で行ごとに集計値を付加する方法
  • インラインビュー（FROM句）で一時テーブルとして使う方法
  • WHERE句の IN・EXISTS・ANY・ALL サブクエリの違いと使い分け
  • 相関サブクエリの仕組みと実務パターン（最新行・最大値行の取得）
  • EXISTS と IN のパフォーマンス比較と選択基準
  • LATERAL 句（12c 以降）で相関インラインビューを書く方法
  • WITH 句（CTE）との使い分け
スポンサーリンク

サブクエリの種類と配置場所

配置場所 種類 返す値 主な用途
SELECT 句 スカラーサブクエリ 1行1列のみ 行ごとに集計値・参照値を付加
FROM 句 インラインビュー 複数行・複数列 中間結果を一時テーブルとして扱う
WHERE 句 単一行サブクエリ 1行1列のみ = や < で比較する条件
WHERE 句 複数行サブクエリ 複数行1列 IN・ANY・ALL・EXISTS での絞り込み
WHERE 句 相関サブクエリ 外部クエリの行ごとに評価 最新行・最大値行・存在チェック
FROM 句 LATERAL（12c〜） 複数行・複数列 外部の行を参照するインラインビュー

スカラーサブクエリ：SELECT 句に埋め込む

SELECT 句に置くサブクエリはスカラーサブクエリと呼ばれ、行ごとに1行1列の値を返す必要があります。LEFT JOIN より書きやすい一方、行数が多いと N 回実行されるためパフォーマンスに注意が必要です。

スカラーサブクエリで部門名を付加する
-- 部門名を employees の各行に付加する（スカラーサブクエリ版）
SELECT
    e.employee_id,
    e.last_name,
    e.salary,
    (SELECT d.department_name
     FROM departments d
     WHERE d.department_id = e.department_id) AS dept_name
FROM employees e
ORDER BY e.employee_id;

-- 注意: 部門が存在しない場合は NULL を返す（LEFT JOIN と同じ動作）
-- 部門が複数マッチする場合は ORA-01427: 単一行副問合わせが2行以上返す エラーになる
スカラーサブクエリで集計値を付加する
-- 各社員の給与と部門の平均給与を並べる
SELECT
    e.employee_id,
    e.last_name,
    e.salary,
    ROUND((SELECT AVG(e2.salary)
           FROM employees e2
           WHERE e2.department_id = e.department_id), 0) AS dept_avg_salary,
    e.salary - ROUND((SELECT AVG(e2.salary)
                      FROM employees e2
                      WHERE e2.department_id = e.department_id), 0) AS diff_from_avg
FROM employees e;

-- 同じ処理を分析関数で書くと1回のスキャンで済んで高速（こちらが推奨）
SELECT
    employee_id,
    last_name,
    salary,
    ROUND(AVG(salary) OVER (PARTITION BY department_id), 0) AS dept_avg_salary,
    salary - ROUND(AVG(salary) OVER (PARTITION BY department_id), 0) AS diff_from_avg
FROM employees;
スカラーサブクエリ vs 分析関数
スカラーサブクエリは行数分だけ実行されるため、大きなテーブルではAVG() OVER (PARTITION BY) などの分析関数を使う方が高速です。同じ相関条件のスカラーサブクエリが複数ある場合は特にパフォーマンス差が顕著になります。

インラインビュー（FROM 句のサブクエリ）

FROM 句に置いたサブクエリはインラインビューと呼ばれ、一時的なテーブルとして扱えます。複雑な条件を段階的に処理するのに役立ちます。

インラインビューで ROWNUM を使ったページング
-- ROWNUM を直接 WHERE に使うと ORDER BY 前に付番されてしまう（よくある誤り）
-- NG: ORDER BY 前に ROWNUM が付番されるため、結果は意図しない行になる
SELECT * FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC;

-- OK: インラインビューで先に ORDER BY → 外側で ROWNUM を適用
SELECT *
FROM (
    SELECT employee_id, last_name, salary
    FROM employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

-- ページング（11行目〜20行目を取得）
SELECT *
FROM (
    SELECT rownum AS rn, t.*
    FROM (
        SELECT employee_id, last_name, salary
        FROM employees
        ORDER BY salary DESC
    ) t
    WHERE ROWNUM <= 20        -- 上限を先に絞る
)
WHERE rn >= 11;               -- 下限を外側で絞る

-- Oracle 12c 以降: FETCH FIRST / OFFSET で簡単に書ける
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;   -- 11〜20行目
インラインビューで集計後にさらに絞り込む
-- 部門の平均給与を先に集計し、平均が 7000 以上の部門だけを抽出
SELECT *
FROM (
    SELECT
        d.department_name,
        COUNT(e.employee_id)     AS headcount,
        ROUND(AVG(e.salary), 0)  AS avg_salary,
        SUM(e.salary)            AS total_salary
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    GROUP BY d.department_name
) dept_summary
WHERE avg_salary >= 7000
ORDER BY avg_salary DESC;

-- HAVING で書いても同じだが、インラインビューにすると
-- さらに外側で ORDER BY や JOIN を追加しやすくなる

WHERE 句のサブクエリ：IN・ANY・ALL・EXISTS

単一行サブクエリ（比較演算子 =, >, < と組み合わせ）
-- 全社員の平均給与より高い給与の社員を取得
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

-- 最も給与が高い社員と同じ給与の社員（= サブクエリ）
SELECT employee_id, last_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- 注意: 単一行サブクエリが複数行を返すと ORA-01427 エラー
-- WHERE department_id = (SELECT department_id FROM departments WHERE location_id = 1700)
-- → location_id=1700 に複数の部門があると ORA-01427
複数行サブクエリ：IN・ANY・ALL
-- IN: サブクエリが返す値のどれかに一致する行を取得
SELECT employee_id, last_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 1700
);

-- ANY: サブクエリの結果のいずれか1つと比較が真なら抽出（IN と同じ動作）
-- > ANY = サブクエリの最小値より大きい（ANY で最もよく使うのは < ANY）
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE department_id = 20
);
-- → 部門20の最小給与より高い全社員

-- ALL: サブクエリの全結果との比較が真の行を抽出
-- > ALL = サブクエリの最大値より大きい（ALL で最もよく使うのは > ALL）
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE department_id = 20
);
-- → 部門20の全員より給与が高い社員

EXISTS vs IN のパフォーマンス比較と使い分け

EXISTS：存在確認に特化したサブクエリ
-- EXISTS: サブクエリが1行でも返せば TRUE（残りはスキャンしない）
SELECT e.employee_id, e.last_name
FROM employees e
WHERE EXISTS (
    SELECT 1                          -- SELECT の内容は何でもよい（SELECT 1 が慣習）
    FROM job_history jh
    WHERE jh.employee_id = e.employee_id  -- 外部クエリと相関
);
-- → 転職歴（job_history）がある社員を抽出

-- NOT EXISTS: サブクエリが0行の場合に TRUE
SELECT e.employee_id, e.last_name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM job_history jh
    WHERE jh.employee_id = e.employee_id
);
-- → 転職歴がない社員を抽出
条件 EXISTS が有利 IN が有利
サブクエリ件数 件数が多い（早期終了の恩恵が大きい） 件数が少ない（IN リストが小さい）
NULL の扱い NULL の影響を受けない（EXISTS は TRUE/FALSE のみ） サブクエリに NULL があると NOT IN が全件 FALSE になる（注意）
外部キー結合 相関条件がある場合（EXISTS の典型的な使い方） 定数リストや小テーブルとの照合
インデックス 相関列にインデックスがあると特に高速 IN リストが小さければ十分速い
NOT IN と NULL の罠
NOT IN のサブクエリに NULL が含まれると、結果が0件になります。これは SQL の三値論理（TRUE/FALSE/NULL）の仕様です。NOT IN (1, 2, NULL) は ≠ 1 AND ≠ 2 AND ≠ NULL に展開され、≠ NULL は常に UNKNOWN になるためどの行も条件を満たせません。NOT EXISTS はこの問題が発生しないため、NULL が含まれる可能性があるサブクエリには NOT EXISTS を使ってください。

相関サブクエリ：行ごとに評価するサブクエリ

相関サブクエリは外部クエリの各行を参照するサブクエリで、行ごとに評価されます。「各グループの最新行」「最大値の行」を取得するのに特に便利です。

相関サブクエリの実務パターン（最大値の行・最新行）
-- パターン1: 各部門で最も給与が高い社員を取得（相関サブクエリで最大値比較）
SELECT e.department_id, e.employee_id, e.last_name, e.salary
FROM employees e
WHERE e.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id  -- 外部クエリと相関
)
ORDER BY e.department_id;

-- パターン2: 各社員の最新の転職歴を取得（相関サブクエリで最大日付比較）
SELECT jh.employee_id, jh.job_id, jh.start_date, jh.end_date
FROM job_history jh
WHERE jh.start_date = (
    SELECT MAX(jh2.start_date)
    FROM job_history jh2
    WHERE jh2.employee_id = jh.employee_id
);

-- 同じ処理を ROW_NUMBER() 分析関数で書く方が通常は高速
SELECT employee_id, job_id, start_date, end_date
FROM (
    SELECT jh.*,
           ROW_NUMBER() OVER (PARTITION BY employee_id
                              ORDER BY start_date DESC) AS rn
    FROM job_history jh
)
WHERE rn = 1;

LATERAL 句（Oracle 12c 以降）：相関インラインビュー

LATERAL はインラインビューから外部テーブルの列を参照できるようにする構文です。相関サブクエリをインラインビューとして書けるため、複数列を返す場合や CROSS APPLY（SQL Server 互換）に使えます。

LATERAL で外部テーブルを参照するインラインビュー
-- Oracle 12c 以降: LATERAL 句で外部クエリの列を参照できる
-- 各部門の上位3名の給与を取得
SELECT d.department_name, top3.last_name, top3.salary
FROM departments d,
     LATERAL (
         SELECT last_name, salary
         FROM employees e
         WHERE e.department_id = d.department_id  -- LATERAL なしでは外部参照不可
         ORDER BY salary DESC
         FETCH FIRST 3 ROWS ONLY
     ) top3
ORDER BY d.department_name, top3.salary DESC;

-- CROSS APPLY は LATERAL の別名（Oracle 12c 以降でどちらも使用可）
SELECT d.department_name, top3.last_name, top3.salary
FROM departments d
CROSS APPLY (
    SELECT last_name, salary
    FROM employees e
    WHERE e.department_id = d.department_id
    ORDER BY salary DESC
    FETCH FIRST 3 ROWS ONLY
) top3
ORDER BY d.department_name, top3.salary DESC;

WITH 句（CTE）との使い分け

  • インラインビュー：1回しか参照しない。シンプルな処理に向いている
  • WITH 句（CTE）：同じ結果を複数箇所で参照する。再帰クエリに使う。可読性が高い
  • 相関サブクエリ：行ごとに外部テーブルを参照する必要がある場合。ただし分析関数で代替できることが多い
WITH 句に書き直せる例（可読性向上）
-- インラインビューが深くネストして可読性が低い場合は WITH 句に切り出す
-- Before: ネストしたインラインビュー（読みにくい）
SELECT dept_name, avg_salary, total
FROM (
    SELECT department_name AS dept_name,
           ROUND(AVG(salary), 0) AS avg_salary,
           COUNT(*) AS total
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    GROUP BY department_name
)
WHERE avg_salary > 6000;

-- After: WITH 句で可読性を上げる
WITH dept_stats AS (
    SELECT department_name AS dept_name,
           ROUND(AVG(salary), 0) AS avg_salary,
           COUNT(*) AS total
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    GROUP BY department_name
)
SELECT dept_name, avg_salary, total
FROM dept_stats
WHERE avg_salary > 6000;

まとめ

  • スカラーサブクエリ（SELECT句）：行ごとに1値を付加。件数が多い場合は分析関数に代替
  • インラインビュー（FROM句）：中間結果を一時テーブルとして扱う。ROWNUM ページングに必須
  • IN サブクエリ：小さなリストや定数との照合に。NULL が含まれる NOT IN は結果が0件になるので注意
  • EXISTS / NOT EXISTS：存在確認に特化。NULL の影響を受けず安全。大量データでも早期終了で高速
  • 相関サブクエリ：最新行・最大値行の取得に。分析関数（ROW_NUMBER・MAX OVER）で代替すると高速になることが多い
  • LATERAL（12c〜）：インラインビューから外部列を参照できる。各グループの上位N件取得に便利

サブクエリをより読みやすくまとめる WITH 句の使い方・再帰 CTE はWITH句（共通表式）の使い方完全ガイドを、最新行・ランキング取得に使う分析関数は分析関数（ウィンドウ関数）の使い方完全ガイドも参照してください。