mysqlのテーブル結合の挙動を確認してみた~前編~
はじめに
こんにちは、SHIFT の開発部門に所属しているmurasawaです。 中途で入社、バックエンド関連の開発を担当しています。
現在、認証基盤の開発を行っています。
開発の中でDB等、新たに学んだり得た知見をアウトプットし理解を深めていくとともに技術の共有として役に立てば幸いです。
今回、テーブルの結合を自分の手を動かしてコマンドから行ってみました。 普段はアプリを使って意識せずに行えていますが、sql文を実際に実行することで理解を深めていきたいと思います。
環境:mysql:5.7.35
以下のテーブルを使用してテーブル結合を行っていきます。
商品テーブル
mysql> DESC products;
+------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| price | int(10) unsigned | NO | | NULL | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | |
+------------+------------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.02 sec)
mysql> select * from products;
+----+-------------+-------+---------------------+---------------------+
| id | name | price | updated_at | created_at |
+----+-------------+-------+---------------------+---------------------+
| 1 | 本 | 1000 | 2022-12-27 17:05:46 | 2022-12-27 17:05:46 |
| 2 | 時計 | 1500 | 2022-12-27 17:06:03 | 2022-12-27 17:06:03 |
| 3 | 加湿器 | 3000 | 2022-12-27 17:06:26 | 2022-12-27 17:06:26 |
| 4 | 土鍋 | 2700 | 2022-12-27 17:06:58 | 2022-12-27 17:06:58 |
| 5 | 扇風機 | 3800 | 2022-12-27 17:07:26 | 2022-12-27 17:07:26 |
| 6 | エビオス錠2000錠 | 3200 | 2022-12-27 17:08:12 | 2022-12-27 17:08:12 |
+----+-------------+-------+---------------------+---------------------+
6 rows in set (0.05 sec)注文テーブル
mysql> DESC orders;
+------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| quantity | int(10) unsigned | NO | | NULL | |
| product_id | int(10) unsigned | NO | MUL | NULL | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | |
+------------+------------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.03 sec)
mysql> select * from orders;
+----+----------+------------+---------------------+---------------------+
| id | quantity | product_id | updated_at | created_at |
+----+----------+------------+---------------------+---------------------+
| 1 | 2 | 1 | 2022-12-27 17:08:36 | 2022-12-27 17:08:36 |
| 2 | 1 | 2 | 2022-12-27 17:08:45 | 2022-12-27 17:08:45 |
| 3 | 3 | 3 | 2022-12-27 17:09:25 | 2022-12-27 17:09:25 |
| 4 | 5 | 4 | 2022-12-27 17:09:38 | 2022-12-27 17:09:38 |
| 5 | 10 | 1 | 2022-12-27 17:09:48 | 2022-12-27 17:09:48 |
| 6 | 25 | 1 | 2022-12-27 17:10:12 | 2022-12-27 17:10:12 |
| 7 | 3 | 2 | 2022-12-27 17:10:23 | 2022-12-27 17:10:23 |
+----+----------+------------+---------------------+---------------------+
7 rows in set (0.03 sec)
mysql> 内部結合
上記テーブルはproductsテーブルのidで紐づいています。
productsのidとordersのproduct_idを紐づけて、結合することができます。
結合したテーブルは実態はありませんが一つのテーブルのように扱うことができます。
mysql> select * from products inner join orders on products.id = orders.product_id;
+----+------+-------+---------------------+---------------------+----+----------+------------+---------------------+---------------------+
| id | name | price | updated_at | created_at | id | quantity | product_id | updated_at | created_at |
+----+------+-------+---------------------+---------------------+----+----------+------------+---------------------+---------------------+
| 1 | 本 | 1000 | 2022-12-27 17:05:46 | 2022-12-27 17:05:46 | 1 | 2 | 1 | 2022-12-27 17:08:36 | 2022-12-27 17:08:36 |
| 1 | 本 | 1000 | 2022-12-27 17:05:46 | 2022-12-27 17:05:46 | 5 | 10 | 1 | 2022-12-27 17:09:48 | 2022-12-27 17:09:48 |
| 1 | 本 | 1000 | 2022-12-27 17:05:46 | 2022-12-27 17:05:46 | 6 | 25 | 1 | 2022-12-27 17:10:12 | 2022-12-27 17:10:12 |
| 2 | 時計 | 1500 | 2022-12-27 17:06:03 | 2022-12-27 17:06:03 | 2 | 1 | 2 | 2022-12-27 17:08:45 | 2022-12-27 17:08:45 |
| 2 | 時計 | 1500 | 2022-12-27 17:06:03 | 2022-12-27 17:06:03 | 7 | 3 | 2 | 2022-12-27 17:10:23 | 2022-12-27 17:10:23 |
| 3 | 加湿器 | 3000 | 2022-12-27 17:06:26 | 2022-12-27 17:06:26 | 3 | 3 | 3 | 2022-12-27 17:09:25 | 2022-12-27 17:09:25 |
| 4 | 土鍋 | 2700 | 2022-12-27 17:06:58 | 2022-12-27 17:06:58 | 4 | 5 | 4 | 2022-12-27 17:09:38 | 2022-12-27 17:09:38 |
+----+------+-------+---------------------+---------------------+----+----------+------------+---------------------+---------------------+
7 rows in set (0.02 sec)
mysql> 2つのテーブルが結合され、「products」のデータが左、「orders」のデータが右側に表示されています。
「SELECT」句に列を指定しない場合、「INNER JOIN」の左側に指定したテーブル「prodcts」の内容が左側に表示され、右側に2番目に指定したテーブルの内容が表示されます。
しかしこれではidやupdated_at,created_atが2つ表示されていたり並び順がわかり辛いので整理して表示してみます。
mysql> select orders.id, products.id as product_id, products.name,products.price, orders.quantity, orders.updated_at, orders.created_at from products inner join orders on products.id = orders.product_id;
+----+------------+------+-------+----------+---------------------+---------------------+
| id | product_id | name | price | quantity | updated_at | created_at |
+----+------------+------+-------+----------+---------------------+---------------------+
| 1 | 1 | 本 | 1000 | 2 | 2022-12-27 17:08:36 | 2022-12-27 17:08:36 |
| 5 | 1 | 本 | 1000 | 10 | 2022-12-27 17:09:48 | 2022-12-27 17:09:48 |
| 6 | 1 | 本 | 1000 | 25 | 2022-12-27 17:10:12 | 2022-12-27 17:10:12 |
| 2 | 2 | 時計 | 1500 | 1 | 2022-12-27 17:08:45 | 2022-12-27 17:08:45 |
| 7 | 2 | 時計 | 1500 | 3 | 2022-12-27 17:10:23 | 2022-12-27 17:10:23 |
| 3 | 3 | 加湿器 | 3000 | 3 | 2022-12-27 17:09:25 | 2022-12-27 17:09:25 |
| 4 | 4 | 土鍋 | 2700 | 5 | 2022-12-27 17:09:38 | 2022-12-27 17:09:38 |
+----+------------+------+-------+----------+---------------------+---------------------+
7 rows in set (0.05 sec)
mysql> また、products.id as product_idのようにasで別名を付けることができます。
selectの後に表示したいカラムを指定して、わかりやすくしてみました。
これでidが注文番号でどの製品をいつ、どれだけ注文したかがわかります。
もし、テーブル名が長くなってしまって、sql文が書きずらい場合は別名を付けて短く表現することもできます。
mysql> select B.id, A.id as product_id, A.name, A.price, B.quantity, B.updated_at, B.created_at from products A inner join orders B on A.id = B.product_id;
+----+------------+------+-------+----------+---------------------+---------------------+
| id | product_id | name | price | quantity | updated_at | created_at |
+----+------------+------+-------+----------+---------------------+---------------------+
| 1 | 1 | 本 | 1000 | 2 | 2022-12-27 17:08:36 | 2022-12-27 17:08:36 |
| 5 | 1 | 本 | 1000 | 10 | 2022-12-27 17:09:48 | 2022-12-27 17:09:48 |
| 6 | 1 | 本 | 1000 | 25 | 2022-12-27 17:10:12 | 2022-12-27 17:10:12 |
| 2 | 2 | 時計 | 1500 | 1 | 2022-12-27 17:08:45 | 2022-12-27 17:08:45 |
| 7 | 2 | 時計 | 1500 | 3 | 2022-12-27 17:10:23 | 2022-12-27 17:10:23 |
| 3 | 3 | 加湿器 | 3000 | 3 | 2022-12-27 17:09:25 | 2022-12-27 17:09:25 |
| 4 | 4 | 土鍋 | 2700 | 5 | 2022-12-27 17:09:38 | 2022-12-27 17:09:38 |
+----+------------+------+-------+----------+---------------------+---------------------+
7 rows in set (0.03 sec)
mysql> この例ではproductsにA、ordersにBという名前を付け置き換えています。
このようにテーブル同士の外部参照キー（どちらにもある共有のキー）で、データが同じものだけ（今回でいうとproductsのキーとordersのproduct_idが同じもの）を結合することを「内部結合（もしくはINNER JOIN）」と呼びます。
テーブル名の省略
テーブルを結合する際、どちらか片方にしかないカラムはテーブル名を省略することができます。
mysql> select B.id, A.id as product_id, name, price, quantity, B.updated_at, B.created_at from products A inner join orders B on A.id = B.product_id;
+----+------------+------+-------+----------+---------------------+---------------------+
| id | product_id | name | price | quantity | updated_at | created_at |
+----+------------+------+-------+----------+---------------------+---------------------+
| 1 | 1 | 本 | 1000 | 2 | 2022-12-27 17:08:36 | 2022-12-27 17:08:36 |
| 5 | 1 | 本 | 1000 | 10 | 2022-12-27 17:09:48 | 2022-12-27 17:09:48 |
| 6 | 1 | 本 | 1000 | 25 | 2022-12-27 17:10:12 | 2022-12-27 17:10:12 |
| 2 | 2 | 時計 | 1500 | 1 | 2022-12-27 17:08:45 | 2022-12-27 17:08:45 |
| 7 | 2 | 時計 | 1500 | 3 | 2022-12-27 17:10:23 | 2022-12-27 17:10:23 |
| 3 | 3 | 加湿器 | 3000 | 3 | 2022-12-27 17:09:25 | 2022-12-27 17:09:25 |
| 4 | 4 | 土鍋 | 2700 | 5 | 2022-12-27 17:09:38 | 2022-12-27 17:09:38 |
+----+------------+------+-------+----------+---------------------+---------------------+
7 rows in set (0.03 sec)
mysql> name、price、quantityはどちらか片方にしかないカラムなのでテーブルを省略しても認識してくれます。
外部結合
内部結合はproductsのidとordersのproduct_idが一致するもののみ結合することができました。
ただ今回のproductsとodersの関係から、注文が入っていない商品の情報を知りたい場合もあると考えられます。
外部結合では、片方のテーブルにしかない情報も表示することができます。
左外部結合
mysql> select B.id, A.id as product_id, name, price, quantity, B.updated_at, B.created_at from products A left join orders B on A.id = B.product_id;
+------+------------+-------------+-------+----------+---------------------+---------------------+
| id | product_id | name | price | quantity | updated_at | created_at |
+------+------------+-------------+-------+----------+---------------------+---------------------+
| 1 | 1 | 本 | 1000 | 2 | 2022-12-27 17:08:36 | 2022-12-27 17:08:36 |
| 5 | 1 | 本 | 1000 | 10 | 2022-12-27 17:09:48 | 2022-12-27 17:09:48 |
| 6 | 1 | 本 | 1000 | 25 | 2022-12-27 17:10:12 | 2022-12-27 17:10:12 |
| 2 | 2 | 時計 | 1500 | 1 | 2022-12-27 17:08:45 | 2022-12-27 17:08:45 |
| 7 | 2 | 時計 | 1500 | 3 | 2022-12-27 17:10:23 | 2022-12-27 17:10:23 |
| 3 | 3 | 加湿器 | 3000 | 3 | 2022-12-27 17:09:25 | 2022-12-27 17:09:25 |
| 4 | 4 | 土鍋 | 2700 | 5 | 2022-12-27 17:09:38 | 2022-12-27 17:09:38 |
| NULL | 5 | 扇風機 | 3800 | NULL | NULL | NULL |
| NULL | 6 | エビオス錠2000錠 | 3200 | NULL | NULL | NULL |
+------+------------+-------------+-------+----------+---------------------+---------------------+
9 rows in set (0.06 sec)
mysql> この結合でproduct_id 5の扇風機とproduct_id 6のエビオス錠2000錠の注文がないことがわかります。
内部結合と似ていますが、「INNER JOIN」が「LEFT JOIN」に変わっています。
このように指定すると、はじめに指定したテーブルの全データと2番目に指定したテーブルの外部キーが一致するもの(productsのidとodersのproduct_id）で結合できるデータが取得されます。
このような外部結合を「左外部結合」と呼びます。
右外部結合
次は左の反対、右外部結合です。
ためしに先ほどの左外部結合のsql文をそのままright joinに変更してみます。
mysql> select B.id, A.id as product_id, name, price, quantity, B.updated_at, B.created_at from products A right join orders B on A.id = B.product_id;
+----+------------+------+-------+----------+---------------------+---------------------+
| id | product_id | name | price | quantity | updated_at | created_at |
+----+------------+------+-------+----------+---------------------+---------------------+
| 1 | 1 | 本 | 1000 | 2 | 2022-12-27 17:08:36 | 2022-12-27 17:08:36 |
| 2 | 2 | 時計 | 1500 | 1 | 2022-12-27 17:08:45 | 2022-12-27 17:08:45 |
| 3 | 3 | 加湿器 | 3000 | 3 | 2022-12-27 17:09:25 | 2022-12-27 17:09:25 |
| 4 | 4 | 土鍋 | 2700 | 5 | 2022-12-27 17:09:38 | 2022-12-27 17:09:38 |
| 5 | 1 | 本 | 1000 | 10 | 2022-12-27 17:09:48 | 2022-12-27 17:09:48 |
| 6 | 1 | 本 | 1000 | 25 | 2022-12-27 17:10:12 | 2022-12-27 17:10:12 |
| 7 | 2 | 時計 | 1500 | 3 | 2022-12-27 17:10:23 | 2022-12-27 17:10:23 |
+----+------------+------+-------+----------+---------------------+---------------------+
7 rows in set (0.03 sec)
mysql> このように指定すると、はじめに指定したテーブルの外部キーが一致するもの(productsのidとodersのproduct_id）と2番目に指定したテーブルの全データで結合できるデータが取得されます。
メインとするテーブルを右に指定するか左に指定するかの違いでしかないようです。
ちなみに、上記sqlのテーブルを入れ替えると
mysql> select B.id, A.id as product_id, name, price, quantity, B.updated_at, B.created_at from orders B right join products A on A.id = B.product_id;
+------+------------+-------------+-------+----------+---------------------+---------------------+
| id | product_id | name | price | quantity | updated_at | created_at |
+------+------------+-------------+-------+----------+---------------------+---------------------+
| 1 | 1 | 本 | 1000 | 2 | 2022-12-27 17:08:36 | 2022-12-27 17:08:36 |
| 5 | 1 | 本 | 1000 | 10 | 2022-12-27 17:09:48 | 2022-12-27 17:09:48 |
| 6 | 1 | 本 | 1000 | 25 | 2022-12-27 17:10:12 | 2022-12-27 17:10:12 |
| 2 | 2 | 時計 | 1500 | 1 | 2022-12-27 17:08:45 | 2022-12-27 17:08:45 |
| 7 | 2 | 時計 | 1500 | 3 | 2022-12-27 17:10:23 | 2022-12-27 17:10:23 |
| 3 | 3 | 加湿器 | 3000 | 3 | 2022-12-27 17:09:25 | 2022-12-27 17:09:25 |
| 4 | 4 | 土鍋 | 2700 | 5 | 2022-12-27 17:09:38 | 2022-12-27 17:09:38 |
| NULL | 5 | 扇風機 | 3800 | NULL | NULL | NULL |
| NULL | 6 | エビオス錠2000錠 | 3200 | NULL | NULL | NULL |
+------+------------+-------------+-------+----------+---------------------+---------------------+
9 rows in set (0.03 sec)
mysql>左外部結合と同じ動きになります。
3つ以上のテーブルの結合
テーブルの結合は3つ以上でも行うことができます。
追加で以下テーブルを用意します。
テーブルの結合は3つ以上でも行うことができます。
追加で以下テーブルを用意します。
mysql> DESC product_stores;
+---------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| product_id | int(10) unsigned | NO | MUL | NULL | |
| product_store | int(10) unsigned | NO | | NULL | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | |
+---------------+------------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.03 sec)
mysql> select * from product_stores;
+----+------------+---------------+---------------------+---------------------+
| id | product_id | product_store | updated_at | created_at |
+----+------------+---------------+---------------------+---------------------+
| 1 | 1 | 100 | 2022-12-27 18:23:35 | 2022-12-27 18:23:35 |
| 2 | 2 | 1000 | 2022-12-27 18:23:46 | 2022-12-27 18:23:46 |
| 3 | 3 | 55 | 2022-12-27 18:23:56 | 2022-12-27 18:23:56 |
| 4 | 4 | 70 | 2022-12-27 18:24:04 | 2022-12-27 18:24:04 |
| 5 | 5 | 80 | 2022-12-27 18:24:15 | 2022-12-27 18:24:15 |
| 6 | 6 | 9000 | 2022-12-27 18:24:26 | 2022-12-27 18:24:26 |
+----+------------+---------------+---------------------+---------------------+
6 rows in set (0.05 sec)3つのテーブルを結合します。
mysql> select B.id, A.id as product_id, name, price, product_store, quantity, B.updated_at, B.created_at from products A inner join orders B on A.id = B.product_id inner join product_stores C on A.id = C.product_id;
+----+------------+------+-------+---------------+----------+---------------------+---------------------+
| id | product_id | name | price | product_store | quantity | updated_at | created_at |
+----+------------+------+-------+---------------+----------+---------------------+---------------------+
| 1 | 1 | 本 | 1000 | 100 | 2 | 2022-12-27 17:08:36 | 2022-12-27 17:08:36 |
| 2 | 2 | 時計 | 1500 | 1000 | 1 | 2022-12-27 17:08:45 | 2022-12-27 17:08:45 |
| 3 | 3 | 加湿器 | 3000 | 55 | 3 | 2022-12-27 17:09:25 | 2022-12-27 17:09:25 |
| 4 | 4 | 土鍋 | 2700 | 70 | 5 | 2022-12-27 17:09:38 | 2022-12-27 17:09:38 |
| 5 | 1 | 本 | 1000 | 100 | 10 | 2022-12-27 17:09:48 | 2022-12-27 17:09:48 |
| 6 | 1 | 本 | 1000 | 100 | 25 | 2022-12-27 17:10:12 | 2022-12-27 17:10:12 |
| 7 | 2 | 時計 | 1500 | 1000 | 3 | 2022-12-27 17:10:23 | 2022-12-27 17:10:23 |
+----+------------+------+-------+---------------+----------+---------------------+---------------------+
7 rows in set (0.02 sec)終わりに
以上、内部結合と外部結合について実際に動かして知識を深めてみました。
ほかにも完全外部結合やクロス結合等種類があるので、そちらも実際に動かして挙動を確認してみたいと思います。
あわせて読みたい関連記事
執筆者プロフィール：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/