各テーブル情報

物品管理の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     |
+---------------+------------+----------------+