はじめに
SQLを学び始めた皆さん。
簡単なCRUD操作ができるようになったのも束の間、複雑なクエリ設計に悩んでいる方も多いのでは？
今回はそんなあなたに捧げる記事です。
どこかで誰かの助けになれば! と、だたただ例題を解く手順を書き殴ります。
例題
以下の記事の例題を拝借します。
解くのは演習14です。
テーブルのデータ
employees
| emp_id | name | department |
|---|---|---|
| 1 | 田中 一郎 | 営業 |
| 2 | 佐藤 花子 | 営業 |
| 3 | 鈴木 太郎 | 開発 |
| 4 | 高橋 美咲 | 開発 |
| 5 | 山田 裕子 | 開発 |
salaries
| emp_id | salary_month | salary |
|---|---|---|
| 1 | 2024-01-01 | 300000 |
| 1 | 2024-02-01 | 320000 |
| 2 | 2024-01-01 | 310000 |
| 3 | 2024-01-01 | 400000 |
| 3 | 2024-02-01 | 420000 |
| 4 | 2024-01-01 | 390000 |
| 4 | 2024-02-01 | 390000 |
| 5 | 2024-01-01 | 360000 |
問題文
各部署において、最も高い給与をもらっている社員の名前とその金額を表示してください。
STEP1: ゴールを明確にする
まずはどういうデータが欲しいのかを把握するところが大事です。
ここを疎かにしていきなりSQLを書き始めると頭が混乱していきます。
社員の名前と金額は問題文に載っているので必須。
"部署ごとに"という条件もあるので部署名もあったほうが親切でしょう。
記事には最終的な取得結果が画像で載ってますが、あえて文で書くとしたらこんな感じ。
ほしいもの:
- 社員の名前
- 部署名
- 給与額
条件:
- 「各部署ごとに」最も高い給与の人だけ
STEP2: 一番外側のクエリから考える
個人的にSQLを組んでいく上で大切なのは段階的に組んでいくことだと思ってます。
いきなり結合やらサブクエリやらを考え始めるとよくわからなくなるので、テーブル1個で取得できる外側のクエリから始めてみましょう。
例題の内容だとemployeesをメインとしてデータを取得するのが見て取れるので、まずはemployeesから取得可能なデータで組めるとこまでを考えます。
SELECT
name,
department
FROM
employees;
👇 結果
田中 一郎 | 営業
佐藤 花子 | 営業
鈴木 太郎 | 開発
高橋 美咲 | 開発
山田 裕子 | 開発
これで社員名と部署名は取得できましたね💡
STEP3: salariesから給与額を結合する
次は、STEP2で取得したデータに給与額の情報を付与したいです。
employeesの情報だけでは完結できないので、salariesからデータを取ってきて結合する必要があります。
emp_idがキーとなってリレーションしているのがわかりますね。
また、salariesに給与額データが存在しない社員データは必要ないのでINNER JOINを使います。
SELECT
e.name,
e.department,
s.salary
FROM
employees e
INNER JOIN
salaries s
ON e.emp_id = s.emp.id;
👇 結果
田中 一郎 | 営業 | 300000
田中 一郎 | 営業 | 320000
佐藤 花子 | 営業 | 310000
鈴木 太郎 | 開発 | 400000
鈴木 太郎 | 開発 | 420000
高橋 美咲 | 開発 | 390000
高橋 美咲 | 開発 | 390000
山田 裕子 | 開発 | 360000
ここまでで全員の社員名、部署名、給与額が取得できました！
この段階でまだ"部署ごとの最高給与額"という条件は考えなくていいです。
STEP4: 条件部分を考える
いよいよ条件部分を作っていきます。
STEP3までで取得した全員のデータを一個一個見ていきながら、
その人が所属する部署の最高給与額と一致するかどうか
を判定していけばOKです。
そこで登場するのが相関サブクエリです。
慣れないとイメージつきにくいかもしれないですが、動き自体はそこまで難しくないので覚えておくと便利です。
👇 参考記事
https://it-biz.online/it-skills/correlated-subqueries/
👇 処理フローイメージ
https://claude.ai/public/artifacts/f7d929ed-6b4f-4dd4-85cb-5feca8f31a2a?fullscreen=false
ループで処理していくわけですね。
一旦サブクエリの中身は置いておいて大枠だけ作っちゃいましょう。
SELECT
e.name,
e.department,
s.salary
FROM
employees e
INNER JOIN
salaries s
ON e.emp_id = s.emp.id
-- 各メンバーのs.salary(給与額)がe.department(所属部署)の最高額と一致するか判定
WHERE s.salary = (
-- ここからが相関サブクエリ
-- e.department(所属部署)の最高額を取得する相関サブクエリが入る
);
STEP5: "各部署の最高給与"を求める部分を別で作る
ここで早まっていきなり相関サブクエリで組み込もうとせずに、単体で実行できる単位から組んでいくことをおすすめします☝️
一旦"営業"部署の中の最高額を取得するクエリを考えます。
SELECT
MAX(s2.salary)
FROM
salaries s2
JOIN
employees e2
ON s2.emp_id = e2.emp_id
WHERE
-- まず"営業"部署決めうちで試す
e2.department = '営業';
👇 結果
320000
ここまででちゃんと動くことを確認しましょう。
ハードコーディングしている営業をループで回ってくるe.departmentに置き換えれば、その人が所属している部署の最高額を求める相関サブクエリになります。
STEP6: 合体
ここまでの実装を合体させて完成です。
SELECT
e.name,
e.department,
s.salary
FROM
employees e
INNER JOIN
salaries s
ON e.emp_id = s.emp.id
-- 各メンバーのs.salary(給与額)がe.department(所属部署)の最高額と一致するか判定
WHERE s.salary = (
SELECT
MAX(s2.salary)
FROM
salaries s2
JOIN
employees e2
ON s2.emp_id = e2.emp_id
WHERE
-- ここで外側のe.departmentを参照しているので相関サブクエリとなる
e2.department = e.department
);
まとめ
- 常にゴールイメージ（欲しいデータの形式や意味）を明確に持つことが最重要
- 外側のクエリから段階的に考えていく
- サブクエリは単独で実行可能な単位から書いていく(最初は具体的な値でOK)
一気に組んでいってエラーが出ているとどこがエラーなのかがわかりにくですが、段階的に組んでいくことでエラー原因の特定もしやすいです。ぜひ参考にしてください🙇