相関サブクエリ

-- 外側クエリのエイリアスをサブクエリ内で参照します。
SELECT 列名
FROM テーブル AS outer_alias
WHERE 列名 比較演算子 (
    SELECT 集計関数(列名)
    FROM テーブル AS inner_alias
    WHERE inner_alias.グループキー = outer_alias.グループキー
);

-- EXISTS と組み合わせた相関サブクエリ。
SELECT 列名
FROM テーブル AS a
WHERE EXISTS (
    SELECT 1
    FROM 別テーブル AS b
    WHERE b.外部キー = a.主キー
      AND b.条件列 = '条件値'
);

-- 各社員の給与が、自分と同じ部署の平均給与より高い社員を取得します。
SELECT emp.社員名, emp.部署名, emp.給与
FROM 社員 AS emp
WHERE emp.給与 > (
    SELECT AVG(sub.給与)
    FROM 社員 AS sub
    WHERE sub.部署名 = emp.部署名
)
ORDER BY emp.部署名, emp.給与 DESC;

-- 最新注文日が2024年以降の顧客を EXISTS で取得します。
SELECT c.顧客名
FROM 顧客 AS c
WHERE EXISTS (
    SELECT 1
    FROM 注文 AS o
    WHERE o.顧客ID = c.顧客ID
      AND o.注文日 >= '2024-01-01'
);

-- SELECT句で各社員の部署内順位を計算します。
SELECT
    emp.社員名,
    emp.部署名,
    emp.給与,
    (
        SELECT COUNT(*)
        FROM 社員 AS sub
        WHERE sub.部署名 = emp.部署名
          AND sub.給与 > emp.給与
    ) + 1 AS 部署内順位
FROM 社員 AS emp
ORDER BY emp.部署名, 部署内順位;

-- 部署平均より給与が高い社員。
社員名     | 部署名   | 給与
-----------+----------+---------
鈴木 花子  | 技術部   | 620000
山田 太郎  | 営業部   | 480000

-- 部署内順位。
社員名     | 部署名   | 給与    | 部署内順位
-----------+----------+---------+-----------
鈴木 花子  | 技術部   | 620000  | 1
佐藤 三郎  | 技術部   | 390000  | 2
山田 太郎  | 営業部   | 480000  | 1
田中 恵    | 営業部   | 320000  | 2

相関サブクエリの構文は主要なデータベースで共通して使用できます。

-- MySQL・PostgreSQL・SQL Server・Oracle・SQLite 共通
SELECT emp.社員名, emp.部署名, emp.給与
FROM 社員 AS emp
WHERE emp.給与 > (
    SELECT AVG(sub.給与)
    FROM 社員 AS sub
    WHERE sub.部署名 = emp.部署名
)
ORDER BY emp.部署名, emp.給与 DESC;

Oracle ではテーブルエイリアスに『AS』キーワードが使えません。エイリアスを付ける際は『AS』を省略して記述します。

-- Oracle ではテーブルエイリアスに AS を使わない
SELECT emp.社員名, emp.部署名, emp.給与
FROM 社員 emp
WHERE emp.給与 > (
    SELECT AVG(sub.給与)
    FROM 社員 sub
    WHERE sub.部署名 = emp.部署名
)
ORDER BY emp.部署名, emp.給与 DESC;

相関サブクエリは外側クエリの各行に対してサブクエリが1回ずつ実行されます。外側クエリがN行あればサブクエリもN回実行されるため、大量データに対してそのまま使うとパフォーマンスが悪化します。同じ結果はウィンドウ関数（『OVER / PARTITION BY』）や JOIN で書き直せることが多く、大規模テーブルでは書き換えを検討してください。

EXISTS との組み合わせはサブクエリが1行見つかった時点で評価を打ち切るため、IN より効率よく動作します。SELECT句の相関サブクエリは可読性が高い反面、実行コストが大きいため注意が必要です。

サブクエリの基本は『サブクエリ（スカラー・行）』を、EXISTS の使い方は『IN / EXISTS』を参照してください。