【Oracle】LATERAL・CROSS APPLY・OUTER APPLY完全ガイド｜Oracle 12c 以降の外部参照サブクエリ・グループ内 Top-N まで解説

-- 通常のインラインビュー: 外側テーブルの列を参照できない（エラー）
SELECT d.department_name, e.max_salary
FROM departments d,
     (SELECT MAX(salary) AS max_salary
      FROM employees
      WHERE department_id = d.department_id   -- ORA-00904: d.department_id は参照不可
     ) e;

-- LATERAL を付けると外側テーブル(d)の列を参照できる
SELECT d.department_name, e.max_salary
FROM departments d,
     LATERAL (
         SELECT MAX(salary) AS max_salary
         FROM employees
         WHERE department_id = d.department_id   -- d.department_id を参照できる
     ) e;
-- 各部門の最高給与を1行で取得できる

-- INNER JOIN で LATERAL を使う（同等の書き方）
SELECT d.department_name, e.max_salary
FROM departments d
JOIN LATERAL (
    SELECT MAX(salary) AS max_salary, COUNT(*) AS emp_count
    FROM employees
    WHERE department_id = d.department_id
) e ON (1=1);
-- ON (1=1): LATERAL との結合では結合条件を 1=1（常に真）にする

-- CROSS APPLY: インラインビューが行を返す部門のみ結果に含まれる（INNER JOIN 相当）
SELECT d.department_name, top3.last_name, top3.salary
FROM departments d
CROSS APPLY (
    SELECT last_name, salary
    FROM employees
    WHERE department_id = d.department_id
    ORDER BY salary DESC
    FETCH FIRST 3 ROWS ONLY    -- 各部門の給与上位3名
) top3
ORDER BY d.department_name, top3.salary DESC;
-- 社員が1人もいない部門は結果に含まれない

-- OUTER APPLY: 社員がいない部門は NULL として残る（LEFT OUTER JOIN 相当）
SELECT d.department_name,
       top3.last_name,
       NVL(TO_CHAR(top3.salary), '社員なし') AS salary_info
FROM departments d
OUTER APPLY (
    SELECT last_name, salary
    FROM employees
    WHERE department_id = d.department_id
    ORDER BY salary DESC
    FETCH FIRST 3 ROWS ONLY
) top3
ORDER BY d.department_name, top3.salary DESC NULLS LAST;
-- 社員がいない部門は last_name = NULL, salary = NULL として出力される

-- CROSS APPLY は以下の LATERAL + INNER JOIN と同等
SELECT d.department_name, top3.last_name, top3.salary
FROM departments d
JOIN LATERAL (
    SELECT last_name, salary FROM employees
    WHERE department_id = d.department_id
    ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY
) top3 ON (1=1);

-- 方法 1: CROSS APPLY + FETCH FIRST（Oracle 12c 以降、シンプル）
SELECT d.department_name, e.last_name, e.salary
FROM departments d
CROSS APPLY (
    SELECT last_name, salary
    FROM employees
    WHERE department_id = d.department_id
    ORDER BY salary DESC
    FETCH FIRST 3 ROWS ONLY
) e
ORDER BY d.department_name, e.salary DESC;

-- 方法 2: RANK() OVER を使った従来の方法（全バージョンで使える）
SELECT department_name, last_name, salary
FROM (
    SELECT
        d.department_name,
        e.last_name,
        e.salary,
        RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
)
WHERE salary_rank <= 3
ORDER BY department_name, salary DESC;
-- RANK() は同順位が複数いると3位が複数になる場合がある
-- FETCH FIRST ROWS ONLY の方が直感的で Ties の扱いが明確

-- 方法 3: 相関サブクエリによる従来の方法（記述が複雑）
SELECT d.department_name, e.last_name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE (
    SELECT COUNT(*)
    FROM employees e2
    WHERE e2.department_id = e.department_id
      AND e2.salary > e.salary
) < 3
ORDER BY d.department_name, e.salary DESC;

-- LATERAL で CONNECT BY LEVEL を外部テーブルと組み合わせる（行生成）
-- 各ユーザーの最近 N 日分のデータを生成する
SELECT
    u.user_id,
    u.user_name,
    d.day_offset,
    SYSDATE - d.day_offset AS log_date
FROM users u
CROSS APPLY (
    SELECT LEVEL - 1 AS day_offset
    FROM DUAL
    CONNECT BY LEVEL <= u.report_days   -- u.report_days: ユーザーごとの日数（外部参照）
) d
ORDER BY u.user_id, d.day_offset;
-- 通常の CONNECT BY では外部テーブルの列（report_days）を参照できないが LATERAL で可能に

-- LATERAL と XMLTABLE: 各行の XML 列を個別に展開する
-- （XMLTABLE は内部的に LATERAL と同等の動作をするため直接参照可能）
SELECT p.order_id, x.product_id, x.quantity
FROM purchase_orders p,
     XMLTABLE(
         '$doc/PurchaseOrder/Items/Item'
         PASSING p.xml_data AS "doc"
         COLUMNS
             product_id VARCHAR2(20) PATH 'ProductId',
             quantity   NUMBER       PATH 'Quantity'
     ) x;
-- XMLTABLE は暗黙的に LATERAL と同様に外側テーブルの列を参照できる