今回は、MySQLにおけるテーブルの結合(JOIN)について理解が整理できていない部分がありましたので整理したいと思います。
まず、結合(JOIN)を下記のように分類します。
1.内部結合(INNER JOIN)
2.外部結合(OUTER JOIN)
2-1.左外部結合(LEFT OUTER JOIN)
2-2.右外部結合(RIGHT OUTER JOIN)
2-3.完全外部結合(FULL OUTER JOIN)
次に、確認の準備を、、
準備の手順は本記事の下部に【準備】として書き留めておきました。
|
左(LEFT)
|
右(RIGHT)
|
mysql> select * from prefectures;
+------+-----------------+---------+
| id | prefecture_name | area_id |
+------+-----------------+---------+
| 1 | 福岡県 | 3 |
| 2 | 東京都 | 2 |
| 3 | 千葉県 | 2 |
| 4 | 高知県 | 4 |
+------+-----------------+---------+
4 rows in set (0.00 sec) |
mysql> select * from areas;
+------+-----------------+
| id | area_name |
+------+-----------------+
| 1 | 北海道地方 |
| 2 | 関東地方 |
| 3 | 九州地方 |
+------+-----------------+
3 rows in set (0.00 sec) |
この2つのテーブルを例として、各結合を確認していきます。
1.内部結合(INNER JOIN)
以下より、内部結合は、結合条件に一致しないデータは取得されない結合ということができます。
①左(prefectures)のテーブルの一覧を取得し、
②右(areas)テーブルから、ONで指定した条件にマッチするレコードを1つずつ探して結合します。
③マッチするレコードが複数あった場合、結合したものをそれぞれ別のレコードにします。
④左(prefectures)のテーブル一覧のうち、条件にマッチするレコードがないものは削除されます。
mysql> select * from prefectures join areas on prefectures.area_id = areas.id;
+------+-----------------+---------+------+--------------+
| id | prefecture_name | area_id | id | area_name |
+------+-----------------+---------+------+--------------+
| 1 | 福岡県 | 3 | 3 | 九州地方 |
| 2 | 東京都 | 2 | 2 | 関東地方 |
| 3 | 千葉県 | 2 | 2 | 関東地方 |
+------+-----------------+---------+------+--------------+
3 rows in set (0.00 sec)
2.外部結合(OUTER JOIN)
2-1.左外部結合(LEFT OUTER JOIN)
以下より、左外部結合は、左表の全行を必ず出力する結合ということができます。
①左(prefectures)のテーブルの一覧を取得し、
②右(areas)テーブルから、ONで指定した条件にマッチするレコードを1つずつ探して結合します。
③マッチするレコードが複数あった場合、結合したものをそれぞれ別のレコードにします。
④左(prefectures)のテーブルの一覧のうち、条件にマッチするレコードがないものの値はNULLになります。
レコードは削除されません。
一方、右(areas)のテーブルの一覧のうち、条件にマッチするレコードがないもは削除されます。
mysql> select * from prefectures left (outer) join areas on prefectures.area_id = areas.id;
+------+-----------------+---------+------+--------------+
| id | prefecture_name | area_id | id | area_name |
+------+-----------------+---------+------+--------------+
| 1 | 福岡県 | 3 | 3 | 九州地方 |
| 2 | 東京都 | 2 | 2 | 関東地方 |
| 3 | 千葉県 | 2 | 2 | 関東地方 |
| 4 | 高知県 | 4 | NULL | NULL |
+------+-----------------+---------+------+--------------+
4 rows in set (0.00 sec)
2-2.右外部結合(RIGHT OUTER JOIN)
以下より、右外部結合は、右表の全行を必ず出力する結合ということができます。
①左(prefectures)のテーブルの一覧を取得し、
②右(areas)テーブルから、ONで指定した条件にマッチするレコードを1つずつ探して結合します。
③マッチするレコードが複数あった場合、結合したものをそれぞれ別のレコードにします。
④右(areas)のテーブルの一覧のうち、条件にマッチするレコードがないものの値はNULLになります。
レコードは削除されません。
一方、左(prefectures)のテーブルの一覧のうち、条件にマッチするレコードがないもは削除されます。
mysql> select * from prefectures right (outer) join areas on prefectures.area_id = areas.id;
+------+-----------------+---------+------+-----------------+
| id | prefecture_name | area_id | id | area_name |
+------+-----------------+---------+------+-----------------+
| NULL | NULL | NULL | 1 | 北海道地方 |
| 2 | 東京都 | 2 | 2 | 関東地方 |
| 3 | 千葉県 | 2 | 2 | 関東地方 |
| 1 | 福岡県 | 3 | 3 | 九州地方 |
+------+-----------------+---------+------+-----------------+
4 rows in set (0.00 sec)
2-3.完全外部結合(FULL OUTER JOIN)
完全外部結合とは左右の表の全行を必ず出力する結合です。
MySQLは完全外部結合に対応していませんが、下記の例の様に、左外部結合(LEFT OUTER JOIN)と右外部結合(RIGHT OUTER JOIN)をUNIONすることによって同じ結果を得ることができます。
mysql> select * from prefectures
-> left (outer) join areas on prefectures.area_id = areas.id
-> union
-> select * from prefectures
-> right (outer) join areas on prefectures.area_id = areas.id;
+------+-----------------+---------+------+-----------------+
| id | prefecture_name | area_id | id | area_name |
+------+-----------------+---------+------+-----------------+
| 1 | 福岡県 | 3 | 3 | 九州地方 |
| 2 | 東京都 | 2 | 2 | 関東地方 |
| 3 | 千葉県 | 2 | 2 | 関東地方 |
| 4 | 高知県 | 4 | NULL | NULL |
| NULL | NULL | NULL | 1 | 北海道地方 |
+------+-----------------+---------+------+-----------------+
5 rows in set (0.00 sec)
最後に結合(JOIN)は、2つのテーブルのどちらを右表とみるか左表とみるかで外部結合に関しては一部出力結果が変わってくるようです。
以上です！
【準備】
mysql> create database prefectures;
Query OK, 1 row affected (0.02 sec)
mysql> use prefectures;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table prefectures.prefectures (id int, prefecture_name varchar(20), area_id int);
Query OK, 0 rows affected (0.08 sec)
mysql> create table prefectures.areas (id int, area_name varchar(20));
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
+-----------------------+
| Tables_in_prefectures |
+-----------------------+
| areas |
| prefectures |
+-----------------------+
2 rows in set (0.00 sec)
mysql> desc prefectures;
+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| prefecture_name | varchar(20) | YES | | NULL | |
| area_id | int(11) | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> desc areas;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| area_name | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into prefectures
-> (id, prefecture_name, area_id)
-> VALUES (1, '福岡県', 3), (2, '東京都', 2), (3, '千葉県' ,2), (4, '高知県' ,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into areas
-> (id, area_name)
-> VALUES (1, '北海道地方'), (2, '関東地方'), (3, '九州地方');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
【参考サイト】
『内部結合と外部結合』
『内部結合(INNER JOIN句)』
『MySQLでの完全外部結合』