計画と改善

エンジニアのブログ

MySQLのテーブル結合系の基本操作まとめ

MySQLの結合系のデータ操作方法をまとめた。

サンプルのデータベースはMySQL公式で提供されているsakilaを使用している。mysql-sakilaのdockerイメージを利用すると簡単に環境を構築できるので便利。

テーブル数も豊富で色々なクエリを試してみることができる。

mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| active_customer_vw         |
| actor                      |
| actor_genre                |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| datetime                   |
| film                       |
| film_actor                 |
| film_category              |
| film_comment               |
| film_genre                 |
| film_image                 |
| film_list                  |
| film_origin                |
| film_rating                |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
31 rows in set (0.00 sec)

INNER JOIN(内部結合)とOTUER JOIN(外部結合)とUNION

外部結合には左外部結合、右外部結合があり、以下内部結合との違いについて記述する。

内部結合と左外部結合、右外部結合の違い

  • 内部結合
    • 結合条件に指定している値が結合する両方のテーブルに存在しているレコードのみ表示する
  • 左外部結合
    • 結合条件に指定している値が左側のテーブルに存在するレコード全てと右側のテーブルのレコードを結合し、表示する
  • 右外部結合
    • 結合条件に指定している値が右側のテーブルに存在するレコード全てと左側のテーブルのレコードを結合し、表示する

在庫テーブル(inventoryテーブル)の確認するとfilm_id が1から20の間に14が抜けている(新作か何かで在庫になっていない商品)。

mysql> SELECT DISTINCT film_id from inventory WHERE film_id BETWEEN 1 and 20;
+---------+
| film_id |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       6 |
|       7 |
|       8 |
|       9 |
|      10 |
|      11 |
|      12 |
|      13 |
|      15 |
|      16 |
|      17 |
|      18 |
|      19 |
|      20 |
+---------+
19 rows in set (0.00 sec)

内部結合

inventoryテーブルとfilmテーブルを内部結合してみる。

内部結合するにはINNER JOIN句を指定する。

mysql> SELECT f.film_id, f.title FROM inventory i INNER JOIN film f ON i.film_id = f.film_id WHERE f.film_id BETWEEN 1 and 20;
+---------+---------------------+
| film_id | title               |
+---------+---------------------+
|       1 | ACADEMY DINOSAUR    |
|       1 | ACADEMY DINOSAUR    |
|       1 | ACADEMY DINOSAUR    |
|       1 | ACADEMY DINOSAUR    |
|       1 | ACADEMY DINOSAUR    |
|       1 | ACADEMY DINOSAUR    |
|       1 | ACADEMY DINOSAUR    |
|       1 | ACADEMY DINOSAUR    |
|       2 | ACE GOLDFINGER      |
|       2 | ACE GOLDFINGER      |
|       2 | ACE GOLDFINGER      |
|       3 | ADAPTATION HOLES    |
|       3 | ADAPTATION HOLES    |
|       3 | ADAPTATION HOLES    |
|       3 | ADAPTATION HOLES    |
|       4 | AFFAIR PREJUDICE    |
|       4 | AFFAIR PREJUDICE    |
|       4 | AFFAIR PREJUDICE    |
|       4 | AFFAIR PREJUDICE    |
|       4 | AFFAIR PREJUDICE    |
|       4 | AFFAIR PREJUDICE    |
|       4 | AFFAIR PREJUDICE    |
|       5 | AFRICAN EGG         |
|       5 | AFRICAN EGG         |
|       5 | AFRICAN EGG         |
|       6 | AGENT TRUMAN        |
|       6 | AGENT TRUMAN        |
|       6 | AGENT TRUMAN        |
|       6 | AGENT TRUMAN        |
|       6 | AGENT TRUMAN        |
|       6 | AGENT TRUMAN        |
|       7 | AIRPLANE SIERRA     |
|       7 | AIRPLANE SIERRA     |
|       7 | AIRPLANE SIERRA     |
|       7 | AIRPLANE SIERRA     |
|       7 | AIRPLANE SIERRA     |
|       8 | AIRPORT POLLOCK     |
|       8 | AIRPORT POLLOCK     |
|       8 | AIRPORT POLLOCK     |
|       8 | AIRPORT POLLOCK     |
|       9 | ALABAMA DEVIL       |
|       9 | ALABAMA DEVIL       |
|       9 | ALABAMA DEVIL       |
|       9 | ALABAMA DEVIL       |
|       9 | ALABAMA DEVIL       |
|      10 | ALADDIN CALENDAR    |
|      10 | ALADDIN CALENDAR    |
|      10 | ALADDIN CALENDAR    |
|      10 | ALADDIN CALENDAR    |
|      10 | ALADDIN CALENDAR    |
|      10 | ALADDIN CALENDAR    |
|      10 | ALADDIN CALENDAR    |
|      11 | ALAMO VIDEOTAPE     |
|      11 | ALAMO VIDEOTAPE     |
|      11 | ALAMO VIDEOTAPE     |
|      11 | ALAMO VIDEOTAPE     |
|      11 | ALAMO VIDEOTAPE     |
|      11 | ALAMO VIDEOTAPE     |
|      11 | ALAMO VIDEOTAPE     |
|      12 | ALASKA PHANTOM      |
|      12 | ALASKA PHANTOM      |
|      12 | ALASKA PHANTOM      |
|      12 | ALASKA PHANTOM      |
|      12 | ALASKA PHANTOM      |
|      12 | ALASKA PHANTOM      |
|      12 | ALASKA PHANTOM      |
|      13 | ALI FOREVER         |
|      13 | ALI FOREVER         |
|      13 | ALI FOREVER         |
|      13 | ALI FOREVER         |
|      15 | ALIEN CENTER        |
|      15 | ALIEN CENTER        |
|      15 | ALIEN CENTER        |
|      15 | ALIEN CENTER        |
|      15 | ALIEN CENTER        |
|      15 | ALIEN CENTER        |
|      16 | ALLEY EVOLUTION     |
|      16 | ALLEY EVOLUTION     |
|      16 | ALLEY EVOLUTION     |
|      16 | ALLEY EVOLUTION     |
|      17 | ALONE TRIP          |
|      17 | ALONE TRIP          |
|      17 | ALONE TRIP          |
|      17 | ALONE TRIP          |
|      17 | ALONE TRIP          |
|      17 | ALONE TRIP          |
|      18 | ALTER VICTORY       |
|      18 | ALTER VICTORY       |
|      18 | ALTER VICTORY       |
|      18 | ALTER VICTORY       |
|      18 | ALTER VICTORY       |
|      18 | ALTER VICTORY       |
|      19 | AMADEUS HOLY        |
|      19 | AMADEUS HOLY        |
|      19 | AMADEUS HOLY        |
|      19 | AMADEUS HOLY        |
|      19 | AMADEUS HOLY        |
|      19 | AMADEUS HOLY        |
|      20 | AMELIE HELLFIGHTERS |
|      20 | AMELIE HELLFIGHTERS |
|      20 | AMELIE HELLFIGHTERS |
+---------+---------------------+
101 rows in set (0.00 sec)

ご覧の通り、inventoryテーブルとfilmテーブルをfilm_idをキーに内部結合すると、どのタイトルの在庫がいくつあるのか確認することができる。

また、以下のように集計することもできる。

mysql> SELECT f.film_id, f.title, count(f.title) count FROM inventory i INNER JOIN film f ON i.film_id = f.film_id WHERE f.film_id BETWEEN 1 and 20 GROUP BY f.film_id;
+---------+---------------------+-------+
| film_id | title               | count |
+---------+---------------------+-------+
|       1 | ACADEMY DINOSAUR    |     8 |
|       2 | ACE GOLDFINGER      |     3 |
|       3 | ADAPTATION HOLES    |     4 |
|       4 | AFFAIR PREJUDICE    |     7 |
|       5 | AFRICAN EGG         |     3 |
|       6 | AGENT TRUMAN        |     6 |
|       7 | AIRPLANE SIERRA     |     5 |
|       8 | AIRPORT POLLOCK     |     4 |
|       9 | ALABAMA DEVIL       |     5 |
|      10 | ALADDIN CALENDAR    |     7 |
|      11 | ALAMO VIDEOTAPE     |     7 |
|      12 | ALASKA PHANTOM      |     7 |
|      13 | ALI FOREVER         |     4 |
|      15 | ALIEN CENTER        |     6 |
|      16 | ALLEY EVOLUTION     |     4 |
|      17 | ALONE TRIP          |     6 |
|      18 | ALTER VICTORY       |     6 |
|      19 | AMADEUS HOLY        |     6 |
|      20 | AMELIE HELLFIGHTERS |     3 |
+---------+---------------------+-------+
19 rows in set (0.00 sec)

内部結合は結合条件に指定している値が結合する両方のテーブルに存在しているレコードのみ表示する。のであった。

inventoryテーブルにfilm_idが14のカラム存在していないため、inventoryテーブルとfilmテーブルを内部結合してもfilm_idが14のカラムは表示されない。

左外部結合

次に左外部結合をしてみる

mysql> SELECT f.film_id, f.title, count(f.title) count FROM inventory i LEFT OUTER JOIN film f ON i.film_id = f.film_id WHERE f.film_id BETWEEN 1 and 20 GROUP BY f.film_id;
+---------+---------------------+-------+
| film_id | title               | count |
+---------+---------------------+-------+
|       1 | ACADEMY DINOSAUR    |     8 |
|       2 | ACE GOLDFINGER      |     3 |
|       3 | ADAPTATION HOLES    |     4 |
|       4 | AFFAIR PREJUDICE    |     7 |
|       5 | AFRICAN EGG         |     3 |
|       6 | AGENT TRUMAN        |     6 |
|       7 | AIRPLANE SIERRA     |     5 |
|       8 | AIRPORT POLLOCK     |     4 |
|       9 | ALABAMA DEVIL       |     5 |
|      10 | ALADDIN CALENDAR    |     7 |
|      11 | ALAMO VIDEOTAPE     |     7 |
|      12 | ALASKA PHANTOM      |     7 |
|      13 | ALI FOREVER         |     4 |
|      15 | ALIEN CENTER        |     6 |
|      16 | ALLEY EVOLUTION     |     4 |
|      17 | ALONE TRIP          |     6 |
|      18 | ALTER VICTORY       |     6 |
|      19 | AMADEUS HOLY        |     6 |
|      20 | AMELIE HELLFIGHTERS |     3 |
+---------+---------------------+-------+
19 rows in set (0.00 sec)

右外部結合

次に右外部結合をしてみる

mysql> SELECT f.film_id, f.title, count(f.title) count FROM inventory i RIGHT OUTER JOIN film f ON i.film_id = f.film_id WHERE f.film_id BETWEEN 1 and 20 GROUP BY f.film_id;
+---------+---------------------+-------+
| film_id | title               | count |
+---------+---------------------+-------+
|       1 | ACADEMY DINOSAUR    |     8 |
|       2 | ACE GOLDFINGER      |     3 |
|       3 | ADAPTATION HOLES    |     4 |
|       4 | AFFAIR PREJUDICE    |     7 |
|       5 | AFRICAN EGG         |     3 |
|       6 | AGENT TRUMAN        |     6 |
|       7 | AIRPLANE SIERRA     |     5 |
|       8 | AIRPORT POLLOCK     |     4 |
|       9 | ALABAMA DEVIL       |     5 |
|      10 | ALADDIN CALENDAR    |     7 |
|      11 | ALAMO VIDEOTAPE     |     7 |
|      12 | ALASKA PHANTOM      |     7 |
|      13 | ALI FOREVER         |     4 |
|      14 | ALICE FANTASIA      |     1 |
|      15 | ALIEN CENTER        |     6 |
|      16 | ALLEY EVOLUTION     |     4 |
|      17 | ALONE TRIP          |     6 |
|      18 | ALTER VICTORY       |     6 |
|      19 | AMADEUS HOLY        |     6 |
|      20 | AMELIE HELLFIGHTERS |     3 |
+---------+---------------------+-------+
20 rows in set (0.00 sec)

上記の相違はfilm_idが14のレコードが表示されているか否か。

いずれもinventoryテーブルが左側、filmテーブルが右側として設定されている。

左外部結合をした際はinventoryテーブルが基準となっていたため、film_idが14のレコードが含まれることは無かった。

それに対して右外部結合をした場合、filmテーブルにfilm_idが14のレコードが含まれているため、結果としてfilm_idが14のレコードも取得された。

UNION

UNIONを使用すると同名のカラムをもったテーブル同士を結合することができる。

UNIONにALLを指定した場合、重複したカラムも表示させることができる。

customerテーブル

mysql> SELECT last_name, first_name from customer;
+--------------+-------------+
| last_name    | first_name  |
+--------------+-------------+
| SMITH        | MARY        |
| JOHNSON      | PATRICIA    |
...中略...
| DELVALLE     | WADE        |
| CINTRON      | AUSTIN      |
+--------------+-------------+
599 rows in set (0.00 sec)

actorテーブル

mysql> SELECT last_name, first_name from actor;
+--------------+-------------+
| last_name    | first_name  |
+--------------+-------------+
| GUINESS      | PENELOPE    |
| WAHLBERG     | NICK        |
| CHASE        | ED          |
...中略...
| KEITEL       | MARY        |
| FAWCETT      | JULIA       |
| TEMPLE       | THORA       |
+--------------+-------------+
200 rows in set (0.00 sec)

UNION ALL

mysql> SELECT last_name, first_name FROM customer UNION ALL SELECT last_name, first_name FROM actor;
+--------------+-------------+
| last_name    | first_name  |
+--------------+-------------+
| SMITH        | MARY        |
| JOHNSON      | PATRICIA    |
...中略...
| FAWCETT      | JULIA       |
| TEMPLE       | THORA       |
+--------------+-------------+
799 rows in set (0.00 sec)

UNION

UNIONを指定した場合は重複行は削除される。

mysql> SELECT last_name, first_name FROM customer UNION SELECT last_name, first_name FROM actor;
+--------------+-------------+
| last_name    | first_name  |
+--------------+-------------+
| SMITH        | MARY        |
...中略...
| KEITEL       | MARY        |
| FAWCETT      | JULIA       |
| TEMPLE       | THORA       |
+--------------+-------------+
797 rows in set (0.00 sec)

UNION ALLとUNIONで確認した通り、今回は799 - 797 = 2件の重複があることがわかった。 UNION ALLで重複している行は以下のようなクエリで確認することができる。

mysql> SELECT * FROM (SELECT last_name, first_name FROM customer UNION ALL SELECT last_name, first_name FROM actor) tmp GROUP BY last_name, first_name HAVING COUNT(*) >= 2;
+-----------+------------+
| last_name | first_name |
+-----------+------------+
| DAVIS     | JENNIFER   |
| DAVIS     | SUSAN      |
+-----------+------------+
2 rows in set (0.00 sec)

感想

MySQLにはINTERSECTやEXCEPTなどの演算子が用意されていないので集合を扱おうとするとやや手間がかかる。

sakilaみたいな公式のサンプルがあるので、練習するのに手を動かしてできるので大変よい。