MySQLで複数テーブルを結合する方法について説明します。結合には、内部結合と外部結合(左外部結合、右外部結合、完全外部結合)があり、それぞれ動作確認していきます。
- 内部結合
- 両方のテーブルで対応するデータが存在するものだけを表示します。
- 外部結合(左外部結合、右外部結合、完全外部結合)
- どちらかのテーブルにしか存在しないデータについても表示します。
目次
動作確認用テーブル
動作確認のための準備をします。
テーブルを作成します。
CREATE TABLE `test`.`table_a` (
`id` INT NOT NULL ,
`code_a` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
CREATE TABLE `test`.`table_b` (
`id` INT NOT NULL ,
`table_a_id` INT NOT NULL ,
`code_b` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;レコード挿入します。
INSERT INTO `table_a` (`id`, `code_a`)
VALUES
(1, 'aaaaa'),
(2, 'bbbbb'),
(3, 'ccccc'),
(4, 'ddddd'),
(5, 'eeeee');
INSERT INTO `table_b` (`id`, `table_a_id`, `code_b`)
VALUES
(1, 2, 'apple'),
(2, 4, 'orange'),
(3, 2, 'banana'),
(4, 1, 'peach'),
(5, 6, 'melon');下記テーブルを例に説明します。
mysql> SELECT * FROM `table_a`;
+----+--------+
| id | code_a |
+----+--------+
| 1 | aaaaa |
| 2 | bbbbb |
| 3 | ccccc |
| 4 | ddddd |
| 5 | eeeee |
+----+--------+
5 rows in set (0.00 sec)mysql> SELECT * FROM `table_b`;
+----+------------+--------+
| id | table_a_id | code_b |
+----+------------+--------+
| 1 | 2 | apple |
| 2 | 4 | orange |
| 3 | 2 | banana |
| 4 | 1 | peach |
| 5 | 6 | melon |
+----+------------+--------+
5 rows in set (0.00 sec)内部結合
内部結合では、INNER JOINを使用します。
mysql> SELECT table_a.id AS id_A,
-> table_b.id AS id_B,
-> code_a,
-> code_b
-> FROM table_a
-> INNER JOIN table_b
-> ON table_a.id = table_b.table_a_id;
+------+------+--------+--------+
| id_A | id_B | code_a | code_b |
+------+------+--------+--------+
| 2 | 1 | bbbbb | apple |
| 4 | 2 | ddddd | orange |
| 2 | 3 | bbbbb | banana |
| 1 | 4 | aaaaa | peach |
+------+------+--------+--------+
4 rows in set (0.00 sec)左外部結合
左外部結合では、LEFT JOINを使用します。
mysql> SELECT table_a.id AS id_A,
-> table_b.id AS id_B,
-> code_a,
-> code_b
-> FROM table_a
-> LEFT JOIN table_b
-> ON table_a.id = table_b.table_a_id;
+------+------+--------+--------+
| id_A | id_B | code_a | code_b |
+------+------+--------+--------+
| 2 | 1 | bbbbb | apple |
| 4 | 2 | ddddd | orange |
| 2 | 3 | bbbbb | banana |
| 1 | 4 | aaaaa | peach |
| 3 | NULL | ccccc | NULL |
| 5 | NULL | eeeee | NULL |
+------+------+--------+--------+
6 rows in set (0.00 sec)右外部結合
右外部結合では、RIGHT JOINを使用します。
mysql> SELECT table_a.id AS id_A,
-> table_b.id AS id_B,
-> code_a,
-> code_b
-> FROM table_a
-> RIGHT JOIN table_b
-> ON table_a.id = table_b.table_a_id;
+------+------+--------+--------+
| id_A | id_B | code_a | code_b |
+------+------+--------+--------+
| 2 | 1 | bbbbb | apple |
| 4 | 2 | ddddd | orange |
| 2 | 3 | bbbbb | banana |
| 1 | 4 | aaaaa | peach |
| NULL | 5 | NULL | melon |
+------+------+--------+--------+
5 rows in set (0.00 sec)完全外部結合
MySQLでは、FULL OUTER JOINができません。なのでUNIONLEFT JOINRIGHT JOINを利用して完全外部結合と同じ処理結果を取得します。
※UNIONは重複行を削除して表示します。重複行を削除せずに表示したい場合UNION ALLと指定します。
mysql> SELECT table_a.id AS id_A,
-> table_b.id AS id_B,
-> code_a,
-> code_b
-> FROM table_a
-> LEFT JOIN table_b
-> ON table_a.id = table_b.table_a_id
-> UNION
-> SELECT table_a.id AS id_A,
-> table_b.id AS id_B,
-> code_a,
-> code_b
-> FROM table_a
-> RIGHT JOIN table_b
-> ON table_a.id = table_b.table_a_id;
+------+------+--------+--------+
| id_A | id_B | code_a | code_b |
+------+------+--------+--------+
| 2 | 1 | bbbbb | apple |
| 4 | 2 | ddddd | orange |
| 2 | 3 | bbbbb | banana |
| 1 | 4 | aaaaa | peach |
| 3 | NULL | ccccc | NULL |
| 5 | NULL | eeeee | NULL |
| NULL | 5 | NULL | melon |
+------+------+--------+--------+
7 rows in set (0.00 sec)
