SQLについて基本的な操作をまとめました。こちらではテーブルの結合についてまとめています。
DB作成と基本操作についてはこちらから
SQL ~ DB 作成から基本操作、テーブルの結合まで ~ DB 作成編
SQL ~ DB 作成から基本操作、テーブルの結合まで ~ 基本操作編
各テーブル情報
物品管理のDBを作成しました。
現在の勤務先の物品やらに依存しているので、あまり一般的な DB で無いですがご了承ください。
wheelchair（車椅子） テーブル
mysql> select * from wheelchair;
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
| id | name | type | money | purchase | floor_id | occupant_id | created_at |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
| 1 | low_wheelchair | wheelchair | 10000 | NULL | 1 | 1 | 2020-10-16 10:16:53 |
| 2 | wheelchair | wheelchair | 10000 | NULL | 1 | 2 | 2020-10-16 10:17:48 |
| 3 | action_3 | modular | NULL | NULL | 2 | 3 | 2020-10-16 10:19:08 |
| 4 | rebo | modular | NULL | NULL | 3 | 4 | 2020-10-16 10:20:04 |
| 5 | mytilt | tilt_ant_recline | NULL | NULL | 4 | 5 | 2020-10-16 10:22:33 |
| 6 | mytilt_pro | tilt_ant_recline | NULL | NULL | 5 | 6 | 2020-10-16 10:23:11 |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
floor デーブル
+----+------------+
| id | floor_name |
+----+------------+
| 1 | AB3F |
| 2 | AB4F |
| 3 | AB2F |
| 4 | C2F |
| 5 | C3F |
+----+------------+
occupant テーブル
mysql> select * from occupant;
+----+---------------+------+------+----------+---------------------+
| id | occupant_name | sex | age | floor_id | created_at |
+----+---------------+------+------+----------+---------------------+
| 1 | 鈴木 | NULL | NULL | 1 | 2020-10-16 10:04:25 |
| 2 | 田中 | NULL | NULL | 1 | 2020-10-16 10:05:20 |
| 3 | 佐藤 | NULL | NULL | 2 | 2020-10-16 10:05:36 |
| 4 | 後藤 | NULL | NULL | 3 | 2020-10-16 10:05:58 |
| 5 | 加藤 | NULL | NULL | 4 | 2020-10-16 10:06:22 |
| 6 | 村田 | NULL | NULL | 5 | 2020-10-16 10:06:36 |
+----+---------------+------+------+----------+---------------------+
内部結合 INNER JOIN
基本構文
SELECT カラム名1, カラム名2, ... FROM テーブル名１
INNER JOIN テーブル名2 ON 結合の条件
mysql> SELECT * FROM wheelchair INNER JOIN floor ON wheelchair.floor_id = floor.id;
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+----+------------+
| id | name | type | money | purchase | floor_id | occupant_id | created_at | id | floor_name |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+----+------------+
| 1 | low_wheelchair | wheelchair | 10000 | NULL | 1 | 1 | 2020-10-16 10:16:53 | 1 | AB3F |
| 2 | wheelchair | wheelchair | 10000 | NULL | 1 | 2 | 2020-10-16 10:17:48 | 1 | AB3F |
| 3 | action_3 | modular | NULL | NULL | 2 | 3 | 2020-10-16 10:19:08 | 2 | AB4F |
| 4 | rebo | modular | NULL | NULL | 3 | 4 | 2020-10-16 10:20:04 | 3 | AB2F |
| 5 | mytilt | tilt_ant_recline | NULL | NULL | 4 | 5 | 2020-10-16 10:22:33 | 4 | C2F |
| 6 | mytilt_pro | tilt_ant_recline | NULL | NULL | 5 | 6 | 2020-10-16 10:23:11 | 5 | C3F |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+----+------------+
6 rows in set (0.00 sec)
wheelchair テーブルの全レコードと floor テーブルを id で紐付けて結合している。
mysql> SELECT floor_id, floor_name FROM wheelchair JOIN floor ON wheelchair.floor_id = floor.id;
+----------+------------+
| floor_id | floor_name |
+----------+------------+
| 1 | AB3F |
| 1 | AB3F |
| 2 | AB4F |
| 3 | AB2F |
| 4 | C2F |
| 5 | C3F |
+----------+------------+
wheelchair テーブルの floor_id と floor テーブルの id を結合。
JOIN のみにしているが INNER JOIN と同じ意味。
外部結合 OUTER JOIN
基本構文
SELECT カラム名1, カラム名2, ...,FROM テーブル名1
LEFT(RIGHT) OUTER JOIN テーブル名2 ON 結合の条件
LEFT OUTER JOIN と RIGHT OUTER JOIN
SQL 文の FROM 〜 の文で左側(LEFT)のテーブルをメインとするか右側(RIGHT)のテーブルをメインとするかの違い。基本構文の例で言うと、LEFT OUTER JOIN にすると「テーブル名１」をメインとする。RIGHT OUTER JOIN とすると「テーブル名２」をメインとする。
わかりやすくするため、occupant テーブルに foor_id を入力していない値を設定。
mysql> select * from occupant;
+----+---------------+------+------+----------+---------------------+
| id | occupant_name | sex | age | floor_id | created_at |
+----+---------------+------+------+----------+---------------------+
| 1 | 鈴木 | NULL | NULL | 1 | 2020-10-16 10:04:25 |
| 2 | 田中 | NULL | NULL | 1 | 2020-10-16 10:05:20 |
| 3 | 佐藤 | NULL | NULL | 2 | 2020-10-16 10:05:36 |
| 4 | 後藤 | NULL | NULL | 3 | 2020-10-16 10:05:58 |
| 5 | 加藤 | NULL | NULL | 4 | 2020-10-16 10:06:22 |
| 6 | 村田 | NULL | NULL | 5 | 2020-10-16 10:06:36 |
| 7 | 鈴木 | NULL | NULL | NULL | 2020-10-16 14:32:24 |
+----+---------------+------+------+----------+---------------------+
LEFT OUTER JOIN 文
mysql> SELECT occupant_name, floor_id, floor_name FROM occupant LEFT OUTER JOIN floor ON occupant.floor_id = floor.id;
+---------------+----------+------------+
| occupant_name | floor_id | floor_name |
+---------------+----------+------------+
| 鈴木 | 1 | AB3F |
| 田中 | 1 | AB3F |
| 佐藤 | 2 | AB4F |
| 後藤 | 3 | AB2F |
| 加藤 | 4 | C2F |
| 村田 | 5 | C3F |
| 鈴木 | NULL | NULL |
+---------------+----------+------------+
occupant テーブルの foor_id と floor テーブルの id を紐づけて表示。内部結合と違うのは、occupant テーブルに保存されているデータを全て表示していて、紐付かれていないレコードに関しては NULL を返してくること。
以下は内部結合をした結果。
mysql> SELECT occupant_name, floor_id, floor_name FROM occupant INNER JOIN floor ON occupant.floor_id = floor.id;
+---------------+----------+------------+
| occupant_name | floor_id | floor_name |
+---------------+----------+------------+
| 鈴木 | 1 | AB3F |
| 田中 | 1 | AB3F |
| 佐藤 | 2 | AB4F |
| 後藤 | 3 | AB2F |
| 加藤 | 4 | C2F |
| 村田 | 5 | C3F |
+---------------+----------+------------+
紐付かれていない（floor_id が入力されていない）レコードは表示されない。
RIGHT OUTER JOIN 文
比較のため、floor テーブルに未所属レコードを追加
mysql> select * from floor;
+----+------------+
| id | floor_name |
+----+------------+
| 1 | AB3F |
| 2 | AB4F |
| 3 | AB2F |
| 4 | C2F |
| 5 | C3F |
| 6 | 未所属 |
+----+------------+
mysql> SELECT occupant_name, floor_id, floor_name FROM occupant RIGHT OUTER JOIN floor ON occupant.floor_id = floor.id;
+---------------+----------+------------+
| occupant_name | floor_id | floor_name |
+---------------+----------+------------+
| 鈴木 | 1 | AB3F |
| 田中 | 1 | AB3F |
| 佐藤 | 2 | AB4F |
| 後藤 | 3 | AB2F |
| 加藤 | 4 | C2F |
| 村田 | 5 | C3F |
| NULL | NULL | 未所属 |
+---------------+----------+------------+
floor テーブルをメインとし、先ほどと同じように occupant テーブルの floor_id と floor テーブルの id を紐付けて表示。floor テーブルをメインとしているため、未所属レコードが表示され、occupant_name, floor_id に紐付かれている値がないため NULL が表示される。
複数のテーブルを結合させて表示
長いので改行しています。
SELECT occupant_name, floor_name, name
FROM wheelchair
LEFT OUTER JOIN floor
ON wheelchair.floor_id = floor.id
LEFT OUTER JOIN occupant
ON wheelchair.occupant_id = occupant.id;
少しややこしいが JOIN 文をつなぎ合わせているだけ。
実行結果。
mysql> SELECT occupant_name, floor_name, name FROM wheelchair LEFT OUTER JOIN floor ON wheelchair.floor_id = floor.id LEFT OUTER JOIN occupant ON wheelchair.occupant_id = occupant.id;
+---------------+------------+----------------+
| occupant_name | floor_name | name |
+---------------+------------+----------------+
| 鈴木 | AB3F | low_wheelchair |
| 田中 | AB3F | wheelchair |
| 佐藤 | AB4F | action_3 |
| 後藤 | AB2F | rebo |
| 加藤 | C2F | mytilt |
| 村田 | C3F | mytilt_pro |
+---------------+------------+----------------+
where 句で検索可能。
mysql> mysql> SELECT occupant_name, floor_name, name FROM wheelchair LEFT OUTER JOIN floor ON wheelchair.floor_id = floor.id LER JOIN occupant ON wheelchair.occupant_id = occupant.id where wheelchair.floor_id = 1;
+---------------+------------+----------------+
| occupant_name | floor_name | name |
+---------------+------------+----------------+
| 鈴木 | AB3F | low_wheelchair |
| 田中 | AB3F | wheelchair |
+---------------+------------+----------------+