内部結合ってなんだ (別のテーブル同士で共通するidを持つテーブルを結合する)
内部結合を理解する
チームテーブル(team)を作成
#team drop table if exists team; create table team( team_id int primary key, team_name varchar(32) ); insert into team(team_id,team_name) values(1,'現代'); insert into team(team_id,team_name) values(2,'日本画'); insert into team(team_id,team_name) values(3,'ストリート'); insert into team(team_id,team_name) values(4,'デザイン');
team :
| team_id | team_name |
|---|---|
| 1 | 現代 |
| 2 | 日本画 |
| 3 | ストリート |
| 4 | デザイン |
②メンバーテーブル(member)を作成
#member drop table if exists member; create table member( id int primary key auto_increment, team_id int, name varchar(32) ); insert into member(team_id,name) values(1,'横尾'); insert into member(team_id,name) values(3,'バリーマッギー'); insert into member(team_id,name) values(3,'マーガレット'); insert into member(team_id,name) values(2,'ひろしげ'); insert into member(team_id,name) values(2,'きいつ'); insert into member(team_id,name) values(0,'太郎');
＊ id int primary key auto_incrementでは作成順にIDが付与される
member :
| member_id | name | team_id |
|---|---|---|
| 1 | 横尾 | 1 |
| 2 | バリーマッギー | 3 |
| 3 | マーガレット | 3 |
| 4 | ひろしげ | 2 |
| 5 | きいつ | 2 |
| 6 | 太郎 | 0 |
select で確認をする
mysql> select * from team; +---------+-----------------+ | team_id | team_name | +---------+-----------------+ | 1 | 現代 | | 2 | 日本画 | | 3 | ストリート | | 4 | デザイン | +---------+-----------------+ 3 rows in set (0.00 sec)
mysql> select * from member; +----+---------+-----------------------+ | id | team_id | name | +----+---------+-----------------------+ | 1 | 1 | 横尾 | | 2 | 3 | バリーマッギー | | 3 | 3 | マーガレット | | 4 | 2 | ひろしげ | | 5 | 2 | きいつ | +----+---------+-----------------------+ 5 rows in set (0.00 sec)
できた
結合をやってみる
memberテーブルをみたときに、team_idだけではどのチームに所属しているかわかりにくいので
日本語でチーム名を表示させてみる
等価結合
select team.team_name, member.id, member.name from team,member where team.team_id = member.team_id;
+-----------------+----+-----------------------+ | team_name | id | name | +-----------------+----+-----------------------+ | 現代 | 1 | 横尾 | | ストリート | 2 | バリーマッギー | | ストリート | 3 | マーガレット | | 日本画 | 4 | ひろしげ | | 日本画 | 5 | きいつ | +-----------------+----+-----------------------+
デザインが表示されず、所属する人がいるチームしか表示がされていない
どちらかのテーブルに存在していないものは表示されない
所属の人がいるかいないかに関わらず、チームを表示
所属チームが決まっていない人も表示するには
LEFT (OUTER) JOIN, RIGHT (OUTER) JOINなどを利用する
LEFT JOIN
チームをすべて表示する
select team.team_name, member.name from team left join member on team.team_id = member.team_id;
+-----------------+-----------------------+ | team_name | name | +-----------------+-----------------------+ | 現代 | 横尾 | | ストリート | バリーマッギー | | ストリート | マーガレット | | 日本画 | ひろしげ | | 日本画 | きいつ | | デザイン | NULL | +-----------------+-----------------------+ 6 rows in set (0.00 sec)
デザインも表示された
けど、何にも所属していない太郎が表示されない
RIGHT JOIN
leftの部分をrightに変える
select team.team_name, member.name from team right join member on team.team_id = member.team_id;
+-----------------+-----------------------+ | team_name | name | +-----------------+-----------------------+ | 現代 | 横尾 | | 日本画 | ひろしげ | | 日本画 | きいつ | | ストリート | バリーマッギー | | ストリート | マーガレット | | NULL | 太郎 | +-----------------+-----------------------+ 6 rows in set (0.00 sec)
今度は太郎が出現、デザインが消えた
LEFT JOINとRIGHT JOINとは
LEFT, RIGHTは、そのテーブルが優先テーブルなのかを決めるために使う
from team(左) left join member(右)
全ての情報を結合したい場合はUNIONを使う
UNIONは2つのクエリを結合する
select team.team_name, member.name from team left join member on team.team_id = member.team_id union select team.team_name, member.name from team right join member on team.team_id = member.team_id;
+-----------------+-----------------------+ | team_name | name | +-----------------+-----------------------+ | 現代 | 横尾 | | ストリート | バリーマッギー | | ストリート | マーガレット | | 日本画 | ひろしげ | | 日本画 | きいつ | | デザイン | NULL | | NULL | 太郎 | +-----------------+-----------------------+
