mysqlのテーブル結合の挙動を確認してみた~後編~

SHIFT Group 技術ブログ

2023年8月18日 19:55

はじめに

こんにちは、SHIFT の開発部門に所属しているmurasawaです。中途で入社、バックエンド関連の開発を担当しています。

現在、認証基盤の開発を行っています。
開発の中でDB等、新たに学んだり得た知見をアウトプットし理解を深めていくとともに技術の共有として役に立てば幸いです。

前回、基本的なテーブルの結合（内部結合、左外部結合、右外部結合）を自分の手を動かしてコマンドから行ってみました。

mysqlのテーブル結合の挙動を確認してみた ~前編~

今回は発展としてほかにいくつか存在する結合方法を実際に行い、データベースのテーブル結合について知識を深めてきます。

環境:mysql:5.7.35

今回扱う結合一覧

①自然結合

②完全外部結合

③自己結合

④非等価結合

⑤交差結合

自然結合

自然結合は同じ名前のカラムがある場合同じ値のレコードを結合します。

自然結合を動作させるために以下のテーブルを用います。

booksテーブル

mysql> DESC books;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| book_name | varchar(255)     | NO   |     | NULL    |                |
| price_id  | int(10) unsigned | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> select * from books;
+----+-----------+----------+
| id | book_name | price_id |
+----+-----------+----------+
|  1 | SQL入門    |        1 |
|  2 | AWS入門    |        2 |
|  3 | Azure入門  |        3 |
|  4 | TEST入門   |        1 |
|  5 | 罪と罰      |        4 |
|  6 | 時短レシピ    |        1 |
|  7 | 良いデザインとは  |        2 |
+----+-----------+----------+
7 rows in set (0.04 sec)

mysql>

priceテーブル

mysql> DESC price;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| price_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| price    | int(10) unsigned | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> select * from price;
+----------+-------+
| price_id | price |
+----------+-------+
|        1 |  1200 |
|        2 |  1100 |
|        3 |  1500 |
|        4 |   800 |
|        5 |  2000 |
|        6 |  3200 |
+----------+-------+
6 rows in set (0.01 sec)

mysql>

内部結合や外部結合では

mysql> select * from books inner join price on books.price_id = price.price_id;
+----+-----------+----------+----------+-------+
| id | book_name | price_id | price_id | price |
+----+-----------+----------+----------+-------+
|  1 | SQL入門    |        1 |        1 |  1200 |
|  2 | AWS入門    |        2 |        2 |  1100 |
|  3 | Azure入門  |        3 |        3 |  1500 |
|  4 | TEST入門   |        1 |        1 |  1200 |
|  5 | 罪と罰      |        4 |        4 |   800 |
|  6 | 時短レシピ    |        1 |        1 |  1200 |
|  7 | 良いデザインとは  |        2 |        2 |  1100 |
+----+-----------+----------+----------+-------+
7 rows in set (0.02 sec)

mysql>

on books.price_id = price.price_id;

のように結合する条件を句術していましたが、自然結合では

mysql> select * from books natural join price;
+----------+----+-----------+-------+
| price_id | id | book_name | price |
+----------+----+-----------+-------+
|        1 |  1 | SQL入門    |  1200 |
|        2 |  2 | AWS入門    |  1100 |
|        3 |  3 | Azure入門  |  1500 |
|        1 |  4 | TEST入門   |  1200 |
|        4 |  5 | 罪と罰      |   800 |
|        1 |  6 | 時短レシピ    |  1200 |
|        2 |  7 | 良いデザインとは  |  1100 |
+----------+----+-----------+-------+
7 rows in set (0.02 sec)

mysql>

と条件を記述する必要はなく、

完結に書くことができます。また、上記の内部結合のようにprice_idが重複することもなく、きれいに表示されています。

ここで試しにbooksテーブルとpriceテーブルにcategory_idを追加したテーブルを作成して、共通するカラムが2つあった場合はどうなるのか見てみます。

以下のテーブルを用意しました。

books2テーブル

mysql> DESC books2;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| book_name   | varchar(255)     | NO   |     | NULL    |                |
| price_id    | int(10) unsigned | NO   |     | NULL    |                |
| category_id | int(10) unsigned | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

mysql> select * from books2;
+----+-----------+----------+-------------+
| id | book_name | price_id | category_id |
+----+-----------+----------+-------------+
|  1 | SQL入門    |        1 |           1 |
|  2 | AWS入門    |        2 |           4 |
|  3 | Azure入門  |        3 |           3 |
+----+-----------+----------+-------------+
3 rows in set (0.01 sec)

mysql>

price2テーブル

mysql> DESC price2;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| price_id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| price       | int(10) unsigned | NO   |     | NULL    |                |
| category_id | int(10) unsigned | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> select * from price2;
+----------+-------+-------------+
| price_id | price | category_id |
+----------+-------+-------------+
|        1 |  1200 |           1 |
|        2 |  1100 |           2 |
|        3 |  1500 |           3 |
+----------+-------+-------------+
3 rows in set (0.01 sec)

mysql>

この場合、共通するカラムがすべて一致するもののみ結合して表示します。


mysql> select * from books_copy natural join price_copy;
+----------+-------------+----+-----------+-------+
| price_id | category_id | id | book_name | price |
+----------+-------------+----+-----------+-------+
|        1 |           1 |  1 | SQL入門    |  1200 |
|        3 |           3 |  3 | Azure入門  |  1500 |
+----------+-------------+----+-----------+-------+
2 rows in set (0.02 sec)

mysql>

自然結合は共通するカラムの値を見て、よしなに結合してくれるため、簡潔に書けます。

しかし、共通のカラムを複数持つなどテーブルの設計によっては注意が必要です。

完全外部結合

完全外部結合では両方のテーブルに存在するレコードを「すべて」抽出します

left join,right joinに対して完全外部結合はfull joinやfull outer joinのように使われます。
postgresSQLやOracleではfull joinを使って完全外部結合ができるようですが、

mysqlでは完全外部結合である、full joinは使えないので、疑似的に再現します。

左外部結合と右外部結合をunion句でくっ付けて表示します

使うのはbooksテーブルとpriceテーブルです。

左外部結合

mysql> select A.id, A.book_name, B.price_id, B.price from books A left join price B on A.price_id = B.price_id;
+----+-----------+----------+-------+
| id | book_name | price_id | price |
+----+-----------+----------+-------+
|  1 | SQL入門    |        1 |  1200 |
|  2 | AWS入門    |        2 |  1100 |
|  3 | Azure入門  |        3 |  1500 |
|  4 | TEST入門   |        1 |  1200 |
|  5 | 罪と罰      |        4 |   800 |
|  6 | 時短レシピ    |        1 |  1200 |
|  7 | 良いデザインとは  |        2 |  1100 |
|  8 | ピアノ入門    | NULL     | NULL  |
+----+-----------+----------+-------+
8 rows in set (0.03 sec)

mysql>

右外部結合

mysql> select A.id, A.book_name, B.price_id, B.price from books A right join price B on A.price_id = B.price_id;
+------+-----------+----------+-------+
| id   | book_name | price_id | price |
+------+-----------+----------+-------+
|    1 | SQL入門    |        1 |  1200 |
|    2 | AWS入門    |        2 |  1100 |
|    3 | Azure入門  |        3 |  1500 |
|    4 | TEST入門   |        1 |  1200 |
|    5 | 罪と罰      |        4 |   800 |
|    6 | 時短レシピ    |        1 |  1200 |
|    7 | 良いデザインとは  |        2 |  1100 |
| NULL | NULL      |        5 |  2000 |
| NULL | NULL      |        6 |  3200 |
+------+-----------+----------+-------+
9 rows in set (0.03 sec)

mysql>

上記をくっつけて表示します

完全外部結合

mysql> select A.id, A.book_name, B.price_id, B.price from books A left join price B on A.price_id = B.price_id 
\union select A.id, A.book_name, B.price_id, B.price from books A right join price B on A.price_id = B.price_id;
+------+-----------+----------+-------+
| id   | book_name | price_id | price |
+------+-----------+----------+-------+
|    1 | SQL入門    |        1 |  1200 |
|    2 | AWS入門    |        2 |  1100 |
|    3 | Azure入門  |        3 |  1500 |
|    4 | TEST入門   |        1 |  1200 |
|    5 | 罪と罰      |        4 |   800 |
|    6 | 時短レシピ    |        1 |  1200 |
|    7 | 良いデザインとは  |        2 |  1100 |
|    8 | ピアノ入門    | NULL     | NULL  |
| NULL | NULL      |        5 |  2000 |
| NULL | NULL      |        6 |  3200 |
+------+-----------+----------+-------+
10 rows in set (0.03 sec)

mysql>

上記を見ると左外部結合で表示された

|  8 | ピアノ入門    | NULL     | NULL  |

と右外部結合で表示された

| NULL | NULL      |        5 |  2000 |
| NULL | NULL      |        6 |  3200 |

が同時に表示されています。

双方に情報がないレコードもすべて表示することができます。

自己結合

自己結合はその名の通り、自己を結合します。
自己結合を動作させるために以下のテーブルを用います。

mysql> desc users;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name      | varchar(255)     | NO   |     | NULL    |                |
| parent_id | int(10) unsigned | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> select * from users;
+----+-----------------+-----------+
| id | name            | parent_id |
+----+-----------------+-----------+
|  1 | sato taro       | NULL      |
|  2 | kato jiro       | NULL      |
|  3 | fujimoto saburo |         1 |
|  4 | morimoto siro   |         1 |
|  5 | suzuki goro     |         2 |
+----+-----------------+-----------+
5 rows in set (0.01 sec)

mysql>

上記テーブルではuserのidがあり、そのidをparent_idとして使用しています。

では自己結合を左外部結合で行ってみます。

mysql> select A.id, A.name, A.parent_id, B.name as parent_name from users A left join users B on A.parent_id = B.id;
+----+-----------------+-----------+-------------+
| id | name            | parent_id | parent_name |
+----+-----------------+-----------+-------------+
|  1 | sato taro       | NULL      | NULL        |
|  2 | kato jiro       | NULL      | NULL        |
|  3 | fujimoto saburo |         1 | sato taro   |
|  4 | morimoto siro   |         1 | sato taro   |
|  5 | suzuki goro     |         2 | kato jiro   |
+----+-----------------+-----------+-------------+
5 rows in set (0.02 sec)

mysql>

自分自身のidを使って親の情報まで表示することができました。

自己と結合するため、カラム名が同じになってしまうものはasをエラーなく、使ってわかりやすく、使いやすくすることができます。

非等価結合

非等価結合は今までの

on A.parent_id = B.id

のような条件（等価）でなく不等号（><)やbetwwenなどを用いて結合することです。

失敗例

確認のため、以下のようなテーブルを用意しました。

students

mysql> desc students;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)     | NO   |     | NULL    |                |
| score | int(10) unsigned | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> select * from students;
+----+-----------------+-------+
| id | name            | score |
+----+-----------------+-------+
|  1 | sato taro       |    99 |
|  2 | kato jiro       |    89 |
|  3 | fujimoto saburo |    76 |
|  4 | morimoto siro   |    67 |
|  5 | suzuki goro     |    49 |
|  6 | goto rokuro     |    20 |
+----+-----------------+-------+
6 rows in set (0.01 sec)

mysql>

grades


mysql> desc grades;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| grade | varchar(255)     | NO   |     | NULL    |                |
| high  | int(10) unsigned | NO   |     | 100     |                |
| low   | int(10) unsigned | NO   |     | 0       |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

mysql> select * from grades;
+----+-------+------+-----+
| id | grade | high | low |
+----+-------+------+-----+
|  1 | A+    |  100 |  90 |
|  2 | A     |   89 |  80 |
|  3 | B+    |   79 |  70 |
|  4 | B     |   69 |  50 |
|  5 | C     |   49 |  30 |
|  6 | F     |   29 |   0 |
+----+-------+------+-----+
6 rows in set (0.02 sec)

mysql>

studentsテーブルが持つscoreの値で成績（grade）を付けていきます。

mysql> select A.id, A.name, A.score, B.grade from students A left join grades B on A.score between B.high and B.low;
+----+-----------------+-------+-------+
| id | name            | score | grade |
+----+-----------------+-------+-------+
|  1 | sato taro       |    99 | NULL  |
|  2 | kato jiro       |    89 | NULL  |
|  3 | fujimoto saburo |    76 | NULL  |
|  4 | morimoto siro   |    67 | NULL  |
|  5 | suzuki goro     |    49 | NULL  |
|  6 | goto rokuro     |    20 | NULL  |
+----+-----------------+-------+-------+
6 rows in set (0.02 sec)

mysql>

studentsテーブル側にgradeというカラムがないため、外部結合の性質上 gradesテーブル側のgradeを表示することができませんでした。

このように、範囲で判定して範囲に応じた評価を付けるなどはmysqlではできないようです。

非等価結合の使い方

非等価結合は自己結合と合わせて使い、行間比較（たとえばランキング（順位）算出のように行をまたいで条件を比較するような演算のこと）に用いることが多いようです。

以下のテーブルを用意します。

mysql> desc users;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> select * from users;
+-----+------------------+
| id  | name             |
+-----+------------------+
|  17 | sato taro        |
|  24 | kato jiro        |
|  34 | fujimoto saburo  |
|  47 | morimoto siro    |
|  51 | suzuki goro      |
|  62 | goto rokuro      |
| 755 | yamamoto shitiro |
+-----+------------------+
7 rows in set (0.02 sec)

mysql>

ユーザーの削除が行われたのか、idが連番になっておらずバラバラです。

これを連番として取得してみます。

mysql> select A.id, B.id from users A inner join users B on A.id >= B.id;
+-----+-----+
| id  | id  |
+-----+-----+
|  17 |  17 |
|  24 |  17 |
|  24 |  24 |
|  34 |  17 |
|  34 |  24 |
|  34 |  34 |
|  47 |  17 |
|  47 |  24 |
|  47 |  34 |
|  47 |  47 |
|  51 |  17 |
|  51 |  24 |
|  51 |  34 |
|  51 |  47 |
|  51 |  51 |
|  62 |  17 |
|  62 |  24 |
|  62 |  34 |
|  62 |  47 |
|  62 |  51 |
|  62 |  62 |
| 755 |  17 |
| 755 |  24 |
| 755 |  34 |
| 755 |  47 |
| 755 |  51 |
| 755 |  62 |
| 755 | 755 |
+-----+-----+
28 rows in set (0.06 sec)

mysql>

下記条件により

B on A.id >= B.id

id=17以下のidは17の一つ、id=24以下のidは17,24の2つとそのidが何番目かを取得することができます。

見やすく整理します。

mysql> select A.id, count(*) as seq from users A inner join users B on A.id >= B.id group by A.id;
+-----+-----+
| id  | seq |
+-----+-----+
|  17 |   1 |
|  24 |   2 |
|  34 |   3 |
|  47 |   4 |
|  51 |   5 |
|  62 |   6 |
| 755 |   7 |
+-----+-----+
7 rows in set (0.03 sec)

mysql>

これでバラバラのidが何番目のidなのか連番で把握することができます。

交差結合

交差結合は結合するテーブルのデータであり得る組み合わせすべてを表示します。

以下テーブルを利用します。

booksテーブル

mysql> DESC books;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| book_name | varchar(255)     | NO   |     | NULL    |                |
| price_id  | int(10) unsigned | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> select * from books;
+----+-----------+----------+
| id | book_name | price_id |
+----+-----------+----------+
|  1 | SQL入門    |        1 |
|  2 | AWS入門    |        2 |
|  3 | Azure入門  |        3 |
|  4 | TEST入門   |        1 |
|  5 | 罪と罰      |        4 |
|  6 | 時短レシピ    |        1 |
|  7 | 良いデザインとは  |        2 |
+----+-----------+----------+
7 rows in set (0.04 sec)

mysql>

priceテーブル

mysql> DESC price;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| price_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| price    | int(10) unsigned | NO   |     | NULL    |                |
+----------+------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> select * from price;
+----------+-------+
| price_id | price |
+----------+-------+
|        1 |  1200 |
|        2 |  1100 |
|        3 |  1500 |
|        4 |   800 |
|        5 |  2000 |
|        6 |  3200 |
+----------+-------+
6 rows in set (0.01 sec)

mysql>

では交差結合を実行します。

mysql> select A.id,A.book_name,B.price_id,B.price from books A cross join price B;
+----+-----------+----------+-------+
| id | book_name | price_id | price |
+----+-----------+----------+-------+
|  1 | SQL入門    |        1 |  1200 |
|  1 | SQL入門    |        2 |  1100 |
|  1 | SQL入門    |        3 |  1500 |
|  1 | SQL入門    |        4 |   800 |
|  1 | SQL入門    |        5 |  2000 |
|  1 | SQL入門    |        6 |  3200 |
|  2 | AWS入門    |        1 |  1200 |
|  2 | AWS入門    |        2 |  1100 |
|  2 | AWS入門    |        3 |  1500 |
|  2 | AWS入門    |        4 |   800 |
|  2 | AWS入門    |        5 |  2000 |
|  2 | AWS入門    |        6 |  3200 |
|  3 | Azure入門  |        1 |  1200 |
|  3 | Azure入門  |        2 |  1100 |
|  3 | Azure入門  |        3 |  1500 |
|  3 | Azure入門  |        4 |   800 |
|  3 | Azure入門  |        5 |  2000 |
|  3 | Azure入門  |        6 |  3200 |
|  4 | TEST入門   |        1 |  1200 |
|  4 | TEST入門   |        2 |  1100 |
|  4 | TEST入門   |        3 |  1500 |
|  4 | TEST入門   |        4 |   800 |
|  4 | TEST入門   |        5 |  2000 |
|  4 | TEST入門   |        6 |  3200 |
|  5 | 罪と罰      |        1 |  1200 |
|  5 | 罪と罰      |        2 |  1100 |
|  5 | 罪と罰      |        3 |  1500 |
|  5 | 罪と罰      |        4 |   800 |
|  5 | 罪と罰      |        5 |  2000 |
|  5 | 罪と罰      |        6 |  3200 |
|  6 | 時短レシピ    |        1 |  1200 |
|  6 | 時短レシピ    |        2 |  1100 |
|  6 | 時短レシピ    |        3 |  1500 |
|  6 | 時短レシピ    |        4 |   800 |
|  6 | 時短レシピ    |        5 |  2000 |
|  6 | 時短レシピ    |        6 |  3200 |
|  7 | 良いデザインとは  |        1 |  1200 |
|  7 | 良いデザインとは  |        2 |  1100 |
|  7 | 良いデザインとは  |        3 |  1500 |
|  7 | 良いデザインとは  |        4 |   800 |
|  7 | 良いデザインとは  |        5 |  2000 |
|  7 | 良いデザインとは  |        6 |  3200 |
|  8 | ピアノ入門    |        1 |  1200 |
|  8 | ピアノ入門    |        2 |  1100 |
|  8 | ピアノ入門    |        3 |  1500 |
|  8 | ピアノ入門    |        4 |   800 |
|  8 | ピアノ入門    |        5 |  2000 |
|  8 | ピアノ入門    |        6 |  3200 |
+----+-----------+----------+-------+
48 rows in set (0.18 sec)

mysql>

booksテーブル ×　priceテーブルで考えうる組み合わせがすべて表示されました。

テーブル間の総当たりのデータが必要な場面は多くはないと思いますが、活用できる場面はありそうです。

終わりに

基本的な内部結合、外部結合以外の特殊な結合を実行、確認してみました。

特殊なアプリや、必要に応じて使い分け生かしてきたいと思いまます。

参考

MySQL公式ドキュメント

あわせて読みたい関連記事

執筆者プロフィール：Satoshi Murasawa
前社ではRPAツールの技術サポート、開発を1年半経験。 SHIFTでは、バックエンドエンジニアとして入社し、node.jsやmysqlに触れはじめた。
DBに漠然とした興味があり、勉強して部の中でDB関連で役割を持つことができたらよいなと思っています。

ソフトウェア品質に関するお問い合わせはお気軽に！

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/

PHOTO：UnsplashのMike Kononov

みんなにも読んでほしいですか？

オススメした記事はフォロワーのタイムラインに表示されます！

最後まで読んでくださり、ありがとうございます！

御社サービスのご相談はお気軽に。

ソフトウェア品質のプロがお伺いします。