3.6 テーブル結合の考え方
データから情報を読み取るとき, 多くの場合で一つのテーブルでは完結しません. そこで, 複数のテーブを結合する必要が生じてきます. しかし, 結合と一口に言っても, 複数のやり方があります. まずは, 内部結合について説明します.
SQL では, まず結合する2つのテーブルについて, 全てのレコードの組み合わせを用意します. これは, INNER JOIN 句の代わりに CROSS JOIN 句を用いることで出力できます. 結合条件が存在しないため, ON 句は不要です. これはいわゆるデカルト集合 (直積集合)です (表 3.5).
| emp.id | emp.name | department_id | dep.id | dep.name |
|---|---|---|---|---|
| 1 | アンドレイ・コルモゴロフ | 1 | 1 | 総務部 |
| 2 | ロナルド・フィッシャー | 1 | 1 | 総務部 |
| 3 | ピエール・ラプラス | 2 | 1 | 総務部 |
| 4 | ルドルフ・カルマン | 3 | 1 | 総務部 |
| 5 | 赤池 弘次 | 4 | 1 | 総務部 |
| 6 | 林 知己夫 | 4 | 1 | 総務部 |
| 7 | 伊藤 清 | NULL | 1 | 総務部 |
| 1 | アンドレイ・コルモゴロフ | 1 | 2 | 人事部 |
| 2 | ロナルド・フィッシャー | 1 | 2 | 人事部 |
| 3 | ピエール・ラプラス | 2 | 2 | 人事部 |
| 4 | ルドルフ・カルマン | 3 | 2 | 人事部 |
| 5 | 赤池 弘次 | 4 | 2 | 人事部 |
| 6 | 林 知己夫 | 4 | 2 | 人事部 |
| 7 | 伊藤 清 | NULL | 2 | 人事部 |
この組み合わせから一定の条件にあうレコードを取り出せば, 内部結合と同じ結果が得られます. 先ほどの内部結合の例では, employees テーブルの department_id 列と department テーブルの id 列で紐付けています. この条件を指定するのが ON 句です. ON 句で結合の条件に使用した列を, 結合キーと呼びます. 上記では, これらが一致していない行はグレーで塗りつぶしています. 以上からさきほどの内部結合とは,
- 2つのテーブルのレコードのデカルト集合を作成する
employees.department_idとdepartments.idが等しいレコードだけを抜き出すemployees.id,employees.name,departments.name列だけを取りだす
という操作を順に行っている事になります8. また ON 句による条件指定だけでなく, その後に WHERE 句を書くことで従来のような条件抽出をすることもできます. 例えば, 結合した上で, 年齢が30歳以上の社員だけを出力したい場合,
SELECT t1.id, t1.name, t2.name as department
FROM table t1
LEFT INNER JOIN
table2 t2
ON t1.id = t2.department_id
WHERE age >= 30と書けます. GROUP BY, ORDER BY 句も利用可能です.
ここまでの説明の範囲では, ON 句と WHERE 句の機能に本質的な違いがありません. つまり,
SELECT t1.id, t1.name, t2.name AS department
FROM <DB>.employees t1
INNER JOIN <DB>.departments t2
ON 1=1
WHERE t1.department_id = t2.id and age >= 30と書いても結果は同じです (内部結合する場合 ON 句を必ず書く必要があるため, どのレコードでも必ず条件が真になる 1=1 という条件を指定しています). しかし, 内部結合と外部結合について考えた場合, ON と WHERE に違いが発生します9.