読者です 読者をやめる 読者になる 読者になる

Training to develop the IT skills

IT技術の習得を日々記録します

内部結合ってなんだ (別のテーブル同士で共通する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            | 太郎                  |
+-----------------+-----------------------+