システムちーむのベンです。

先日仕事中にテーブル結合の処理を作成する機会があり、
テーブル結合に関する知識が浅かったので、調べてまとめてみました。

データの用意

データベース作成

CREATE DATABASE onepiece;
USE onepiece;

はい、完全に僕の趣味です。

海賊団テーブル(pirates)作成

海賊団ID、海賊団名の構成

CREATE TABLE pirates(
    pirates_id int primary key,
    pirates_name varchar(255)
);
INSERT INTO pirates(pirates_id,pirates_name) VALUES(1,'麦わらの一味');
INSERT INTO pirates(pirates_id,pirates_name) VALUES(2,'黒ひげ海賊団');
INSERT INTO pirates(pirates_id,pirates_name) VALUES(3,'白ひげ海賊団');

クルーテーブル(crew)作成

クルーID、海賊団ID、クルー名の構成

CREATE TABLE crew(
    id int primary key auto_increment,
    pirates_id int,
    name varchar(255)
);
INSERT INTO crew(pirates_id,name) VALUES(1,'ルフィ');
INSERT INTO crew(pirates_id,name) VALUES(1,'ゾロ');
INSERT INTO crew(pirates_id,name) VALUES(1,'サンジ');
INSERT INTO crew(pirates_id,name) VALUES(2,'ティーチ');
INSERT INTO crew(pirates_id,name) VALUES(2,'バージェス');
INSERT INTO crew(pirates_id,name) VALUES(2,'ラフィット');
INSERT INTO crew(pirates_id,name) VALUES(0,'ミホーク');

海賊団テーブル内容確認

mysql> SELECT * FROM pirates;
+------------+--------------------+
| pirates_id | pirates_name       |
+------------+--------------------+
|          1 | 麦わらの一味       |
|          2 | 黒ひげ海賊団       |
|          3 | 白ひげ海賊団       |
+------------+--------------------+
3 rows in set (0.00 sec)

クルーテーブル内容確認

ここで覚えておいてほしいのが、下記2点です。

• 白ひげ海賊団に所属するクルー(pirates_id = 3)がいない
• ミホークはどの海賊団にも所属していない(pirates_id = 0)

mysql> SELECT * FROM crew;
+----+------------+-----------------+
| id | pirates_id | name            |
+----+------------+-----------------+
|  1 |          1 | ルフィ          |
|  2 |          1 | ゾロ            |
|  3 |          1 | サンジ          |
|  4 |          2 | ティーチ        |
|  5 |          2 | バージェス      |
|  6 |          2 | ラフィット      |
|  7 |          0 | ミホーク        |
+----+------------+-----------------+
7 rows in set (0.00 sec)

では結合していきます。

内部結合（INNER JOIN）

mysql> SELECT
    ->     pirates.pirates_name,
    ->     crew.name
    -> FROM pirates INNER JOIN crew
    -> ON pirates.pirates_id = crew.pirates_id;
+--------------------+-----------------+
| pirates_name       | name            |
+--------------------+-----------------+
| 麦わらの一味       | ルフィ          |
| 麦わらの一味       | ゾロ            |
| 麦わらの一味       | サンジ          |
| 黒ひげ海賊団       | ティーチ        |
| 黒ひげ海賊団       | バージェス      |
| 黒ひげ海賊団       | ラフィット      |
+--------------------+-----------------+
6 rows in set (0.00 sec)

INNER JOINはカラム(pirates_id)の値が一致するデータのみ取得します。
なので所属者のいない白ひげ海賊団と無所属のミホークは表示されません。

外部結合（OUTER JOIN）

OUTER JOINはINNER JOINとは違い、カラムの値が一致しないものも取得します。
そして左結合(LEFT JOIN)と右結合(RIGHT JOIN)の二種類があります。
※OUTERは省略可能です。

LEFT JOIN

mysql> SELECT
    ->     pirates.pirates_name,
    ->     crew.name
    -> FROM pirates LEFT JOIN crew
    -> ON pirates.pirates_id = crew.pirates_id;
+--------------------+-----------------+
| pirates_name       | name            |
+--------------------+-----------------+
| 麦わらの一味       | ルフィ          |
| 麦わらの一味       | ゾロ            |
| 麦わらの一味       | サンジ          |
| 黒ひげ海賊団       | ティーチ        |
| 黒ひげ海賊団       | バージェス      |
| 黒ひげ海賊団       | ラフィット      |
| 白ひげ海賊団       | NULL            |
+--------------------+-----------------+
7 rows in set (0.00 sec)

所属者がいない白ひげ海賊団が表示されました。
しかし無所属のミホークは表示されてません。
こちらは海賊団(pirates)を主とし一覧表示しています。

RIGHT JOIN

mysql> SELECT
    ->     pirates.pirates_name,
    ->     crew.name
    -> FROM pirates RIGHT JOIN crew
    -> ON pirates.pirates_id = crew.pirates_id;
+--------------------+-----------------+
| pirates_name       | name            |
+--------------------+-----------------+
| 麦わらの一味       | ルフィ          |
| 麦わらの一味       | ゾロ            |
| 麦わらの一味       | サンジ          |
| 黒ひげ海賊団       | ティーチ        |
| 黒ひげ海賊団       | バージェス      |
| 黒ひげ海賊団       | ラフィット      |
| NULL               | ミホーク        |
+--------------------+-----------------+
7 rows in set (0.00 sec)

今度はミホークが表示されました。
ですが白ひげ海賊団が表示されていません。
こちらはクルー(crew)を主とし一覧表示しています。

この結果を見てわかる通り、左結合・右結合の違いをまとめると以下になります。

テーブルA LEFT JOIN テーブルB ・・・テーブルAを主とする
テーブルA RIGHT JOIN テーブルB ・・・テーブルBを主とする

左結合(LEFT JOIN)がよく使われるため、右結合(RIGHT JOIN)はほとんど使われません。

和結合（UNION）

UNIONは2つのクエリを結合するものです。

mysql> SELECT
    ->     pirates.pirates_name,
    ->     crew.name
    -> FROM pirates LEFT JOIN crew
    -> ON pirates.pirates_id = crew.pirates_id
    ->
    -> UNION
    ->
    -> SELECT
    ->     pirates.pirates_name,
    ->     crew.name
    -> FROM pirates RIGHT JOIN crew
    -> ON pirates.pirates_id = crew.pirates_id;
+--------------------+-----------------+
| pirates_name       | name            |
+--------------------+-----------------+
| 麦わらの一味       | ルフィ          |
| 麦わらの一味       | ゾロ            |
| 麦わらの一味       | サンジ          |
| 黒ひげ海賊団       | ティーチ        |
| 黒ひげ海賊団       | バージェス      |
| 黒ひげ海賊団       | ラフィット      |
| 白ひげ海賊団       | NULL            |
| NULL               | ミホーク        |
+--------------------+-----------------+
8 rows in set (0.00 sec)

海賊団もクルーも全て表示されましたね。

UNIONの後に、オプションを付ける事ができます。
ALL ・・・重複も表示
DISTINCT・・・重複は削除（デフォルト）

※今回はUNIONの後にオプション指定していないので、重複は削除された形になります。

交差結合（CROSS JOIN）

mysql> SELECT
    ->     pirates.pirates_name,
    ->     crew.name
    -> FROM pirates CROSS JOIN crew;
+--------------------+-----------------+
| pirates_name       | name            |
+--------------------+-----------------+
| 麦わらの一味       | ルフィ          |
| 黒ひげ海賊団       | ルフィ          |
| 白ひげ海賊団       | ルフィ          |
| 麦わらの一味       | ゾロ            |
| 黒ひげ海賊団       | ゾロ            |
| 白ひげ海賊団       | ゾロ            |
| 麦わらの一味       | サンジ          |
| 黒ひげ海賊団       | サンジ          |
| 白ひげ海賊団       | サンジ          |
| 麦わらの一味       | ティーチ        |
| 黒ひげ海賊団       | ティーチ        |
| 白ひげ海賊団       | ティーチ        |
| 麦わらの一味       | バージェス      |
| 黒ひげ海賊団       | バージェス      |
| 白ひげ海賊団       | バージェス      |
| 麦わらの一味       | ラフィット      |
| 黒ひげ海賊団       | ラフィット      |
| 白ひげ海賊団       | ラフィット      |
| 麦わらの一味       | ミホーク        |
| 黒ひげ海賊団       | ミホーク        |
| 白ひげ海賊団       | ミホーク        |
+--------------------+-----------------+
21 rows in set (0.00 sec)

CROSS JOINでは全ての組み合わせパターンで取得できます。
こちらの結合方法はまず使用されません。

まとめ

初心者の僕としては、いろいろな結合方法を知る事ができ、とても勉強になりました。
今後も仕事と勉強を頑張っていきたいと思います！