【INNER JOIN, LEFT JOIN , RIGHT JOIN】テーブル結合の挙動をまとめてみた【SQL】

はじめに
テーブル結合の動作についてまとめてみました！
ログを見た時に、何してるか分かるようになることを今回の目的とします。
本記事は、**全て動作済みです。**

DBMS: Mysql
##テーブル結合の挙動について

まずはテーブルの結合がどのように行われているのか見て行きましょう。

結合にはJOIN句が使われます。


**使い方**

```sql

SELECT *
  FROM 結合元のテーブル名(左)
  JOIN 結合先のテーブル名(右)
    ON 両テーブルの結合条件
```

`JOIN`で結合先のテーブルを指定し、`ON`より後ろに、どの条件で結合するのかを記述します。



例として以下の`users`テーブルと`posts`テーブルがあるとします。

**usersテーブル**

| id | name |  
|:-: |:-:   | 
| 1  | taro |
| 2  | jiro |
| 3  | hanako |

**postsテーブル**

| id | body | user_id |
|:-: |:-:   | :-: |
| 1  | Hello | 3 |
| 2  | Hi | 1 |
| 3  | Good | 2 |

それではこの二つのテーブルを結合するために以下のsqlを発行して見ましょう。

```sql

SELECT *
  FROM users -- 結合元の左テーブル
  JOIN posts --　結合先の右テーブル
    ON users.id = posts.user_id -- 左テーブルのidと右テーブルのuser_idが一致するもの
```
流れは以下の通り。

1. `users`テーブルの右に`posts`テーブルを結合することを宣言。(`JOIN`)
2. `users`テーブルの`id`と、`posts`テーブルの`user_id`が一致するものを結合する(`ON`)
3. `users`テーブルと`posts`テーブルを結合した状態で全カラムを取得。

つまり上のSQLでは、**「`users`テーブルの`id`と、`posts`テーブルの`user_id`が一致した状態で全カラム取得してください！」**っという命令になります。

よって、結果は以下の通りです↓

```
+------+--------+------+-------+---------+
| id   | name   | id   | body  | user_id |
+------+--------+------+-------+---------+
|    3 | hanako |    1 | Hello |       3 |
|    1 | taro   |    2 | Hi    |       1 |
|    2 | jiro   |    3 | Good  |       2 |
+------+--------+------+-------+---------+
```

usersテーブルのidと、postsテーブルのuser_idが一致していることがお分かりでしょうか。

今回は全カラムを取得していましたが、結合済みの場合、結合先のテーブルからも取得することができます。

```sql

SELECT users.name, posts.body -- 変更箇所
  FROM users
  JOIN posts
    ON users.id = posts.user_id;
```

```
+--------+-------+
| name   | body  |
+--------+-------+
| hanako | Hello |
| taro   | Hi    |
| jiro   | Good  |
+--------+-------+
```

これで誰が何を投稿したのか一目瞭然ですね！
`FROM`の次が`users`になってるため、**「`users`テーブルからしか取得できないのでは？」**と思うかもしれませんが、**postsとusersが結合された状態**から取得することができます。

ここからは先ほど使っていた`JOIN(INNER JOIN)`の挙動について解説して行きます。

##INNER JOIN(内部結合)

`JOIN`は先ほど使っていたもので、**内部結合**をします。

補足: **`JOIN` と `INNER JOIN`は同じです！**


**【特徴】**
**1. 右テーブルの行数に合わせて左テーブルの行数を複製する**
**2. 結合相手がいない行は結合結果から消滅する**

一つづつ見て行きましょう。
###【INNER JOINの挙動①】右テーブルの行数に合わせて左テーブルの行数を複製する

例えば、以下のテーブルがあったとします。

**usersテーブル**

| id | name |  
|:-: |:-:   | 
| 1  | taro |
| 2  | jiro |
| 3  | hanako |

**postsテーブル**

| id | body | user_id |
|:-: |:-:   | :-: |
| 1  | Hello | 3 |
| 2  | Hi | 1 |
| 3  | Good | 2 |
| 4  | Why? | 2 |

`posts`テーブルに`id: 4`が追加されましたね。
現状では、`id`が2の`user`が一つに対し、`user_id`が2の`post`が二つあります。
ここで先ほどのjoinを使って結合しましょう。

```sql

SELECT *
  FROM users -- 結合元の左テーブル
  JOIN posts --　結合先の右テーブル
    ON users.id = posts.user_id -- 左テーブルのidと右テーブルのidが一致するもの
```

```
+------+--------+------+-------+---------+
| id   | name   | id   | body  | user_id |
+------+--------+------+-------+---------+
|    3 | hanako |    1 | Hello |       3 |
|    1 | taro   |    2 | Hi    |       1 |
|    2 | jiro   |    3 | Good  |       2 |
|    2 | jiro   |    4 | Why?  |       2 |
+------+--------+------+-------+---------+
```

usersテーブルの行が一つ増えましたね！
このように、**右テーブルに繋ぐべき行が複数ある場合、左テーブルの行を複製して結合します。**

では、この繋ぐべき行が左テーブルになかったらどうなるでしょうか？それを次に見て行きましょう。

###【INNER JOINの挙動②】 結合相手がいない行は結合結果から消滅する

それでは、結合先のいないidが4の`user`を追加して見ましょう。

**usersテーブル**

| id | name |  
|:-: |:-:   | 
| 1  | taro |
| 2  | jiro |
| 3  | hanako |
| 4  | saito  |

**postsテーブル**

| id | body | user_id |
|:-: |:-:   | :-: |
| 1  | Hello | 3 |
| 2  | Hi | 1 |
| 3  | Good | 2 |

先ほどと同じSQLを実行すると以下のようになります。

```
+------+--------+------+-------+---------+
| id   | name   | id   | body  | user_id |
+------+--------+------+-------+---------+
|    3 | hanako |    1 | Hello |       3 |
|    1 | taro   |    2 | Hi    |       1 |
|    2 | jiro   |    3 | Good  |       2 |
+------+--------+------+-------+---------+
```

このように、結合結果から削除されます。
これは`posts`テーブルに結合先のない行を追加した場合も同じです。**どちらか一方のテーブルになければ複製されず打ち消されるのです。**

また、先ほど追加した`user`の`id(4)`が`NULL`だった場合も結合結果から消滅されます。

**usersテーブル**

| id | name |  
|:-: |:-:   | 
| 1  | taro |
| 2  | jiro |
| 3  | hanako |
| NULL  | saito  |　←

**postsテーブル**

| id | body | user_id |
|:-: |:-:   | :-: |
| 1  | Hello | 3 |
| 2  | Hi | 1 |
| 3  | Good | 2 |

```
+------+--------+------+-------+---------+
| id   | name   | id   | body  | user_id |
+------+--------+------+-------+---------+
|    1 | taro   |    2 | Hi    |       1 |
|    2 | jiro   |    3 | Good  |       2 |
|    3 | hanako |    1 | Hello |       3 |
+------+--------+------+-------+---------+
```
idがNULLの`sato`さんがいなくなってますね。

SQLの世界では**どんな値に`NULL`を比較しても`FALSE`になるためです。
(つまり、NULLとNULL同士もFALSEになる)**

##内部結合と外部結合
テーブルの結合には大きく分けて、**内部結合(INNER JOIN)**と**外部結合(OUTER JOIN)**の2種類があります。
先ほどまで使っていたのが、INNER JOINという名の内部結合です。

内部結合では、**どちらかのテーブルに存在しない場合は結合結果から消滅される**という挙動をしていましたね。

しかし、
「どちらかのテーブルに存在しないから削除されるなんていやだ！」
「片方がNULLであっても結合して欲しい！」
と思った方もいるのではないでしょうか。

そんなときに使うのが次に紹介する**外部結合(OUTER JOIN)**です。
外部結合では、内部結合のように**条件に一致させた状態で結合してくれるのに加え、どちらかのテーブルに存在しないもの、NULLのものに関しても強制的に取得**してくれます。

さらに外部結合には`LEFT JOIN`、`RIGHT JOIN`, `FULL JOIN`の3種類があります。
一つづつ見て行きましょう。

【補足】
`LEFT JOIN` = `LEFT OUTER JOIN`
`RIGHT JOIN` = `RIGHT OUTER JOIN`
`FULL JOIN` = `FULL OUTER JOIN`
##LEFT JOIN(左外部結合)
左外部結合のことで、**左のテーブルは全て表示します。**

それでは結合先のない値を左テーブルに指定した場合をみてみましょう。

**usersテーブル**

| id | name |  
|:-: |:-:   | 
| 1  | taro |
| 2  | jiro |
| 3  | hanako |
| 4  | saito  |

**postsテーブル**

| id | body | user_id |
|:-: |:-:   | :-: |
| 1  | Hello | 3 |
| 2  | Hi | 1 |
| 3  | Good | 2 |

`LEFT JOIN`を使って結合します。

```sql

SELECT *
  FROM users
  LEFT JOIN posts
    ON users.id = posts.user_id
```
↓

```
+------+--------+------+-------+---------+
| id   | name   | id   | body  | user_id |
+------+--------+------+-------+---------+
|    3 | hanako |    1 | Hello |       3 |
|    1 | taro   |    2 | Hi    |       1 |
|    2 | jiro   |    3 | Good  |       2 |
|    4 | saito  | NULL | NULL  |    NULL |
+------+--------+------+-------+---------+
```

先ほど行なっていた内部結合とは違い、消滅されていたはずの行が出力されていますね。
このように、**左の行は強制的に全て表示し、右テーブルには全ての値がNULLである行を生成して結合**してくれます。


また,NULLを指定した場合も同じです。

**usersテーブル**

| id | name |  
|:-: |:-:   | 
| 1  | taro |
| 2  | jiro |
| 3  | hanako |
| NULL  | saito  |

**postsテーブル**

| id | body | user_id |
|:-: |:-:   | :-: |
| 1  | Hello | 3 |
| 2  | Hi | 1 |
| 3  | Good | 2 |


```
+------+--------+------+-------+---------+
| id   | name   | id   | body  | user_id |
+------+--------+------+-------+---------+
|    3 | hanako |    1 | Hello |       3 |
|    1 | taro   |    2 | Hi    |       1 |
|    2 | jiro   |    3 | Good  |       2 |
| NULL | saito  | NULL | NULL  |    NULL |
+------+--------+------+-------+---------+
```

##RIGHT JOIN（右外部結合)

右外部結合のことで、さっきの逆。
右のテーブルを全て表示して結合します。

`posts`テーブルの`user_id`が`NULL`になってることを確認してください。

**usersテーブル**

| id | name |  
|:-: |:-:   | 
| 1  | taro |
| 2  | jiro |
| 3  | hanako |

**postsテーブル**

| id | body | user_id |
|:-: |:-:   | :-: |
| 1  | Hello | 3 |
| 2  | Hi | 1 |
| 3  | Good | 2 |
| 4  | Sorry | 4 |

それでは`RIGHT JOIN`を発行してみましょう。

```sql

SELECT *
  FROM users
  RIGHT JOIN posts
    ON users.id = posts.user_id
```
↓

```
+------+--------+------+-------+---------+
| id   | name   | id   | body  | user_id |
+------+--------+------+-------+---------+
|    1 | taro   |    2 | Hi    |       1 |
|    2 | jiro   |    3 | Good  |       2 |
|    3 | hanako |    1 | Hello |       3 |
| NULL | NULL   |    4 | Sorry |       4 |
+------+--------+------+-------+---------+
```

このように、**右のテーブルは何が何でも全て表示し、左の`users`テーブルには4がないため、全値がNULLの行を生成して結合しています。**
(user_idがNULLの場合も同様)

完全外部結合、mysqlじゃ無理っぽいので省略します(m_ _m)

##ざっくりまとめ

**内部結合・・・ 結合すべき行が見つからなかった場合にその行は消滅する。**
**外部結合・・・ 結合すべき行が見つからなくても諦めず、全ての値が`NULL`である行を生成して結合する**

**【結合の種類】**

| 名前 | 種類 | 内容 |
|:-:|:-:|:--|
| JOIN | 内部結合 | **・右テーブルの行数に合わせて左テーブルの行数を複製する**<br><br>**・結合相手がいない行は結合結果から消滅する**  |
| LEFT JOIN  | 左外部結合 |  **・左の行は強制的に全て表示する**<br><br>**・条件に合わないものは、右テーブルに値が全てNULLである行を生成して結合する** |
| RIGHT JOIN  |  右外部結合 | **・右の行は強制的に全て表示する**<br><br>**・条件に合わないものは、右テーブルに値が全てNULLである行を生成して結合する**  |
|  FULL JOIN | 完全外部結合  | **左右の全テーブルを全て表示させる**  |


この記事で扱った簡単なデータはこっから作れると思うので、検証したい方は以下の`ddl`や`dml`を実行し、自分なりにデータをいじりながら確認してみてください。

```sql

create database test_db;
use test_db;
create table users(id int, name varchar(20));
create table posts(id int, body varchar(20), user_id int);
insert into users values(1, 'taro'), (2, 'jiro'), (3, 'hanako');
insert into posts values(1, 'Hello', 3), (2, 'Hi', 1), (3, 'Good', 2);
select * from users join posts on users.id = posts.user_id;
```