昨日、結合(join)について理解しきれないままだったので続きを調べました。
結合(join)の種類
以下の5種類があります。
- INNER JOIN (内部結合)
- LEFT (OUTER) JOIN (左外部結合)
- RIGHT (OUTER) JOIN (右外部結合)
- FULL (OUTER) JOIN (完全外部結合)
- CROSS JOIN (クロス結合)
※内部結合:両方のテーブルで対応するデータが存在するものだけを表示します。
※外部結合:どちらかのテーブルにしか存在しないデータについても表示します。
前提
以下のようなpetsとeventsという2つのテーブルがあるとします。
nameカラムはどちらもpetsの名前を指しているものとします。(外部キーがわりです)
pets
id |
name |
owner |
species |
gender |
birth |
1 |
Whistler |
Gwen |
bird |
f |
1989-08-30 |
2 |
Puffball |
Diane |
hamster |
f |
1989-08-31 |
3 |
Bowser |
Diane |
dog |
m |
1989-08-30 |
4 |
Momiji |
Hayashi |
dog |
m |
2003-08-12 |
5 |
Buffy |
Nana |
dog |
f |
1991-10-10 |
6 |
chee |
yoshino |
fish |
f |
1994-08-01 |
events
id |
name |
date |
description |
1 |
Whistler |
1999-11-09 |
Went hiking. |
2 |
Puffball |
1999-11-08 |
Chased |
3 |
Bowser |
1999-11-07 |
Go to park. |
4 |
Momiji |
2003-10-20 |
Came to the Hayashi family. |
5 |
Buffy |
2000-10-30 |
Climbed to the mountain. |
6 |
jassmine |
2005-10-10 |
Studied. |
INNER JOIN (内部結合)
どちらのテーブルにも存在するレコードのみで結合したテーブルを作ります。
SELECT * FROM pets INNER JOIN events ON pets.name = events.name;
id |
name |
owner |
species |
gender |
birth |
id |
name |
date |
description |
1 |
Whistler |
Gwen |
bird |
f |
1989-08-30 |
1 |
Whistler |
1999-11-09 |
Went hiking. |
2 |
Puffball |
Diane |
hamster |
f |
1989-08-31 |
2 |
Puffball |
1999-11-08 |
Chased |
3 |
Bowser |
Diane |
dog |
m |
1989-08-30 |
3 |
Bowser |
1999-11-07 |
Go to park. |
4 |
Momiji |
Hayashi |
dog |
m |
2003-08-12 |
4 |
Momiji |
2003-10-20 |
Came to the Hayashi family. |
5 |
Buffy |
Nana |
dog |
f |
1991-10-10 |
5 |
Buffy |
2000-10-30 |
Climbed to the mountain. |
LEFT (OUTER) JOIN (左外部結合)
petsテーブルに存在するレコード基準で結合テーブルを作ります。
eventsテーブルのレコードがない場合にはNULLとなります。
SELECT * FROM pets LEFT OUTER JOIN events ON pets.name = events.name;
id |
name |
owner |
species |
gender |
birth |
id |
name |
date |
description |
1 |
Whistler |
Gwen |
bird |
f |
1989-08-30 |
1 |
Whistler |
1999-11-09 |
Went hiking. |
2 |
Puffball |
Diane |
hamster |
f |
1989-08-31 |
2 |
Puffball |
1999-11-08 |
Chased |
3 |
Bowser |
Diane |
dog |
m |
1989-08-30 |
3 |
Bowser |
1999-11-07 |
Go to park. |
4 |
Momiji |
Hayashi |
dog |
m |
2003-08-12 |
4 |
Momiji |
2003-10-20 |
Came to the Hayashi family. |
5 |
Buffy |
Nana |
dog |
f |
1991-10-10 |
5 |
Buffy |
2000-10-30 |
Climbed to the mountain. |
6 |
chee |
yoshino |
fish |
f |
1994-08-01 |
NULL |
NULL |
NULL |
NULL |
RIGHT (OUTER) JOIN (右外部結合)
eventsテーブルに存在するレコード基準で結合テーブルを作ります。
petsテーブルのレコードがない場合にはNULLとなります。
SELECT * FROM pets RIGHT OUTER JOIN events ON pets.name = events.name;
id |
name |
owner |
species |
gender |
birth |
id |
name |
date |
description |
1 |
Whistler |
Gwen |
bird |
f |
1989-08-30 |
1 |
Whistler |
1999-11-09 |
Went hiking. |
2 |
Puffball |
Diane |
hamster |
f |
1989-08-31 |
2 |
Puffball |
1999-11-08 |
Chased |
3 |
Bowser |
Diane |
dog |
m |
1989-08-30 |
3 |
Bowser |
1999-11-07 |
Go to park. |
4 |
Momiji |
Hayashi |
dog |
m |
2003-08-12 |
4 |
Momiji |
2003-10-20 |
Came to the Hayashi family. |
5 |
Buffy |
Nana |
dog |
f |
1991-10-10 |
5 |
Buffy |
2000-10-30 |
Climbed to the mountain. |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
6 |
jassmine |
2005-10-10 |
Studied. |
FULL (OUTER) JOIN (完全外部結合)
SELECT * FROM pets FULL OUTER JOIN events ON pets.name = events.name;
上記のSQLだと何もレコードを取得する事ができませんでした。
どうやらMySQLにはFULL OUTER JOINというオプション(?)は存在しないようです。
その代わり以下のように、UNIONを使ってLEFT OUTER JOINとRIGHT OUTER JOINの結果を足してから重複を削除した結果を取得する事で、FULL OUTER JOINと同じ結果を得る事が出来ました。
SELECT * FROM pets LEFT OUTER JOIN events ON pets.name = events.name UNION SELECT * FROM pets RIGHT OUTER JOIN events ON pets.name = events.name;
id |
name |
owner |
species |
gender |
birth |
id |
name |
date |
description |
1 |
Whistler |
Gwen |
bird |
f |
1989-08-30 |
1 |
Whistler |
1999-11-09 |
Went hiking. |
2 |
Puffball |
Diane |
hamster |
f |
1989-08-31 |
2 |
Puffball |
1999-11-08 |
Chased |
3 |
Bowser |
Diane |
dog |
m |
1989-08-30 |
3 |
Bowser |
1999-11-07 |
Go to park. |
4 |
Momiji |
Hayashi |
dog |
m |
2003-08-12 |
4 |
Momiji |
2003-10-20 |
Came to the Hayashi family. |
5 |
Buffy |
Nana |
dog |
f |
1991-10-10 |
5 |
Buffy |
2000-10-30 |
Climbed to the mountain. |
6 |
chee |
yoshino |
fish |
f |
1994-08-01 |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
6 |
jassmine |
2005-10-10 |
Studied. |
CROSS JOIN (クロス結合)
クロス結合は 2 つのテーブルのデータをすべて組み合わせたデータを取得できるとの事ですが、いまいちわからず。。。
なんかINNER JOINと同じ結果になってしまった。。。
SELECT * FROM pets CROSS JOIN events ON pets.name = events.name;
id |
name |
owner |
species |
gender |
birth |
id |
name |
date |
description |
1 |
Whistler |
Gwen |
bird |
f |
1989-08-30 |
1 |
Whistler |
1999-11-09 |
Went hiking. |
2 |
Puffball |
Diane |
hamster |
f |
1989-08-31 |
2 |
Puffball |
1999-11-08 |
Chased |
3 |
Bowser |
Diane |
dog |
m |
1989-08-30 |
3 |
Bowser |
1999-11-07 |
Go to park. |
4 |
Momiji |
Hayashi |
dog |
m |
2003-08-12 |
4 |
Momiji |
2003-10-20 |
Came to the Hayashi family. |
5 |
Buffy |
Nana |
dog |
f |
1991-10-10 |
5 |
Buffy |
2000-10-30 |
Climbed to the mountain. |
感想その他
以前にもjoinを調べた事があったのですが、今回もう一度知識を整理する事で理解が深まりました!