クエリの組み立て — JOIN・サブクエリ・UNION を組み合わせる

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

JOIN（複数表をつなぐ）・サブクエリ（クエリの中のクエリ）・UNION（結果を縦にまとめる）は、それぞれ単独でも使えますが、WHERE / ORDER BY / LIMIT と組み合わせると実務的な問い合わせが書けます。

この記事では、これらの書き方を組み合わせて 1 つのクエリに育てる手順を順番に見ていきます。

題材は社員データ 3 表 — employee（社員 30 名）・department（部署 6 件）・sales（売上明細 50 件）です。

簡単な JOIN から始めて、サブクエリ、UNION と段階的に積み上げます。

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

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

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

句の評価順を押さえる — JOIN・WHERE・ORDER BY・LIMIT

クエリは書く順番（SELECT … FROM … WHERE … ORDER BY）と、評価される順番が違います。

データベースはまず FROM / JOIN で表をつなぎ、次に WHERE で行を絞り、GROUP BY / HAVING で集計し、SELECT で列を作り、最後に ORDER BY で並べて LIMIT で件数を絞ります。

この順番を押さえると、「集計結果での絞り込みは WHERE ではなく HAVING」「LIMIT は並べ替えのあとに効く」といった決まりが腑に落ちます。

-- JOIN + WHERE + ORDER BY: Osaka にある部署の社員を入社日順に
SELECT emp.name, dept.dept_name, emp.hired_on
FROM employee emp
JOIN department dept ON dept.dept_id = emp.dept_id
WHERE dept.location = 'Osaka'
ORDER BY emp.hired_on;

JOIN・WHERE・ORDER BY・LIMIT を 1 つのクエリで組み合わせます。（正しく実行できれば解説が表示されます）

① employee と department を dept_id で内部結合し、社員名・部署名・給与を取り出してください。

② 部署の所在地（department.location）が Tokyo の社員だけに絞ってください。

③ 給与の高い順に並べ、上位 5 名だけを取り出してください。

サブクエリで基準値を作り、JOIN 結果を絞る

サブクエリは、JOIN で組み立てた結果をさらに絞るための「基準値」を作るのに使えます。

「自分の部署の平均給与」のように社員ごとに変わる基準は、外側の行を参照する 相関サブクエリ（社員 1 人ごとに計算し直すサブクエリ）で求めます。

JOIN で部署名を付けながら、相関サブクエリの条件で行を絞ると、複数の文法が 1 つのクエリで噛み合います。

-- JOIN + 相関サブクエリ: 自分の部署の最高給与と同額の社員（部署内トップ）
SELECT emp.name, dept.dept_name, emp.salary
FROM employee emp
JOIN department dept ON dept.dept_id = emp.dept_id
WHERE emp.salary = (
  SELECT MAX(dept_member.salary)
  FROM employee dept_member
  WHERE dept_member.dept_id = emp.dept_id
);

今度はサブクエリを重ねます。各部署の中で「自分の部署の平均給与より高い社員」を、部署名つきで一覧にします。

① employee と department を結合し、社員名・部署名・給与を取り出してください。

② その社員と同じ部署の平均給与を相関サブクエリで求め、salary がそれより大きい社員だけに絞ってください。

③ dept_id の昇順、同じ部署の中では給与の高い順に並べてください。

UNION で 2 つの観点をまとめ、ORDER BY で仕上げる

UNION は 2 つの SELECT の結果を縦にまとめます。

上下の SELECT は union 互換（列数が同じで、対応する列の型が両立する）である必要があります。

UNION は完全に同じ行を 1 つにまとめ（重複排除）、UNION ALL は重複をそのまま残します。

並び替えや件数制限は結合後の結果全体に効くので、ORDER BY / LIMIT はいちばん最後に 1 つだけ書きます。

-- UNION ALL: 2 つの観点を区分ラベル付きで縦に積む（重複保持）
SELECT name, 'Kyoto' AS via FROM employee WHERE city = 'Kyoto'
UNION ALL
SELECT emp.name, 'HighSales' AS via
FROM employee emp
JOIN sales sale ON sale.emp_id = emp.emp_id
GROUP BY emp.emp_id
HAVING SUM(sale.amount) >= 1500000
ORDER BY name;

最後に UNION で 2 つの観点を 1 つの名簿にまとめます。

① 「Kyoto 在住の社員」の名前を取り出す SELECT を書いてください。

② 「売上合計が 150 万円以上の社員」を、employee と sales の結合＋集計（GROUP BY / HAVING）で求める SELECT を書いてください。

③ 2 つを UNION でつないで重複を 1 行にまとめ、名前の昇順で並べてください。