これでわかった!? LEFT / RIGHT JOIN.

JOINを曖昧に使っている人に説明するためのメモ。
LEFT, RIGHTやOUTER, INNERの違いを整理。

## 環境

* 検証はMySQLで行っています。

## テストDBおよびテーブルの用意

ここでは、正規化された社員管理用に部署テーブル(depts)と社員テーブル(employees)を作成し、各種JOIN句を実行して期待の結果が得られるようにしてみます。

### データベースの作成

もちろん既存のDBでも構いません。

```sql
create database testdb;
use testdb;
```
### 部署テーブル(depts)の作成

部署コードと部署名のシンプルなテーブル。

```sql
drop table if exists depts;
create table depts(
	dept_id int primary key,
	dept_name varchar(32)
);

insert into depts(dept_id,dept_name) values(1,'営業部');
insert into depts(dept_id,dept_name) values(2,'経理部');
insert into depts(dept_id,dept_name) values(3,'技術部');
insert into depts(dept_id,dept_name) values(4,'法務部');
```

### 従業員テーブル(employees)の作成

社員ID,社員名,部署コードから構成される。

```sql
#employees
drop table if exists employees;
create table employees(
	id int primary key auto_increment,
	dept_id int,
	name varchar(32)
);

insert into employees(dept_id,name) values(1,'田中');
insert into employees(dept_id,name) values(2,'玉木');
insert into employees(dept_id,name) values(3,'鈴木');
insert into employees(dept_id,name) values(3,'山本');
insert into employees(dept_id,name) values(2,'斉藤');
insert into employees(dept_id,name) values(1,'佐藤');
insert into employees(dept_id,name) values(1,'小澤');
insert into employees(dept_id,name) values(2,'関野');
insert into employees(dept_id,name) values(0,'中村');
```

### テーブル内容の確認(depts)
4つの部署と部署コードが登録されています。

```console
mysql> select * from depts;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 営業部     |
|       2 | 経理部     |
|       3 | 技術部     |
|       4 | 法務部     |
+---------+-----------+
```

### テーブル内容の確認(employees)

#### データの要点
employeesテーブルを見て下さい。
いくつか注意、記憶しておいてほしいポイントがあります。

* 法務部に所属する人間(dept_id=4)が１人もいない。
* 中村さんは、どの部署にも所属していない(dept_id=0)。

この2点だけしっかり覚えておいて下さい。

```console
mysql> select * from employees;
+----+---------+--------+
| id | dept_id | name   |
+----+---------+--------+
|  1 |       1 | 田中   |
|  2 |       2 | 玉木   |
|  3 |       3 | 鈴木   |
|  4 |       3 | 山本   |
|  5 |       2 | 斉藤   |
|  6 |       1 | 佐藤   |
|  7 |       1 | 小澤   |
|  8 |       2 | 関野   |
|  9 |       0 | 中村   |
+----+---------+--------+
```

## 結合してみる

上記のようなテーブルがあるときに、一番ニーズのあるクエリは、dept_idでは、どこの部署か直感的にわからないため、日本語で部署名を表示させたいというものです。

このような効果を得るために通常「結合(JOIN)」という操作を行いますが、使用するJOIN句により、得られる結果が異なるため、それをおさらいしてみます。

では、それぞれ、パターンと結果を見ていきます。

### 等価結合

よく使われる結合です。WHERE文に結合のkeyとなるカラムを指定します。

```sql
select
	depts.dept_name,
	employees.name
from
	depts,employees
where depts.dept_id = employees.dept_id;
```

>なお、私は、クエリを小文字で書く習慣がありますので、全て小文字で書いていますが、SQL分はSELECT FROM WHEREと大文字で書く方がわかりやすいかもしれません。もちろん結果に差はありません。

実行結果は下記の通りです。

```
+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部     | 田中   |
| 営業部     | 佐藤   |
| 営業部     | 小澤   |
| 経理部     | 玉木   |
| 経理部     | 斉藤   |
| 経理部     | 関野   |
| 技術部     | 鈴木   |
| 技術部     | 山本   |
+-----------+--------+
```
アプリ開発の多くの場合で、これで事足りますが、いくつか注意点があります。

* 法務部が表示されていません。
* 中村さんが表示されていません。

お気づきの通り、どちらかのテーブルに存在しないものは表示されていません（逆に言えば、両方のテーブルに存在するものが表示されています）。

このように等価結合というクエリでは、keyで指定したデータで、互いのテーブルに存在するものが結合、表示されます。

一方、

* 所属の人がいるかいないかに関わらず、部署は表示したい。
* 所属部署が決まっていない人も表示したい。

などの要求がある場合は、等価結合では情報不足です。

その場合は、LEFT (OUTER) JOIN, RIGHT (OUTER) JOINなどを利用して、期待する結果を得られるようにします。

### LEFT JOIN

#### 部署を全て表示してみる
詳細の説明に入る前に、ひとまず、以下のクエリを実行してみてください。

```sql
select
	depts.dept_name,
	employees.name
from depts left join employees
on depts.dept_id = employees.dept_id;
```
誰も所属していない法務部が表示されました。

>但し、無所属の中村さんは表示されていません。

```
+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部     | 田中   |
| 営業部     | 佐藤   |
| 営業部     | 小澤   |
| 経理部     | 玉木   |
| 経理部     | 斉藤   |
| 経理部     | 関野   |
| 技術部     | 鈴木   |
| 技術部     | 山本   |
| 法務部     | NULL   |
+-----------+--------+
```
詳細を説明する前に、さらにRIGHT JOINを実行してみましょう。

### RIGHT JOIN

上記のクエリの文はそのままで、leftの部分をrightに変えて実行してみて下さい。

```sql
select
	depts.dept_name,
	employees.name
from depts right join employees
on depts.dept_id = employees.dept_id;
```
今度は、中村さんが表示されました。

>が、法務部は表示されていません。

```bash
+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部     | 田中   |
| 経理部     | 玉木   |
| 技術部     | 鈴木   |
| 技術部     | 山本   |
| 経理部     | 斉藤   |
| 営業部     | 佐藤   |
| 営業部     | 小澤   |
| 経理部     | 関野   |
| NULL      | 中村   |
+-----------+--------+
```

### 結局LEFT JOINとRIGHT JOINって何？

とりあえず、left joinとright joinを実行してみました。
何となく違いはわかりましたが、ここでまとめて見ましょう。

#### OUTER JOIN

まず、LEFT JOIN, RIGHT JOINは、LEFT OUTER JOIN, RIGHT OUTER JOINの省略系です。
LEFTであろうと、RIGHTであろうと、OUTER JOINという属性のクエリです。

##### OUTER JOINとは、複数のテーブルがあり、それを結合する際、優先テーブルを１つ決め、そこにある情報は全て表示しながら、他のテーブル情報に対になるデータがあれば表示する（ない場合はNULLで補完表示）。

という機能となります。そして、LEFT, RIGHTは、そのテーブルが優先テーブルなのかを決めるために使っています。

>ここでは優先テーブルという名前を使っていますが、マスターテーブルとかいろいろな呼び名があります。

### #何を基準に左(LEFT)、右(RIGHT)なの？

右、左は、下記の図の通り、FROM句の中での位置関係です。

![join](http://www.bluecode.jp/qiita/join01.png)


##### 優先テーブルに指定されると、そのテーブルの項目は漏れ無く表示される

ということです。なので、

* from depts left join employees
* from employees right join depts

は等価になります（どちらも、優先テーブルはdepts）。

実際に実行してみましょう。
下記は、先ほど紹介したRIGHT JOIN句はそのままにテーブルの位置を入れ替えたものです。

```sql
select
	depts.dept_name,
	employees.name
from employees right join depts
on depts.dept_id = employees.dept_id;
```
結果は、from depts left join employeesとした場合と同じです。

```
+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部     | 田中   |
| 営業部     | 佐藤   |
| 営業部     | 小澤   |
| 経理部     | 玉木   |
| 経理部     | 斉藤   |
| 経理部     | 関野   |
| 技術部     | 鈴木   |
| 技術部     | 山本   |
| 法務部     | NULL   |
+-----------+--------+
```

#### 全ての情報を結合したい

では、両方に無いけど、全てのレコードを結合したい場合はどうすればいいでしょうか？
これはDBにより異なりますが、ここでは、UNIONを使って、left joinとright joinの2つのクエリを結合してみます。

```sql
select
	depts.dept_name,
	employees.name
from depts left join employees
on depts.dept_id = employees.dept_id

union

select
	depts.dept_name,
	employees.name
from depts right join employees
on depts.dept_id = employees.dept_id;
```

UNIONは2つのクエリを結合するものです（重複文は表示されません）。
法務部も中村さんも表示されています。

```
+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部     | 田中   |
| 営業部     | 佐藤   |
| 営業部     | 小澤   |
| 経理部     | 玉木   |
| 経理部     | 斉藤   |
| 経理部     | 関野   |
| 技術部     | 鈴木   |
| 技術部     | 山本   |
| 法務部     | NULL   |
| NULL      | 中村   |
+-----------+--------+
```

### INNER JOIN

では、続いてinner joinを見てみます。

#### INNER JOINにはLEFT, RIGHTの概念は無い（たぶん）

inner joinは、その名の通り、outerjoinの逆なので、「どちらのテーブルにあるものを結合し表示する」ということになります。なので、結合するテーブルのどちらを優先テーブルに指定しようが、どちらにもあるものしか表示されないので、そもそも優先テーブルを指定する必要や意味がありません。

>文法的にはFROMの直後にくる（LEFT）のものが優先テーブル的なものにはなります。

ひとまず、下記のクエリを実行してみます。

```sql
select
	depts.dept_name,
	employees.name
from depts inner join employees
on depts.dept_id = employees.dept_id;
```
このような結果が得られました。法務部も中村さんも表示されていません。

```
+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部  　  | 田中   |
| 営業部  　  | 佐藤   |
| 営業部   　 | 小澤   |
| 経理部   　 | 玉木   |
| 経理部  　  | 斉藤   |
| 経理部  　  | 関野   |
| 技術部  　  | 鈴木   |
| 技術部  　  | 山本   |
+-----------+--------+
```

#### テーブルの書く位置を変えてみる

先ほど補足したとおり、inner joinにも優先テーブルという概念が無いわけではありません。fromの直後に来るテーブルが文法上の優先テーブルです。念のため、deptsとemployeesを入替えて実行してみてください。

```sql
select
	depts.dept_name,
	employees.name
from employees
inner join depts on depts.dept_id = employees.dept_id;
```

やはり結果は同じようです。

```
+-----------+--------+
| dept_name | name   |
+-----------+--------+
| 営業部     | 田中   |
| 営業部     | 佐藤   |
| 営業部     | 小澤   |
| 経理部     | 玉木   |
| 経理部     | 斉藤   |
| 経理部     | 関野   |
| 技術部     | 鈴木   |
| 技術部     | 山本   |
+-----------+--------+
```

さらに、この結果、どこかで見ました。最初に試した等価結合です。
inner joinは、等価結合と同じです（厳密には違いますが）。