サブクエリ ② — FROM 派生表・SELECT 句・CREATE / INSERT SELECT

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

前回はサブクエリを WHERE で使いました。

本記事ではサブクエリを `FROM` 句に置く（派生表）・`SELECT` の列に書く・`CASE` と併用する・サブクエリの結果から新しいテーブルを作る という 4 つの応用を順に押さえます。

題材は社員データ 3 表です。

employee（社員 30 名）・department（部署 6 件）・sales（売上明細 50 件）を使い、部署ごとの集計表を作ったり、社員ごとの売上合計を列に並べたりします。

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

① PRAGMA table_info(...) で 3 テーブルの列定義を確認してください。

② SELECT * FROM 表名 LIMIT 5; で各テーブルの先頭 5 行をプレビューしてください。

FROM 派生表 — サブクエリの結果を 1 つの表として扱う

派生表（derived table）とは、FROM 句にサブクエリを書いて その結果をひとつの仮想テーブルとして扱う 書き方です。

「部署ごとの平均給与」を先に集計表として作り、その表に対してさらに JOIN や WHERE をかけられます。

集計した結果をもう一段絞り込みたいときに便利です。

派生表で重要なルールは 必ず別名を付ける ことです。

FROM (SELECT ...) AS ds のように AS 別名（SQLite では AS 省略も可）を付けないとエラーになります。

付けた別名を通して ds.avg_salary のように派生表の列を参照します。

-- FROM 派生表: 部署ごとの人数と平均給与を集計し department と結合
SELECT d.dept_name, ds.headcount, ds.avg_salary
FROM (
  SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
) AS ds
JOIN department d ON d.dept_id = ds.dept_id
ORDER BY ds.avg_salary DESC;

「部署ごとの平均給与を集計し、平均が 590 万円より高い部署だけを部署名付きで出したい」という要件を想定します。（正しく実行できれば解説が表示されます）

① サブクエリで employee を dept_id ごとに集計し、dept_id と平均給与（別名 avg_salary）を持つ派生表を作ってください。dept_id が NULL の社員は集計対象から外してください。

② 派生表に 別名 を付け、department テーブルと dept_id で結合して部署名を取り出してください。

③ 外側の WHERE で avg_salary が 5900000 より大きい 部署に絞り、avg_salary の降順で並べてください。

SELECT 句のサブクエリ — 列の値として 1 つの値を埋め込む

SELECT の列リストにスカラサブクエリを書くと、行ごとに 1 つの値を計算した列 を追加できます。

(SELECT SUM(sale.amount) FROM sales sale WHERE sale.emp_id = emp.emp_id) を列に置けば、社員ごとに「その社員の売上合計」を 1 列として並べられます。

これは外側の emp.emp_id を参照する相関サブクエリです。

-- SELECT 句のサブクエリ: 社員ごとの売上合計を 1 列として追加
SELECT emp.name,
  COALESCE(
    (SELECT SUM(sale.amount) FROM sales sale WHERE sale.emp_id = emp.emp_id),
    0
  ) AS total_amount
FROM employee emp
ORDER BY emp.emp_id
LIMIT 6;

売上が 1 件もない社員はサブクエリが NULL を返すので、COALESCE(..., 0) で 0 に置き換えると表が読みやすくなります。

SELECT 句のサブクエリも 1 行 1 列 に収まる必要があります。

「全社員の一覧に、その社員の売上合計を 1 列として併記したい。売上がない社員は 0 と表示したい」という要件を想定します。

① employee テーブルに別名 emp を付け、name を取り出してください。

② SELECT の列に、その社員の sales 合計を求めるサブクエリを total_amount という別名で追加してください。sale.emp_id = emp.emp_id で相関させ、合計が NULL になる社員は 0 に置き換えてください。

③ total_amount の 降順、同額なら emp_id の昇順で並べ、先頭 8 行 に絞ってください。

CASE と併用する — サブクエリの値で分岐ラベルを付ける

SELECT 句のサブクエリは CASE の条件にもそのまま書けます。

「売上合計が 150 万以上なら High、0 より大きいなら Mid、それ以外は None」のようなランク列を、サブクエリの値を CASE WHEN で判定して作れます。

同じサブクエリを CASE の各 WHEN で繰り返し書く必要がある点に注意してください（読みやすさを優先するなら、後の章で学ぶ WITH（共通テーブル式） で 1 度だけ書く方法もあります）。

本記事ではサブクエリと CASE を直接組み合わせる基本形を押さえます。

-- 売上件数で社員を分類
SELECT emp.name,
  (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) AS sale_count,
  CASE
    WHEN (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) >= 4 THEN 'Frequent'
    WHEN (SELECT COUNT(*) FROM sales sale WHERE sale.emp_id = emp.emp_id) >= 1 THEN 'Occasional'
    ELSE 'None'
  END AS activity
FROM employee emp
ORDER BY sale_count DESC, emp.emp_id
LIMIT 8;

「全社員に売上合計とランク（High / Mid / None）を併記したい」という要件を想定します。

① employee に別名 emp を付け、name と、その社員の売上合計（NULL は 0 に置換、別名 total_amount）を取り出してください。

② CASE で、売上合計が 1500000 以上なら 'High'、0 より大きく 1500000 未満なら 'Mid'、それ以外（0）は 'None' となるランク列を grade という別名で追加してください。判定にも同じ売上合計のサブクエリを使います。

③ total_amount の降順、同額なら emp_id 昇順で並べ、先頭 8 行 に絞ってください。

CREATE / INSERT SELECT — サブクエリの結果をテーブルにする

CREATE TABLE 新表 AS SELECT ... と書くと、SELECT の結果をそのまま新しいテーブルとして保存 できます（CTAS: Create Table As Select）。

集計結果をスナップショットとして残したいときによく用います。

すでにあるテーブルに行を足したいときは INSERT INTO 既存表 SELECT ... を使い、SELECT した結果を一括で追加します。

これらはテーブルを作る・行を増やす 書き込み操作 です。

本記事では集計用の作業テーブル top_seller を使い切りで作って試します。

何度実行しても同じ結果になるよう、作成前に DROP TABLE IF EXISTS で作業テーブルを片付けてから作り直します。

まとめて INSERT する方が速い

INSERT INTO 表 SELECT ... は対象行を 1 つの文でまとめて挿入 します。

INSERT INTO 表 VALUES (...) を行数ぶん繰り返すより、SQL の解析・インデックス更新・トランザクション処理が 1 回で済むため、入れる行が多いほど速く なります。

別テーブルや集計結果をコピー・移送するときは、1 行ずつ繰り返し INSERT するより INSERT ... SELECT の一括挿入を優先します。

-- 部署別の集計をスナップショット表に保存
DROP TABLE IF EXISTS dept_summary;
CREATE TABLE dept_summary AS
  SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
  FROM employee
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id;

SELECT * FROM dept_summary ORDER BY avg_salary DESC;

「売上合計が 150 万円以上の社員を、社員 ID・名前・売上合計の 3 列で集計テーブルにまとめたい」という要件を想定します。書き込み操作なので、再実行しても壊れないよう作業テーブルを作り直す形にします。

① まず DROP TABLE IF EXISTS top_seller; で作業テーブルがあれば片付けてください。

② employee と sales を結合して社員ごとに売上合計を集計し、合計が 1500000 以上 の社員だけを残す SELECT を書き、その結果から CREATE TABLE top_seller AS SELECT ... で top_seller テーブルを作ってください。列は emp_id・name・total_amount（売上合計）の 3 つにしてください。

③ 最後に SELECT * FROM top_seller ORDER BY total_amount DESC; で中身を確認してください。

「実践 4 の集計テーブルに、売上合計が 90 万円以上 150 万円未満の中堅社員も追加したい」という要件を想定します。INSERT INTO ... SELECT で行を一括追加します。再実行しても壊れないよう、テーブルを作り直してから追加する形にします。

① DROP TABLE IF EXISTS top_seller; で作業テーブルを片付け、続けて実践 4 と同じ CREATE TABLE top_seller AS SELECT ...（150 万以上）でテーブルを作り直してください。

② INSERT INTO top_seller SELECT ... で、売上合計が 900000 以上 1500000 未満 の社員（emp_id・name・売上合計の 3 列）を追加してください。列の並びは top_seller と同じにしてください。

③ SELECT * FROM top_seller ORDER BY total_amount DESC; で全行を確認してください。