テーブルの結合(join)

昨日、結合(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を調べた事があったのですが、今回もう一度知識を整理する事で理解が深まりました!