相関サブクエリ

外側クエリのエイリアスをサブクエリ内で参照します。

SELECT column_name
FROM table_name AS outer_alias
WHERE column_name operator (
    SELECT aggregate_function(column_name)
    FROM table_name AS inner_alias
    WHERE inner_alias.group_key = outer_alias.group_key
);

EXISTS と組み合わせた相関サブクエリです。

SELECT column_name
FROM table_name AS a
WHERE EXISTS (
    SELECT 1
    FROM other_table AS b
    WHERE b.foreign_key = a.primary_key
      AND b.condition_col = 'value'
);

使い方	概要
WHERE句の相関サブクエリ	外側の各行に対してサブクエリを評価し、条件を満たす行を返します。
SELECT句の相関サブクエリ	各行に対して集計値などを動的に計算して列として追加します。
EXISTS との組み合わせ	関連するデータが存在するかを行ごとに確認します。最も多い使い方です。

以下の『members』テーブルを例に説明します。

各メンバーのスコアが、自分の役職の平均より高いメンバーを取得します。

sample_correlated_subquery.sql

SELECT m.name, m.role, m.score
FROM members AS m
WHERE m.score > (
    SELECT AVG(sub.score)
    FROM members AS sub
    WHERE sub.role = m.role
)
ORDER BY m.role, m.score DESC;

+----------+--------+-------+
| name     | role   | score |
+----------+--------+-------+
| 狡噛慎也 | 執行官 |   480 |
| 常守朱   | 監視官 |   620 |
+----------+--------+-------+
2 rows in set

同じ役職に自分よりスコアが高いメンバーが存在するメンバーを EXISTS で取得します。『SELECT 1』の『1』に特別な意味はなく、行が存在するかどうかだけを確認するための書き方です。

sample_correlated_subquery.sql

SELECT m.name, m.role, m.score
FROM members AS m
WHERE EXISTS (
    SELECT 1
    FROM members AS sub
    WHERE sub.role = m.role
      AND sub.score > m.score
);

+------------+--------+-------+
| name       | role   | score |
+------------+--------+-------+
| 征陸智己   | 執行官 |   320 |
| 宜野座伸元 | 監視官 |   390 |
+------------+--------+-------+
2 rows in set

SELECT句で各メンバーの役職内順位を計算します。

sample_correlated_subquery.sql

SELECT
    m.name,
    m.role,
    m.score,
    (
        SELECT COUNT(*)
        FROM members AS sub
        WHERE sub.role = m.role
          AND sub.score > m.score
    ) + 1 AS rank_in_role
FROM members AS m
ORDER BY m.role, rank_in_role;

+------------+--------+-------+--------------+
| name       | role   | score | rank_in_role |
+------------+--------+-------+--------------+
| 狡噛慎也   | 執行官 |   480 |            1 |
| 征陸智己   | 執行官 |   320 |            2 |
| 常守朱     | 監視官 |   620 |            1 |
| 宜野座伸元 | 監視官 |   390 |            2 |
+------------+--------+-------+--------------+
4 rows in set

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

SELECT m.name, m.role, m.score
FROM members AS m
WHERE m.score > (
    SELECT AVG(sub.score)
    FROM members AS sub
    WHERE sub.role = m.role
)
ORDER BY m.role, m.score DESC;

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

SELECT m.name, m.role, m.score
FROM members m
WHERE m.score > (
    SELECT AVG(sub.score)
    FROM members sub
    WHERE sub.role = m.role
)
ORDER BY m.role, m.score DESC;

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

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

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