テーブル結合 ① — INNER JOIN と別名

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

ここまでは 1 つのテーブルだけを扱ってきましたが、現実のデータは 複数のテーブルに分けて保存 されています。社員の名前や給料は employee テーブルに、部署の名前や所在地は department テーブルに、というように 役割ごとに分割 し、employee.dept_id で department を指し示す形で関連付けるのが一般的な設計です（この「他テーブルの行を指す列」を 外部キー と呼びます）。

本記事では、分かれた 2 つのテーブルを 1 つの結果にまとめる JOIN（結合） の基本である INNER JOIN を学びます。題材は department テーブル（6 部署）と employee テーブル（社員 30 名）です。社員に部署名を併記する一覧表を作る演習を通して、テーブル別名・ON・USING の書き方を順に試します。

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

① PRAGMA table_info(department); と PRAGMA table_info(employee); で両テーブルの列定義を確認してください。

② SELECT * FROM department LIMIT 5; と SELECT * FROM employee LIMIT 5; で先頭 5 行のデータをプレビューしてください。employee の dept_id 列に NULL が入る行があることを観察してください。

INNER JOIN — 2 つのテーブルを結合条件でつなぐ

employee テーブルには dept_id（部署番号）しか入っていません。社員一覧に 部署名 も併記したいときは、employee.dept_id と department.dept_id が 一致する行どうしを横につなげて 1 行にまとめます。これが JOIN（結合） で、もっとも基本的なのが INNER JOIN です（INNER = 内部結合）。

書き方は SELECT 列 FROM 左テーブル JOIN 右テーブル ON 結合条件 です。JOIN は INNER JOIN の略で、両方とも同じ動作をします。ON には「どの列とどの列が一致したら同じ行とみなすか」という 結合条件 を書きます。INNER JOIN は 両テーブルで条件が一致した行だけ を残し、片方にしか相手がいない行は結果から消えます。

-- 各社員に dept_name を併記する (INNER JOIN)
SELECT employee.name, employee.salary, department.dept_name
FROM employee
JOIN department
  ON employee.dept_id = department.dept_id;

「社員一覧に、社員番号・名前・所属部署名を並べた表を作りたい」という要件を想定します。（正しく実行できれば解説が表示されます）

① employee テーブルと department テーブルを INNER JOIN してください。結合条件は両テーブルの dept_id が一致することです。

② employee の emp_id・name と、department の dept_name の 3 列を取り出してください。

③ 結果は emp_id の昇順で並べてください。

テーブル別名 — 長いテーブル名を短く書く

employee.name や department.dept_name のように毎回テーブル名を書くと、クエリが長くなります。FROM employee e のようにテーブル名の後ろに 別名（エイリアス） を付けると、以降は e.name のように短い名前で参照できます。AS を付けて FROM employee AS e と書いても同じです。

別名を付けると、同じテーブルを 2 回使う 自己結合（次の記事で扱います）や、サブクエリで結果に名前を付けるときにも必要になります。本記事では employee を e、department を d として書き進めます。

-- employee を e、department を d と別名にする
SELECT e.name, e.city, d.dept_name, d.location
FROM employee e
JOIN department d
  ON e.dept_id = d.dept_id;

「Engineering 部署に所属する社員の名前と、その部署の所在地だけを表示したい」という要件を想定します。

① employee に別名 e、department に別名 d を付けて INNER JOIN してください。結合条件は dept_id の一致です。

② dept_name が Engineering の行だけに絞ってください。

③ e の name と d の location の 2 列を取り出し、name の昇順で並べてください。

USING — 結合する列名が同じときの短縮形

employee と department は どちらも `dept_id` という同じ列名 で結合しています。このように 結合に使う列の名前が両テーブルで完全に一致 している場合は、ON e.dept_id = d.dept_id の代わりに USING (dept_id) と書けます。

USING (列名) は ON 左.列名 = 右.列名 と同じ意味で、結合列を 1 つだけ書けば済むので短くなります。さらに USING で結合した列は 結果に 1 回だけ 現れ、SELECT dept_id（テーブル名なし）でそのまま参照できます。列名が違うとき（例: e.dept_id と d.id）は USING は使えず、ON を使います。

-- ON e.dept_id = d.dept_id と同じ意味を USING で書く
SELECT e.name, dept_id, d.dept_name
FROM employee e
JOIN department d
  USING (dept_id);

「全社員の中から給料の高い順に上位 5 名を取り出し、名前・部署名・給料を一覧にしたい」という要件を想定します。

① employee（別名 e）と department（別名 d）を `USING` 句 で結合してください。結合列は両テーブル共通の dept_id です。

② e の name、d の dept_name、e の salary の 3 列を取り出してください。

③ salary の降順で並べ、先頭 5 行に絞ってください。

INNER JOIN は片方にしかない行を落とす

INNER JOIN は 両テーブルで結合条件が一致した行だけ を返します。dept_id が NULL の 4 名（Ivan・Quinn・Xander・Brian）と、社員が 1 人もいない Legal 部署は、結合相手がいないため結果に現れません。「全社員を出したいのに 26 行しか返らない」のは多くの場合この挙動が原因です。片方にしかない行も残したい ときは、次の記事で学ぶ OUTER JOIN を使います。