自分メモ。
主にouter join関係のこと。MySQL使用。
こんな表があったとして
なんか適当にPointを加算する表とか
person
| id | name | age |
|---|---|---|
| 1 | Tarou | 19 |
| 2 | Hanako | 22 |
| 3 | Tetsuro | 20 |
Point
| id | person_id | point | subject |
|---|---|---|---|
| 1 | 1 | 100 | math |
| 2 | 2 | 60 | math |
| 3 | 1 | 90 | english |
| 4 | 2 | 0 | english |
ここまでのSQL文のcreate／insert
create table Person(id integer, name varchar(100), age integer);
insert Person(id, name, age) values(1, "Taro", 19);
insert Person(id, name, age) values(2, "Hanako", 22);
insert Person(id, name, age) values(3, "Tetsuro", 20);
create table Point(id integer, person_id integer, point integer, subject varchar(100));
insert Point(id, person_id, point, subject) values(1, 1,100,"math");
insert Point(id, person_id, point, subject) values(2, 2,60,"math");
insert Point(id, person_id, point, subject) values(3, 1,90,"english");
insert Point(id, person_id, point, subject) values(4, 2,20,"english");
LEFT JOINとRIGHT JOIN
外部結合。
LEFT／RIGHTはどちらを優先するかということ。
LEFTの場合
select * from Person left join Point
on Person.id = Point.person_id;
| id | name | age | Point.id | person_id | point | subject |
|---|---|---|---|---|---|---|
| 1 | Taro | 19 | 1 | 1 | 100 | math |
| 2 | Hanako | 22 | 2 | 2 | 60 | math |
| 1 | Taro | 19 | 3 | 1 | 90 | english |
| 2 | Hanako | 22 | 4 | 2 | 20 | english |
| 3 | Tetsuro | 20 | NULL | NULL | NULL | NULL |
左側のPersonのデータが優先される。
ない場合はNULLで補完。
RIGHTの場合
select * from Person right join Point
on Person.id = Point.person_id;
| id | name | age | Point.id | person_id | point | subject |
|---|---|---|---|---|---|---|
| 1 | Taro | 19 | 1 | 1 | 100 | math |
| 1 | Taro | 19 | 3 | 1 | 90 | english |
| 2 | Hanako | 22 | 2 | 2 | 60 | math |
| 2 | Hanako | 22 | 4 | 2 | 20 | english |
右側のPointのデータが優先される。
TeturoはPointテーブルにいないので、結合されない。
普段はあまり使わないが、中間テーブルとかで使ったことある。
JOIN句(LEFT JOIN)の条件とWHERE句の条件の違い
JOIN句
結合するときの条件。
select * from Person left join Point
on Person.id = Point.person_id and Point.point = 100;
| id | name | age | Point.id | person_id | point | subject |
|---|---|---|---|---|---|---|
| 1 | Taro | 19 | 1 | 1 | 100 | math |
| 2 | Hanako | 22 | NULL | NULL | NULL | NULL |
| 3 | Tetsuro | 20 | NULL | NULL | NULL | NULL |
イメージとしては、Personの表に対して別の表の情報(Point)をくっつけるのがLEFT JOIN。
LEFT JOINは左側優遇なので、Person表を元に結合を行う。
Pointをくっつけるための条件がPerson.id = Point.person_id and Point.point = 100。
Taroはpoint=100のデータあるため、出力されるが、
HanakoはないのでNULLで補完されている。
WHERE句
結合した後の条件。
select * from Person left join Point
on Person.id = Point.person_id
where Point.point = 100;
| id | name | age | Point.id | person_id | point | subject |
|---|---|---|---|---|---|---|
| 1 | Taro | 19 | 1 | 1 | 100 | math |
これは全て結合した表(下)から、point=100になっているものを出力する。
select * from Person left join Point
on Person.id = Point.person_id;
| id | name | age | Point.id | person_id | point | subject |
|---|---|---|---|---|---|---|
| 1 | Taro | 19 | 1 | 1 | 100 | math |
| 2 | Hanako | 22 | 2 | 2 | 60 | math |
| 1 | Taro | 19 | 3 | 1 | 90 | english |
| 2 | Hanako | 22 | 4 | 2 | 20 | english |
| 3 | Tetsuro | 20 | NULL | NULL | NULL | NULL |
JOINがいっぱいあるとき
ちょっと表を作り変え。
Person
| id | name | age |
|---|---|---|
| 1 | Tarou | 19 |
| 2 | Hanako | 22 |
| 3 | Tetsuro | 20 |
Point
| id | point | subject |
|---|---|---|
| 1 | 100 | math |
| 2 | 60 | math |
| 3 | 90 | english |
| 4 | 20 | english |
PersonPoint
| id | person_id | point_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 4 |
何の意味があるんだって感じだけどｗ
とりあえずPersonPointとかいう中間テーブルが誕生した。
ここまでのcreat/insert文
create table Person(id integer, name varchar(100), age integer);
insert Person(id, name, age) values(1, "Taro", 19);
insert Person(id, name, age) values(2, "Hanako", 22);
insert Person(id, name, age) values(3, "Tetsuro", 20);
create table Point(id integer,point integer, subject varchar(100));
insert Point(id, point, subject) values(1, 100,"math");
insert Point(id, point, subject) values(2, 60,"math");
insert Point(id, point, subject) values(3, 90,"english");
insert Point(id, point, subject) values(4, 20,"english");
create table PersonPoint(id integer, person_id integer, point_id integer);
insert PersonPoint(id, person_id, point_id) values(1, 1, 1);
insert PersonPoint(id, person_id, point_id) values(2, 2, 2);
insert PersonPoint(id, person_id, point_id) values(3, 1, 3);
insert PersonPoint(id, person_id, point_id) values(4, 2, 4);
複数のjoin
PersonPointを軸にする
select * from PersonPoint
left join Person on PersonPoint.person_id = Person.id
left join Point on PersonPoint.point_id = Point.id
| id | person_id | point_id | Person.id | name | age | Point.id | point | subject |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | Taro | 19 | 1 | 100 | math |
| 2 | 2 | 2 | 2 | Hanako | 22 | 2 | 60 | math |
| 3 | 1 | 3 | 1 | Taro | 19 | 3 | 90 | english |
| 4 | 2 | 4 | 2 | Hanako | 22 | 4 | 20 | english |
①PersonPointに対してPersonをLEFT JOiN
②①の結合表に対してPointをLEFT JOIN
select * from PersonPoint
left join Point on PersonPoint.point_id = Point.id
left join Person on PersonPoint.person_id = Person.id
| id | person_id | point_id | Point.id | point | subject | Person.id | name | age |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 100 | math | 1 | Taro | 19 |
| 3 | 1 | 3 | 3 | 90 | english | 1 | Taro | 19 |
| 2 | 2 | 2 | 2 | 60 | math | 2 | Hanako | 22 |
| 4 | 2 | 4 | 4 | 20 | english | 2 | Hanako | 22 |
①PersonPointに対してPointを LEFT JOIN
②①の結合表に対してPersonを LEFT JOIN
データのないtetsuroも出したい場合は、PersonをRIGHT JOINにする。
select * from PersonPoint
left join Point on PersonPoint.point_id = Point.id
right join Person on PersonPoint.person_id = Person.id
| id | person_id | point_id | Point.id | point | subject | Person.id | name | age |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 100 | math | 1 | Taro | 19 |
| 2 | 2 | 2 | 2 | 60 | math | 2 | Hanako | 22 |
| 3 | 1 | 3 | 3 | 90 | english | 1 | Taro | 19 |
| 4 | 2 | 4 | 4 | 20 | english | 2 | Hanako | 22 |
| NULL | NULL | NULL | NULL | NULL | NULL | 3 | Tetsuro | 20 |