MySQL で学ぶテーブル結合

テーブル結合とは、2つ以上のテーブルの列を合体させて1つのテーブルにすることです。今回は、「ユーザは1つのグループに所属している」というのを前提に、MySQL の以下2つのテーブルを例に説明を進めます。

users テーブル

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255) | YES  |     | NULL    |                |
| age      | int(11)      | YES  |     | NULL    |                |
| group_id | bigint(20)   | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

groups テーブル

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

それぞれテストデータを追加しておきます。

insert into groups (name) values ('Aグループ');
insert into groups (name) values ('Bグループ');
insert into groups (name) values ('Cグループ');

+----+---------------+
| id | name          |
+----+---------------+
|  1 | Aグループ       |
|  2 | Bグループ       |
|  3 | Cグループ       |
+----+---------------+

insert into users (name, age, group_id) values ('田中', 15, 1);
insert into users (name, age, group_id) values ('鈴木', 22, 2);
insert into users (name, age, group_id) values ('佐藤', 38, 2);
insert into users (name, age, group_id) values ('中田', 38, 4);

+----+--------+------+----------+
| id | name   | age  | group_id |
+----+--------+------+----------+
|  1 | 田中    |   15 |        1 |
|  2 | 鈴木    |   22 |        2 |
|  3 | 佐藤    |   38 |        2 |
|  4 | 中田    |   38 |        4 |
+----+--------+------+----------+

テーブル結合を実践する試す前に、結合の仕組みについて簡単に説明します。

外部キーと外部キー制約

テーブルを結合するためには、対象のテーブルが同じ列情報を持っている必要があります。今回は users テーブルと groups テーブルを結合するわけですが、それが可能なのは users テーブルの group_id と groups テーブルのid が一緒だからです。

ここで、groups テーブルの id はプライマリーキーですが、他のテーブルのプライマリーキーを参照する列(ここでは users テーブルの group_id)を 外部キーといいます。

基本的には、外部キーの値に、参照しているテーブルのプライマリーキーの値以外が入ると参照不可になります。これを防ぐために、外部キーに保存できる値を、参照しているテーブルのプライマリーキーの値だけに限定する 外部キー制約 を設定することができます。ただし、外部キー制約はあくまで値の制限をするのみで、これが設定されていないとテーブルが結合できない、というわけではありません。

注意点としては、外部キー制約を設定してしまうと、たとえば groups テーブルの「id = 1」のレコードを削除しようとしても、users テーブルに「group_id = 1」のデータが存在しているためエラーになって削除できなくなってしまう、ということです。

上記のサンプルデータで users テーブルに 「group_id = 4」のデータがあるのは、外部キー制約を設定していないからです。

それでは、これから users テーブルと groups テーブルを結合し、各列の情報を select で取得してみようと思います。

内部結合

結合する列の値のうち、両方のテーブルどちらにも存在する値のみを結合し、それ以外は切り捨てます。

select * from テーブルA inner join テーブルB on テーブルA.外部キー = テーブルB.プライマリーキー

on の後にどの列で結合するのかを指定しています。

今回のように結合するテーブル同士で同じ名前の列(id, name)があると、SQL でどちらのテーブルの列なのかわからずエラーになるので、テーブル名.列名 と書いて区別します。

今回の場合だと以下のような SQL になります。

select * from users inner join groups on users.group_id = groups.id;

+----+--------+------+----------+----+---------------+
| id | name   | age  | group_id | id | name          |
+----+--------+------+----------+----+---------------+
|  1 | 田中   |   15 |        1  |  1 | Aグループ      |
|  2 | 鈴木   |   22 |        2  |  2 | Bグループ      |
|  3 | 佐藤   |   38 |        2  |  2 | Bグループ      |
+----+--------+------+----------+----+---------------+

users には group_id が「4」のデータがありますが、groups には id が「4」のデータは存在しないので切り捨てられているのがわかります。

あとは id と name が被っていてわかりにくいので、 groups の id は取得せず、name には as をつけて「group_name」に変えてみます。

select users.*, groups.name as group_name from users inner join groups on users.group_id = groups.id;

+----+--------+------+----------+---------------+
| id | name   | age  | group_id | group_name    |
+----+--------+------+----------+---------------+
|  1 | 田中   |   15 |        1 | Aグループ       |
|  2 | 鈴木   |   22 |        2 | Bグループ       |
|  3 | 佐藤   |   38 |        2 | Bグループ       |
+----+--------+------+----------+---------------+

これでどの列が何の情報かわかるようになりました。

外部結合

結合する列の値のうち、どちらかのテーブルにしか存在しない値も結合します。

# 左外部結合
select * from テーブルA left outer join テーブルB on テーブルA.外部キー = テーブルB.プライマリーキー

# 右外部結合
select * from テーブルA right outer join テーブルB on テーブルA.外部キー = テーブルB.プライマリーキー

テーブルを結合する時に、結合対象のテーブルを「左」「右」で区別します。「左」にあたるのが from XXX で指定されるテーブルで、「右」にあたるのが inner join XXX で指定されるテーブルです。

左外部結合は、左側のテーブルにしか存在しない値も取得する場合に使用し、右外部結合は、右側のテーブルにしか存在しない値も取得する場合に使用します。

たとえば、users テーブルにしか存在しない値も取得する場合は、

select users.*, groups.name as group_name from users left outer join groups on users.group_id = groups.id;

+----+--------+------+----------+---------------+
| id | name   | age  | group_id | group_name    |
+----+--------+------+----------+---------------+
|  1 | 田中    |   15 |        1 | Aグループ       |
|  2 | 鈴木    |   22 |        2 | Bグループ       |
|  3 | 佐藤    |   38 |        2 | Bグループ       |
|  4 | 中田    |   38 |        4 | NULL          |
+----+--------+------+----------+---------------+

のようになり、idが「4」のデータには group_name がないので「NULL」になります。