SQLについての自分メモ(outer join関係)

自分メモ。
主に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
```MySQL
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の場合

```MySQL
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の場合


```MySQL
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句
結合するときの条件。

```MySQL
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句
結合した後の条件。


```MySQL
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になっているものを出力する。



```MySQL
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文

```MySQL
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を軸にする

```MySQL
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

```MySQL
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にする。

```MySQL
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