【MySQL5.6】完全外部結合(FULL OUTER JOIN)のやりかた

※例となるようなSQLは一番下にあります。
SQLだけ知りたい方は、下部にある結論から読んでみてください。
→ [下部にある結論に飛ぶ](http://qiita.com/rhap/items/191938b930b13ee3cc3b#-%E7%B5%90%E8%AB%96)

# ■ はじめに
MySQLで完全外部結合`FULL OUTER JOIN`はまだ使えません。
ですので、同じ結果を得る別の方法を考える必要があります。
なお基礎知識は本当に基礎的なことなので、読み飛ばしていただいても大丈夫です。

# ■ 基礎知識

まず結合について、次の2テーブルを考えます。

テーブルA (table_a)

|user_id|user_name|
|---|---|
|100001|Miura|
|100002|Suzuki|
|100003|Kimura|

テーブルB (table_b)

|user_id|synonym|
|---|---|
|100002|beast|
|100004|lizard|

## ▼ INNER JOIN（内部結合）する
→ 両テーブルの共通行を結合する
### ● SQL
```sql
SELECT 
    *
FROM
    table_a AS a
        INNER JOIN
    table_b AS b ON b.user_id = a.user_id
;
```
### ● 出力結果
|user_id|user_name|user_id|synonym|
|---|---|---|---|
|100002|Suzuki|100002|beast|
### ● テーブルイメージ
![image11.png](https://qiita-image-store.s3.amazonaws.com/0/112838/b7dfbc83-bc75-eb7e-57c5-64c12b34a279.png)


## ▼ LEFT OUTER JOIN（左外部結合）する
→左側テーブルの全行と、右側テーブルの共通行を結合する ※OUTERは省略してOK
### ● SQL
```sql
SELECT 
    *
FROM
    table_a AS a
        LEFT OUTER JOIN
    table_b AS b ON b.user_id = a.user_id
;
```
### ● 出力結果
|user_id|user_name|user_id|synonym|
|---|---|---|---|
|100002|Suzuki|100002|beast|
|100001|Miura|*null*|*null*|
|100003|Kimura|*null*|*null*|
### ● テーブルイメージ
![image12.png](https://qiita-image-store.s3.amazonaws.com/0/112838/37c078c3-ddcf-b9d0-68e2-9da0dab3aaea.png)

## ▼ RIGHT OUTER JOIN（右外部結合）する
→ 左側テーブルの共通行と、右側テーブルの全行を結合する ※OUTERは省略してOK
### ● SQL
```sql
SELECT 
    *
FROM
    table_a AS a
        RIGHT OUTER JOIN
    table_b AS b ON b.user_id = a.user_id
;
```
### ● 出力結果
|user_id|user_name|user_id|synonym|
|---|---|---|---|
|100002|Suzuki|100002|beast|
|*null*|*null*|100004|lizard|
### ● テーブルイメージ
![image13.png](https://qiita-image-store.s3.amazonaws.com/0/112838/7921ba2a-e977-2857-96cb-f2d3846a0919.png)


## ▼ FULL OUTER JOIN（完全外部結合）する
→ 左側テーブルの全行と、右側テーブルの全行を結合する ※MySQLだと使えません
### ● SQL
```sql
SELECT 
    *
FROM
    table_a AS a
        FULL OUTER JOIN
    table_b AS b ON b.user_id = a.user_id
;
```
### ● 出力結果（たぶんこうなる）
|user_id|user_name|user_id|synonym|
|---|---|---|---|
|100002|Suzuki|100002|beast|
|100001|Miura|*null*|*null*|
|100003|Kimura|*null*|*null*|
|*null*|*null*|100004|lizard|
### ● テーブルイメージ
![image14.png](https://qiita-image-store.s3.amazonaws.com/0/112838/66e9e3ad-1d47-4aed-77ba-094bbd5981aa.png)


## ▼ UNIONする
→ 2つのテーブルを重複抜きしながら結合する
※2つのテーブルは列数と列の内容を合わせる必要があります
### ● SQL
```sql
SELECT 
    a.user_id AS user_id
FROM
    table_a AS a
UNION SELECT 
    b.user_id AS user_id
FROM
    table_b AS b
;
```
### ● 出力結果
|user_id|
|---|
|100001|
|100002|
|100003|
|100004|

## ▼ UNION ALLする
→ 2つのテーブルを重複抜きせず結合する
※2つのテーブルは列数と列の内容を合わせる必要があります
### ● SQL
```sql
SELECT 
    a.user_id AS user_id
FROM
    table_a AS a
UNION ALL SELECT 
    b.user_id AS user_id
FROM
    table_b AS b
;
```
### ● 出力結果
|user_id|
|---|
|100001|
|100002|
|100003|
|100002|
|100004|
## ▼ ためしにそれっぽくUNIONしてみる
### ● SQL
```sql
SELECT 
    a.user_id AS user_id
    , a.user_name AS user_name
    , NULL AS synonym
FROM
    table_a AS a
UNION SELECT 
    b.user_id AS user_id
    , NULL AS user_name
    , b.synonym AS synonym
FROM
    table_b AS b
;
```
### ● 出力結果
|user_id|user_name|synonym|
|---|---|---|
|100001|Miura|*null*|
|100002|Suzuki|*null*|
|100003|Kimura|*null*|
|100002|*null*|beast|
|100004|*null*|lizard|
※本当は**user_id: 100002**が1行になってほしいのに2行のままなのでダメ

# ■ 実現方法

はじめにもどってMySQLで`FULL OUTER JOIN`はまだ使えないので、
別の方法で実現することを考えます。ついでに実行速度も計測します。

使用したMySqlのバージョン: 5.6.32

## ▼ 準備
```sql
CREATE SCHEMA `sandbox`;
USE `sandbox`;
CREATE TABLE city_list_2010 (
    city_code CHAR(5) NOT NULL PRIMARY KEY,
    city_name VARCHAR(10) NOT NULL
);
CREATE TABLE city_list_2005 (
    city_code CHAR(5) NOT NULL PRIMARY KEY,
    city_name VARCHAR(10) NOT NULL
);
CREATE TABLE mutex (
    i INT NOT NULL PRIMARY KEY
);
INSERT INTO mutex values (0), (1);
```
・ここから2010年と2005年それぞれのファイルについて、
　『都道府県・市区町村コード』と『都道府県・市区町村名』をとってきます
　http://www.e-stat.go.jp/SG1/estat/List.do?bid=000001037709
## ▼ 1. UNIONと非排他なJOIN
### ● ```UNION```で重複抜きしつつ結合する

```sql
SELECT 
    *
FROM
    city_list_2010 AS cl2010
        LEFT OUTER JOIN
    city_list_2005 AS cl2005 ON cl2010.city_code = cl2005.city_code 
UNION SELECT 
    *
FROM
    city_list_2010 AS cl2010
        RIGHT OUTER JOIN
    city_list_2005 AS cl2005 ON cl2010.city_code = cl2005.city_code
;
```
#### ※ `LEFT OUTER JOIN`のみ（`RIGHT OUTER JOIN`を使わない）版

```sql
SELECT 
    cl2010.*
    , cl2005.*
FROM
    city_list_2010 AS cl2010
        LEFT OUTER JOIN
    city_list_2005 AS cl2005 ON cl2010.city_code = cl2005.city_code 
UNION SELECT 
    cl2010.*
    , cl2005.*
FROM
    city_list_2005 AS cl2005
        LEFT OUTER JOIN
    city_list_2010 AS cl2010 ON cl2010.city_code = cl2005.city_code
;
```

## ▼ 2. UNION ALLと排他なJOIN
### ● `UNION ALL`で結合して`WHERE`で重複抜きする

```sql
SELECT 
    *
FROM
    city_list_2010 AS cl2010
        LEFT OUTER JOIN
    city_list_2005 AS cl2005 ON cl2010.city_code = cl2005.city_code 
UNION ALL SELECT 
    *
FROM
    city_list_2010 AS cl2010
        RIGHT OUTER JOIN
    city_list_2005 AS cl2005 ON cl2010.city_code = cl2005.city_code
WHERE
    cl2010.city_code IS NULL
;
```
#### ※ `LEFT OUTER JOIN`のみ（`RIGHT OUTER JOIN`を使わない）版

```sql
SELECT 
    cl2010.*
    , cl2005.*
FROM
    city_list_2010 AS cl2010
        LEFT OUTER JOIN
    city_list_2005 AS cl2005 ON cl2010.city_code = cl2005.city_code 
UNION ALL SELECT 
    cl2010.*
    , cl2005.*
FROM
    city_list_2005 AS cl2005
        LEFT OUTER JOIN
    city_list_2010 AS cl2010 ON cl2010.city_code = cl2005.city_code
WHERE
    cl2010.city_code IS NULL
;
```

## ▼ 3. mutex tableをつかう
```sql
SELECT 
    cl2010.*, cl2005.*
FROM
    mutex
        LEFT OUTER JOIN
    city_list_2010 AS cl2010 ON i = 0
        LEFT OUTER JOIN
    city_list_2005 AS cl2005 ON i = 1 OR cl2010.city_code = cl2005.city_code
        LEFT OUTER JOIN
    city_list_2010 AS cl2010_2 ON i = 1 AND cl2010_2.city_code = cl2005.city_code
WHERE
    cl2005.city_code IS NULL OR cl2010_2.city_code IS NULL
;
```
# ■ 実現方法のベンチマーク
## ▼ 1. UNIONと非排他なJOIN
```
$ mysqlslap --user=rhap --password --create-schema=sandbox --concurrency=50 --iterations=100 --query "SELECT * FROM city_list_2010 AS cl2010 LEFT OUTER JOIN city_list_2005 AS cl2005 ON cl2010.city_code = cl2005.city_code UNION SELECT * FROM city_list_2010 AS cl2010 RIGHT OUTER JOIN city_list_2005 AS cl2005 ON cl2010.city_code = cl2005.city_code;"
```
```
Benchmark
        Average number of seconds to run all queries: 0.565 seconds
        Minimum number of seconds to run all queries: 0.545 seconds
        Maximum number of seconds to run all queries: 0.977 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1
```
## ▼ 2. UNION ALLと排他なJOIN
```
$ mysqlslap --user=rhap --password --create-schema=sandbox --concurrency=50 --iterations=100 --query "SELECT * FROM city_list_2010 AS cl2010 LEFT OUTER JOIN city_list_2005 AS cl2005 ON cl2010.city_code = cl2005.city_code UNION ALL SELECT * FROM city_list_2010 AS cl2010 RIGHT OUTER JOIN city_list_2005 AS cl2005 ON cl2010.city_code = cl2005.city_code WHERE cl2010.city_code IS NULL;"
```
```
Benchmark
        Average number of seconds to run all queries: 0.307 seconds
        Minimum number of seconds to run all queries: 0.295 seconds
        Maximum number of seconds to run all queries: 0.537 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1
```
## ▼ 3. mutex tableをつかう
```
$ mysqlslap --user=rhap --password --create-schema=sandbox --concurrency=50 --iterations=100 --query "SELECT cl2010.*, cl2005.* FROM mutex LEFT OUTER JOIN city_list_2010 AS cl2010 ON i = 0 LEFT OUTER JOIN city_list_2005 AS cl2005 ON i = 1 OR cl2010.city_code = cl2005.city_code LEFT OUTER JOIN city_list_2010 AS cl2010_2 ON i = 1 AND cl2010_2.city_code = cl2005.city_code WHERE cl2005.city_code IS NULL OR cl2010_2.city_code IS NULL;"
```
```
Benchmark
        Average number of seconds to run all queries: 27.726 seconds
        Minimum number of seconds to run all queries: 27.470 seconds
        Maximum number of seconds to run all queries: 28.201 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1
```

# ■ 結論
・MySQLで`FULL OUTER JOIN`したいときは
まず`UNION ALL`して`WHERE ... IS NULL`してみてください。

### ● 結合の対象となるテーブル
テーブルA (table_a)

|user_id|user_name|
|---|---|
|100001|Miura|
|100002|Suzuki|
|100003|Kimura|

テーブルB (table_b)

|user_id|synonym|
|---|---|
|100002|beast|
|100004|lizard|

### ● SQL
```sql
SELECT 
    a.user_id AS user_id,
    a.user_name AS user_name,
    b.synonym AS synonym
FROM
    table_a AS a
        LEFT OUTER JOIN
    table_b AS b ON b.user_id = a.user_id 
UNION ALL SELECT 
    b.user_id AS user_id,
    a.user_name AS user_name,
    b.synonym AS synonym
FROM
    table_a AS a
        RIGHT OUTER JOIN
    table_b AS b ON b.user_id = a.user_id
WHERE
    a.user_id IS NULL
ORDER BY user_id
;
```
#### ※ `LEFT OUTER JOIN`のみ（`RIGHT OUTER JOIN`を使わない）版

```sql
SELECT 
    a.user_id AS user_id,
    a.user_name AS user_name,
    b.synonym AS synonym
FROM
    table_a AS a
        LEFT OUTER JOIN
    table_b AS b ON b.user_id = a.user_id 
UNION ALL SELECT 
    b.user_id AS user_id,
    a.user_name AS user_name,
    b.synonym AS synonym
FROM
    table_b AS b
        LEFT OUTER JOIN
    table_a AS a ON b.user_id = a.user_id
WHERE
    a.user_id IS NULL
ORDER BY user_id
;
```
### ● 出力結果
|user_id|user_name|synonym|
|---|---|---|
|100001|Miura|*null*|
|100002|Suzuki|beast|
|100003|Kimura|*null*|
|100004|*null*|lizard|

- - -

なおこの文章はどこにでも載っている情報ですが、mutex tableが試したかったから書きました
参照サイト: http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/
※『mutex tableは`UNION`をサポートしていない古いバージョン向け』と参照サイトにあるように早いわけでもありませんでした
※`UNION ALL`したあとuser_idで`GROUP BY`して`SELECT`するとき`MAX()`するとかは考えるまでもなく遅いはず
※個人的には、テーブルは左に足していくものという頭があるので、`RIGHT OUTER JOIN`は極力使いません（というか使っているところ初めて見た