システムちーむのベンです。
先日仕事中にテーブル結合の処理を作成する機会があり、
テーブル結合に関する知識が浅かったので、調べてまとめてみました。
目次
データの用意
データベース作成
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では全ての組み合わせパターンで取得できます。
こちらの結合方法はまず使用されません。
まとめ
初心者の僕としては、いろいろな結合方法を知る事ができ、とても勉強になりました。
今後も仕事と勉強を頑張っていきたいと思います!