SQLの相関サブクエリとは?外側の行ごとに評価される仕組みと書き換え
相関サブクエリは、内側のSELECTが外側の行の値を参照するサブクエリです。外側の1行ごとに内側の答えが変わるため、「自分の部署の平均より高い」「グループ内で最大」といった、行ごとの基準で比べる問題に向きます。同じ社員表で、仕組みと書き換えを順に確かめます。
例で使う表
employeesは1行が社員1人です。deptは部署、salaryは月給(円)で、総務の渡辺は給与が未入力のNULLです。営業の平均は380000円、開発の平均は450000円、総務の平均は320000円です。開発の大和と石井は最高給与が同額です。ORDER BYを付けた例は行順が決まっています。
| id | name | dept | salary |
|---|---|---|---|
| 1 | 青木 | 営業 | 300000 |
| 2 | 北野 | 営業 | 400000 |
| 3 | 中央 | 営業 | 440000 |
| 4 | 大和 | 開発 | 500000 |
| 5 | 石井 | 開発 | 500000 |
| 6 | 田中 | 開発 | 350000 |
| 7 | 森 | 総務 | 320000 |
| 8 | 渡辺 | 総務 | NULL |
相関サブクエリの基本構文
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円です。
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;| id | name | dept | salary | dept_avg |
|---|---|---|---|---|
| 1 | 青木 | 営業 | 300000 | 380000 |
| 2 | 北野 | 営業 | 400000 | 380000 |
| 3 | 中央 | 営業 | 440000 | 380000 |
| 4 | 大和 | 開発 | 500000 | 450000 |
| 5 | 石井 | 開発 | 500000 | 450000 |
| 6 | 田中 | 開発 | 350000 | 450000 |
| 7 | 森 | 総務 | 320000 | 320000 |
| 8 | 渡辺 | 総務 | NULL | 320000 |
自分の部署の平均より高い社員を選ぶ
前の節の値をWHEREの比較に使います。外側の1行ごとに「自分の部署の平均」を求め、salaryと比べます。北野(400000円)と中央(440000円)は営業の平均380000円より高く、大和と石井(500000円)は開発の平均450000円より高いので残ります。森は総務の平均320000円と同額で、渡辺はNULLなので、どちらも外れます。
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;| id | name | dept | salary |
|---|---|---|---|
| 2 | 北野 | 営業 | 400000 |
| 3 | 中央 | 営業 | 440000 |
| 4 | 大和 | 開発 | 500000 |
| 5 | 石井 | 開発 | 500000 |
相関を外すと基準が全社で固定される
内側のWHEREがなければ、サブクエリは外側の行に関係なく、常に同じ値を返します。全社員の平均は約401428.57円なので、400000円の北野は外れ、中央・大和・石井の3人になります。前の節の結果と北野の扱いが変わりました。部署ごとの基準で比べたいときは、相関条件が必要です。
SELECT id, name, dept, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
ORDER BY id;| id | name | dept | salary |
|---|---|---|---|
| 3 | 中央 | 営業 | 440000 |
| 4 | 大和 | 開発 | 500000 |
| 5 | 石井 | 開発 | 500000 |
グループ内で給与が最大の行を取る
GROUP BYで部署ごとの最大値は求められますが、最大の社員の名前は同じSELECTに出せません。相関サブクエリで部署の最大給与を求め、salary = (...)と比べると、その行全体が取れます。営業は中央、総務は森です。開発は最大が同額の大和と石井の2人とも残ります。渡辺はNULLなのでどの最大とも等しくなりません。
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;| id | name | dept | salary |
|---|---|---|---|
| 3 | 中央 | 営業 | 440000 |
| 4 | 大和 | 開発 | 500000 |
| 5 | 石井 | 開発 | 500000 |
| 7 | 森 | 総務 | 320000 |
JOINとGROUP BYの派生表で書き換える
部署ごとの平均を先にGROUP BYで1回だけ集計し、社員の表とJOINして比べます。結果は相関サブクエリの2節目と同じ4人です。この形は、平均の表をほかの列と並べて表示したいときに便利です。外側の行ごとの評価という考え方が不要で、集計表と社員表の結合として読めます。
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;| id | name | dept | salary |
|---|---|---|---|
| 2 | 北野 | 営業 | 400000 |
| 3 | 中央 | 営業 | 440000 |
| 4 | 大和 | 開発 | 500000 |
| 5 | 石井 | 開発 | 500000 |
ウィンドウ関数で書き換える
AVG(salary) OVER (PARTITION BY dept)は、行を減らさずに各行へ部署平均を付けます。それを派生表にして、外側のWHEREでsalaryと比べると、同じ4人が得られます。サブクエリを使わずに平均を各行へ付けられるのが特徴です。ウィンドウ関数の結果はWHEREに直接書けないため、派生表(FROMのサブクエリ)にします。
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;| id | name | dept | salary |
|---|---|---|---|
| 2 | 北野 | 営業 | 400000 |
| 3 | 中央 | 営業 | 440000 |
| 4 | 大和 | 開発 | 500000 |
| 5 | 石井 | 開発 | 500000 |
相関サブクエリでよくある間違い
別名を付けずに同じ表を使うと相関にならない
外側も内側もemployeesのとき、内側のdept = deptは、どちらも内側の表の列として解決されます。外側との対応にはならず、NULLでない行ではすべて真になるため、全社員の平均と比べる結果になります。外側にe1、内側にe2の別名を付け、e2.dept = e1.deptと書いてください。
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:スカラーサブクエリ — 外側の変数を参照するスカラーサブクエリと、州ごとの最大人口を求める例
- PostgreSQL公式:EXISTSと外側の変数 — 外側の変数が1回の評価中は定数になること
- PostgreSQL公式:LATERALサブクエリ — FROM句で前の表の列を参照するLATERAL
- PostgreSQL公式:ウィンドウ関数 — PARTITION BYで行を減らさずに集計する方法