結合の違い(INNER, LEFT, RIGHT, FULL OUTER)

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ができません。なので、UNION LEFT JOIN RIGHT 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)
わくわくBank.
フリーランスのエンジニアとして活動してます。ここでは、ソフトウェア開発で必要とされる技術、用語、概念を整理しています。