SQL練習室
← SQLガイド

SQLの相関サブクエリとは?外側の行ごとに評価される仕組みと書き換え

相関サブクエリは、内側のSELECTが外側の行の値を参照するサブクエリです。外側の1行ごとに内側の答えが変わるため、「自分の部署の平均より高い」「グループ内で最大」といった、行ごとの基準で比べる問題に向きます。同じ社員表で、仕組みと書き換えを順に確かめます。

例で使う表

employeesは1行が社員1人です。deptは部署、salaryは月給(円)で、総務の渡辺は給与が未入力のNULLです。営業の平均は380000円、開発の平均は450000円、総務の平均は320000円です。開発の大和と石井は最高給与が同額です。ORDER BYを付けた例は行順が決まっています。

employees:社員8人(渡辺のsalaryはNULL)
idnamedeptsalary
1青木営業300000
2北野営業400000
3中央営業440000
4大和開発500000
5石井開発500000
6田中開発350000
7森総務320000
8渡辺総務NULL

相関サブクエリの基本構文

sql
SELECT id, name, salary
FROM employees AS e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees AS e2
  WHERE e2.dept = e1.dept
);

外側の行ごとに内側の値が変わる

内側のWHEREにe2.dept = e1.deptを書くと、内側は外側の社員の部署を参照します。SELECT句に置いたこの形のサブクエリは、社員の行ごとに自分の部署の平均給与を返します。営業の3人は380000円、開発の3人は450000円、総務の2人は320000円です。外側の表と内側の表は同じemployeesなので、e1とe2の別名で区別します。給与NULLの渡辺も部署は総務なので、平均は320000円です。

sql
SELECT
  id,
  name,
  dept,
  salary,
  (
    SELECT AVG(salary)
    FROM employees AS e2
    WHERE e2.dept = e1.dept
  ) AS dept_avg
FROM employees AS e1
ORDER BY id;
実行結果
idnamedeptsalarydept_avg
1青木営業300000380000
2北野営業400000380000
3中央営業440000380000
4大和開発500000450000
5石井開発500000450000
6田中開発350000450000
7森総務320000320000
8渡辺総務NULL320000

自分の部署の平均より高い社員を選ぶ

前の節の値をWHEREの比較に使います。外側の1行ごとに「自分の部署の平均」を求め、salaryと比べます。北野(400000円)と中央(440000円)は営業の平均380000円より高く、大和と石井(500000円)は開発の平均450000円より高いので残ります。森は総務の平均320000円と同額で、渡辺はNULLなので、どちらも外れます。

sql
SELECT id, name, dept, salary
FROM employees AS e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees AS e2
  WHERE e2.dept = e1.dept
)
ORDER BY id;
実行結果
idnamedeptsalary
2北野営業400000
3中央営業440000
4大和開発500000
5石井開発500000

相関を外すと基準が全社で固定される

内側のWHEREがなければ、サブクエリは外側の行に関係なく、常に同じ値を返します。全社員の平均は約401428.57円なので、400000円の北野は外れ、中央・大和・石井の3人になります。前の節の結果と北野の扱いが変わりました。部署ごとの基準で比べたいときは、相関条件が必要です。

sql
SELECT id, name, dept, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
)
ORDER BY id;
実行結果
idnamedeptsalary
3中央営業440000
4大和開発500000
5石井開発500000

グループ内で給与が最大の行を取る

GROUP BYで部署ごとの最大値は求められますが、最大の社員の名前は同じSELECTに出せません。相関サブクエリで部署の最大給与を求め、salary = (...)と比べると、その行全体が取れます。営業は中央、総務は森です。開発は最大が同額の大和と石井の2人とも残ります。渡辺はNULLなのでどの最大とも等しくなりません。

sql
SELECT id, name, dept, salary
FROM employees AS e1
WHERE salary = (
  SELECT MAX(salary)
  FROM employees AS e2
  WHERE e2.dept = e1.dept
)
ORDER BY id;
実行結果
idnamedeptsalary
3中央営業440000
4大和開発500000
5石井開発500000
7森総務320000

JOINとGROUP BYの派生表で書き換える

部署ごとの平均を先にGROUP BYで1回だけ集計し、社員の表とJOINして比べます。結果は相関サブクエリの2節目と同じ4人です。この形は、平均の表をほかの列と並べて表示したいときに便利です。外側の行ごとの評価という考え方が不要で、集計表と社員表の結合として読めます。

sql
SELECT e.id, e.name, e.dept, e.salary
FROM employees AS e
JOIN (
  SELECT dept, AVG(salary) AS dept_avg
  FROM employees
  GROUP BY dept
) AS d
  ON d.dept = e.dept
WHERE e.salary > d.dept_avg
ORDER BY e.id;
実行結果
idnamedeptsalary
2北野営業400000
3中央営業440000
4大和開発500000
5石井開発500000

ウィンドウ関数で書き換える

AVG(salary) OVER (PARTITION BY dept)は、行を減らさずに各行へ部署平均を付けます。それを派生表にして、外側のWHEREでsalaryと比べると、同じ4人が得られます。サブクエリを使わずに平均を各行へ付けられるのが特徴です。ウィンドウ関数の結果はWHEREに直接書けないため、派生表(FROMのサブクエリ)にします。

sql
SELECT id, name, dept, salary
FROM (
  SELECT
    id,
    name,
    dept,
    salary,
    AVG(salary) OVER (PARTITION BY dept) AS dept_avg
  FROM employees
) AS t
WHERE salary > dept_avg
ORDER BY id;
実行結果
idnamedeptsalary
2北野営業400000
3中央営業440000
4大和開発500000
5石井開発500000

相関サブクエリでよくある間違い

別名を付けずに同じ表を使うと相関にならない

外側も内側もemployeesのとき、内側のdept = deptは、どちらも内側の表の列として解決されます。外側との対応にはならず、NULLでない行ではすべて真になるため、全社員の平均と比べる結果になります。外側にe1、内側にe2の別名を付け、e2.dept = e1.deptと書いてください。

sql
SELECT id, name, dept, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE dept = dept
)
ORDER BY id;

スカラーとして使うサブクエリが2行以上返るとエラーになる

SELECT句や比較の右辺に置くサブクエリは、1行1列でなければなりません。たとえば内側のSELECTでAVGを付けずにe2.salaryだけを返すと、同じ部署の社員が複数いる行でエラーになります。0行ならNULLになるだけでエラーにはならないため、複数行を返す可能性を、集約関数やLIMITで確認しておきます。

実際に1問解いてみる

SELECT句の相関サブクエリ

社員ごとに、社員のid、名前、同じ部署の社員数を取得してください。部署の人数は、SELECT句に相関サブクエリを置いてCOUNT(*)で求めます。行順は問いません。

テーブル

読み込み中…

実行結果

関連構文

相関サブクエリについてよくある質問

相関サブクエリは遅いのでしょうか?
一概には言えません。実行方法はPostgreSQLが決めるため、同じ結果のJOINと同等になる場合もあります。件数が多く遅いと感じたら、EXPLAINで確認し、JOIN+GROUP BYやウィンドウ関数への書き換えも比べます。
部署ごとの最大の社員が同額で複数いたらどうなりますか?
salary = (部署の最大)で比べると、同額の社員はすべて残ります。1人だけにしたいときは、同額のときの選び方(idの小さい順など)を決めて、LATERALのLIMIT 1やROW_NUMBERを使います。
相関サブクエリとJOINはどう使い分けますか?
行ごとの基準で絞るだけなら相関サブクエリが読みやすく、基準の値を結果の列にも並べたいならJOINの派生表やウィンドウ関数が向きます。結果が同じになるか、NULLや同額の行で確認してから選びます。
相関条件の列名が外側か内側か分からなくなります。
内側のFROMに同名の列があると、内側の列が優先されます。外側を参照したいときは、外側の表に別名(e1など)を付けて、e1.deptのように修飾してください。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。