テーブル結合 ③ — JOIN に WHERE / ORDER BY / CASE を重ねる

本記事で使うデータ — department と employee と sales

前回までで INNER JOIN・OUTER JOIN・自己結合という結合のバリエーションを学びました。

本記事では、結合した結果にWHERE で絞り込み・ORDER BY で並べ替え・CASE で分類ラベルを付けることを重ね、現場で出てくる集計レポートの形を組み立てます。

題材はdepartment（6 部署）・employee（社員 30 名）に加え、売上明細のsalesテーブル（50 件）を使います。

sales.emp_idはemployee.emp_idを指す外部キーです。

3 つのテーブルを結合し、売上に部署名と担当者名を併記したレポートを作っていきます。

演習に入る前に、本記事で使う 3 つのテーブル — department・employee・sales — の列定義とデータのサンプルを確認しておきます。

① 各テーブルにPRAGMA table_info(表名);を実行して列定義を確認してください。

② 各テーブルにSELECT * FROM 表名 LIMIT 5;を実行して先頭 5 行のデータをプレビューしてください。salesがemp_idでどの社員の売上かを指していることを観察してください。

3 つのテーブルを結合する — JOIN を連ねる

3 つ以上のテーブルを結合するときは、JOIN ... ON ...を続けて連ねるだけです。

FROM sales s JOIN employee e ON s.emp_id = e.emp_id JOIN department d ON e.dept_id = d.dept_idのように、salesとemployeeをemp_idでつなぎ、その結果をさらにemployeeとdepartmentをdept_idでつなぎます。

結合の順序は「明細（sales）を主役にして、そこへ社員と部署の情報を付ける」と読むと自然です。

すべて INNER JOIN なので、3 つのテーブルすべてで対応が取れた行だけが残ります。

本データでは売上のある 12 名は全員いずれかの部署に所属しているため、3 表 INNER JOIN の結果はsalesの 50 行すべてが残ります。

-- 各売上に担当者名と dept_name を併記 (3 表 INNER JOIN)
SELECT s.sale_id, e.name, d.dept_name, s.amount, s.sale_date
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY s.sale_id;

「売上明細に、担当社員の名前と所属部署名を併記したレポートを作りたい」という要件を想定します。（正しく実行できれば解説が表示されます）

① sales（別名s）にemployee（別名e）をemp_idで INNER JOIN し、さらにdepartment（別名d）をdept_idで INNER JOIN してください。

② s.sale_id、e.name、d.dept_name、s.amountの 4 列を取り出してください。

③ s.sale_idの昇順で並べてください。

WHERE で絞り込み、ORDER BY で並べ替える

結合したレポートに対して、WHEREで必要な行だけに絞り込み、ORDER BYで見やすい順序に並べ替えます。

WHERE の条件には、結合したどのテーブルの列でも使えます。

WHERE d.location = 'Tokyo'で「Tokyo にある部署の売上だけ」、WHERE s.amount >= 400000で「高額の売上だけ」のように絞れます。

SQL の句はFROM → JOIN → ON → WHERE → ORDER BYの順に書きます。

WHERE は結合が終わった後の行に対して効き、ORDER BY は最後に並べ替えます。

複数の列で並べたいときはORDER BY 列1 DESC, 列2のようにカンマで区切り、1 列目が同じ値の行は 2 列目で並びます（順序を安定させるための副キー）。

-- Osaka 所在の部署の売上だけ、金額の高い順
SELECT e.name, d.dept_name, s.amount, s.sale_date
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
WHERE d.location = 'Osaka'
ORDER BY s.amount DESC, s.sale_id;

「所在地が Tokyo の部署で、40 万円以上の売上だけを、金額の高い順に並べたレポートを作りたい」という要件を想定します。

① sales（s）・employee（e）・department（d）をemp_idとdept_idで 3 表 INNER JOIN してください。

② d.locationが Tokyo で、かつs.amountが 400000 以上の行に絞り込んでください。

③ e.name・d.dept_name・s.amountの 3 列を取り出し、s.amountの降順で並べてください。金額が同じ行はs.sale_idの昇順を副キーにして順序を安定させてください。

CASE で帯ラベルを付ける — 分類済みレポートに仕上げる

結合・絞り込み・並べ替えに加えて、CASE式で金額帯のラベル列を足すと、人が一目で読める分類済みレポートになります。

CASEは条件を上から順に評価し、最初に真になった分岐の値を返す式で、SELECT の列リストにそのまま並べられます（評価式形式: CASE WHEN 条件 THEN 値 ... ELSE 既定値 END）。

例えば 1 件あたりの売上金額をamount >= 400000なら High、amount >= 200000なら Mid、それ未満なら Low の 3 段に分け、bandという別名の列にします。

ラベルは ASCII 英語の固定値（'High' / 'Mid' / 'Low'）にしておくと、結果が環境に依存せず安定します。

3 表 JOIN・WHERE・ORDER BY・CASE を 1 つのクエリにまとめると、現場で日々作られる集計レポートとほぼ同じ形になります。

-- 例: amount を 2 段階に分ける (300000 以上は Large)
SELECT e.name, d.dept_name, s.amount,
  CASE
    WHEN s.amount >= 300000 THEN 'Large'
    ELSE 'Small'
  END AS size
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Sales'
ORDER BY s.sale_id;

「売上明細に担当者名・部署名・金額帯ラベルを付けて、金額の高い順に並べたレポートを作りたい」という要件を想定します。

① sales（s）・employee（e）・department（d）を 3 表 INNER JOIN してください。

② 評価式形式の CASEで、s.amountが 400000 以上なら 'High'、200000 以上なら 'Mid'、それ未満なら 'Low' となるラベル列をbandという別名で作ってください。

③ e.name・d.dept_name・s.amount・bandの 4 列を取り出し、s.amountの降順、同額はs.sale_idの昇順で並べてください。

「売上のある担当者ごとに売上合計を出し、合計額で Gold / Silver / Bronze の 3 ランクに分けたサマリを作りたい」という要件を想定します。

① sales（s）・employee（e）・department（d）を 3 表 INNER JOIN してください。

② e.nameとd.dept_nameでグループ化し、SUM(s.amount)をtotalという別名で求めてください。

③ 評価式形式の CASEで、SUM(s.amount)が 1500000 以上なら 'Gold'、1000000 以上なら 'Silver'、それ未満なら 'Bronze' となるラベル列をtierという別名で作ってください。

④ e.name・d.dept_name・total・tierの 4 列を取り出し、totalの降順、合計が同じ場合はe.nameの昇順を副キーにして並べてください。

売上のない社員は INNER JOIN で消える

本記事のレポートはすべて INNER JOIN なので、売上が 1 件もない 18 名は結果に現れません。

「全社員を出し、売上ゼロの人は合計 0 で表示する」レポートが必要なら、employeeを主役にしてsalesをLEFT JOINし、SUMが NULL になる行をCOALESCE(SUM(s.amount), 0)で 0 に置き換えます。

「結合の種類を間違えると、現れるべき行が静かに消える」点は、レポートの数字が合わないときに最初に疑うところです。