はじめに
後輩エンジニアに質問を受けたんだけど、これまでろくに教育をした経験がないこともあり、思ったより説明がたどたどしくなってしまった。
自分自身の頭の整理も兼ねてまとめてみる。
まずJOINって単語は「結ぶ」とか「繋げる」って意味がある。
これだけでも分かるようにJOINとはテーブル同士を繋いで結合させる役割をしている。
そしてこの結合なんだけど、ざっくり２つの種類に大別できる。
- 内部結合
- 外部結合
今回は内部結合のINNER JOIN、外部結合のLEFT JOINについて挙動の違いと使い時を説明していく。
内部結合: INNER JOIN
まずは内部結合から。
一言でいうと、指定したキーが一致するレコードのみを抽出・結合する。
これだけではピンと来ないと思うので、実際にサンプルデータとクエリを見ていこう。
あなたはとあるテレビ番組の制作会社にエンジニアとして勤務している。
主な仕事内容は「お笑い芸人の情報が登録されている社内システム」を使用し、依頼されたデータを出力すること。
ある日、あなたの元にこんな依頼が来た。
コンビで活動している芸人の芸名とコンビ名を出力してほしい、ピン芸人は必要ない
つまりこのようなデータを出力できればいいわけだ。（ちなみにガキ使を例に出す）
コンビを組んでいる芸人の芸名＋コンビ名のセット。
月亭方正はピンで活動いているので出力する必要はなさそう。
| 芸名 | コンビ名 |
|---|---|
| 松本人志 | ダウンタウン |
| 浜田雅功 | ダウンタウン |
| 田中直樹 | ココリコ |
| 遠藤章造 | ココリコ |
サンプルデータ
それでは実際にテーブルを覗いてみよう。
comedians（芸人テーブル）
| id | comedian_groups_id | name |
|---|---|---|
| 1 | 1 | 松本人志 |
| 2 | 1 | 浜田雅功 |
| 3 | NULL | 月亭方正 |
| 4 | 2 | 田中直樹 |
| 5 | 2 | 遠藤章造 |
comedian_groupes（コンビテーブル）
| id | name |
|---|---|
| 1 | ダウンタウン |
| 2 | ココリコ |
| 3 | ライセンス |
芸人テーブルには芸名（name）とコンビテーブルのレコードを特定するための外部キー（comedian_groups_id）がある。
コンビテーブルにはコンビ名（name）が登録されている。
ピン芸人にコンビ名がないから、コンビテーブルには対応するデータが存在しない。
つまり、「コンビ名が登録されている芸人のデータだけ」出力すれば目的のデータを出力できそうと分かる。
INNER JOINを試す
それではINNER JOINでテーブルを結合してみよう。
# 基本文法
# SELECT * FROM テーブルA INNER JOIN テーブルB ON テーブルA.カラム名 = テーブルB.カラム名;
SELECT * FROM comedians INNER JOIN comedian_groups ON comedians.comedian_groups_id = comedian_groups.id;
結果はこうなる。
| id | comedian_groups_id | name | id | name |
|---|---|---|---|---|
| 1 | 1 | 松本人志 | 1 | ダウンタウン |
| 2 | 1 | 浜田雅功 | 1 | ダウンタウン |
| 4 | 2 | 田中直樹 | 2 | ココリコ |
| 5 | 2 | 遠藤章造 | 2 | ココリコ |
何が起きたかというと、テーブルA.カラム名とテーブルB.カラム名が一致するレコードだけを結合して出力している。
そのため、ピン芸人の月亭方正には対応するコンビテーブルのレコードが存在しないため、出力からは除外されている。
このように、キーとなるカラム同士のレコードのみを出力するのがINNER JOINだ。
今回のようにキーが不一致のデータは出力する必要がない場合はINNER JOINを使用する。
ここまで読めば冒頭の指定したキーが一致するレコードのみを抽出・結合するの意味がわかってもらえると思う。
不要なカラムを削除した最終的なクエリは以下の通りとなる。（ちなみにINNER JOINは単にJOINとも書ける）
SELECT
comedians.name AS '芸名',
comedian_groups.name AS 'コンビ名'
FROM comedians
INNER JOIN comedian_groups ON comedians.comedian_groups_id = comedian_groups.id;
外部結合: LEFT JOIN
これを一言でいうと、左側のテーブルのレコードを全て保持しつつ、指定したキーが一致するレコードを抽出・結合する。
ではまた実際にクエリを見ていこう。
別の日、あなたの元にこんな依頼が来た。
芸名とコンビ名をセットで出力してほしい、ピン芸人は芸名だけでいいよ
つまり今度はこのようなデータがほしいわけだ。
コンビ芸人はさっきと同じように芸名＋コンビ名のセット。
変わった部分はピン芸人の月亭方正のレコードが増えている点だ。
| 芸名 | コンビ名 |
|---|---|
| 松本人志 | ダウンタウン |
| 浜田雅功 | ダウンタウン |
| 月亭方正 | |
| 田中直樹 | ココリコ |
| 遠藤章造 | ココリコ |
さて、どのようなクエリを実行するべきだろうか。
思い出してほしいのは、INNER JOINでは月亭方正のデータは出力されなかったというところだ。
今回は全芸人の芸名は全て出力しないといけない。
LEFT JOINを試す
こんな時に使うのがLEFT JOINだ。
実際にLEFT JOINを使用してテーブルを結合してみよう。
# 基本文法
# SELECT * FROM テーブルA LEFT JOIN テーブルB ON テーブルA.カラム名 = テーブルB.カラム名;
SELECT * FROM comedians LEFT JOIN comedian_groups ON comedians.comedian_groups_id = comedian_groups.id;
結果はこうなる。
| id | comedian_groups_id | name | id | name |
|---|---|---|---|---|
| 1 | 1 | 松本人志 | 1 | ダウンタウン |
| 2 | 1 | 浜田雅功 | 1 | ダウンタウン |
| 3 | NULL | 月亭方正 | NULL | NULL |
| 4 | 2 | 田中直樹 | 2 | ココリコ |
| 5 | 2 | 遠藤章造 | 2 | ココリコ |
INNER JOINと結果が変わり、月亭方正のレコードも出力されている。
説明していくと、まずLEFT JOINのLEFTとはJOINの左側に書かれたテーブルを指している。
つまりテーブルAの芸人テーブル（comedians）のことだ。
LEFT JOINを使うと、JOINの左側に書かれたテーブルのデータは全て保持される。
comedians LEFT JOINとなっているので、今回のクエリだと芸人テーブルのデータが全て保持された状態になる。
その上で、指定したキー同士が一致するレコードは結合され、一致しないレコードに関してはNULLが入る。
そのため、コンビ芸人はINNER JOINと同様に芸名＋コンビのレコードが紐づけられる。
ピン芸人の月亭方正に関しても芸名はしっかりと保持・出力されるが、対応するコンビテーブルのデータが無いため、そこにはNULLが入るといった具合だ。
LEFT JOINは基準となるテーブルのデータは全て保持し、その上で関連する別テーブルのデータを追加したい時に使用する。
こちらも依頼に対応するための最終的なクエリを書いておこう。
SELECT
comedians.name '芸名',
CASE
# NULLのときは代わりに空の文字列を出力したほうがきれい
WHEN comedian_groups.name IS NULL THEN ''
ELSE comedian_groups.name
END 'コンビ名'
FROM comedians
LEFT JOIN comedian_groups ON comedians.comedian_groups_id = comedian_groups.id;
まとめ
今はINNER JOINとLEFT JOINの説明にとどまっているけど、また後で別のJOIN句についての説明と使い時も追記しようと思っている。
このサイトでExecuteをクリックすると、今回使用したクエリを実際に実行できる。
自分でデータを追加したり、クエリを変更したりして遊んでみてほしい。
SQL Fiddle
ディスカッション