Blog Article
MySQLのテーブル結合について
MySQLのテーブル結合についてまとめました。
まずは、シンプルなテーブルを作成し、その中で使われる各要素について説明します。
users テーブルと 日報 (reports) テーブルを結合する形で、各ユーザーが日報を投稿できるようにします。
テーブルの作成とデータ挿入
usersテーブルの作成
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
age INT,
);id INT AUTO_INCREMENT PRIMARY KEY- idはユーザーごとの固有の識別番号 (主キー)
AUTO_INCREMENTにより、新しいユーザーを追加するたびに自動で連番が付与されます。- 主キーとして指定されているため、同じ値を持つデータは存在できません。
name VARCHAR(100) NOT NULL- nameはユーザーの名前を保存するカラム。
VARCHAR(100)は、最大100文字の文字列を保存できることを意味します。NOT NULLにより、名前が必ず入力されなければならない制約があります。
email VARCHAR(100), UNIQUE- emailはユーザーのメールアドレスを保存するカラム。
VARCHAR(100)で最大100文字の文字列を保存可能です。UNIQUE制約により、同じメールアドレスを複数のユーザーが登録することはできません。
age INT- ageはユーザーの年齢を保存するカラム。
- 数値 (整数型) で保存されます。
テーブルの詳細をDESC テーブル名で表示します。usersテーブルのデータは前回のデータをそのまま使っています。
DESC users;
+------------+--------------+------+-----+------------+--------------------+
| Field | Type | Null | Key | Default | Extra | |
+------------+--------------+------+-----+------------+--------------------+
| id | int | NO | PRI | NULL | auto_increment | |
| name | varchar(100) | NO | | NULL | | |
| email | varchar(100) | YES | UNI | NULL | | |
| age | int | YES | | NULL | | |
+------------+--------------+------+-----+------------+- ------------------+reportsテーブルを作成する
CREATE TABLE reports (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
report_date DATE NOT NULL,
content TEXT NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);id: 日報ごとのユニークなIDuser_id: 日報を投稿したユーザーのID。usersテーブルのidを参照する**外部キー (FOREIGN KEY)**になっている。report_date: 日報の日付content: 日報の内容
reportsテーブルにデータを挿入
INSERT INTO reports (user_id, report_date, content)
VALUES
(1, '2024-01-01', '今日はプロジェクトの仕様を確認した。'),
(1, '2024-01-02', 'プログラムのバグを修正した。'),
(2, '2024-01-01', 'クライアントのミーティングに参加。'),
(3, '2024-01-03', '新しい機能の設計を行った。'),
(4, '2024-01-02', 'データベースの最適化を行った。'),
(5, '2024-01-03', 'APIのテストを実施。'),
(6, '2024-01-04', 'レポートのまとめを作成した。'),
(7, '2024-01-05', 'システムのリリース準備を進めた。');データ挿入後に確認をします。これらのデータを使って結合します。
SELECT * FROM users;
+----+--------------+----------------------+------+
| id | name | email | age |
+----+--------------+----------------------+------+
| 1 | 山田太郎 | yamada@example.com | 21 |
| 2 | 佐藤大樹 | sato@example.com | 26 |
| 3 | 山本早紀 | yamamoto@example.com | 28 |
| 4 | 田中瑞希 | tanaka@example.com | 30 |
| 5 | 工藤高次 | kudo@example.com | 40 |
| 6 | 石田裕子 | isida@example.com | 48 |
| 7 | 加藤裕二 | kato@example.com | 55 |
+----+--------------+----------------------+------+SELECT * FROM reports;
+----+---------+-------------+--------------------------------------------------------+
| id | user_id | report_date | content |
+----+---------+-------------+--------------------------------------------------------+
| 25 | 1 | 2024-01-01 | 今日はプロジェクトの仕様を確認した。 |
| 26 | 1 | 2024-01-02 | プログラムのバグを修正した。 |
| 27 | 2 | 2024-01-01 | クライアントのミーティングに参加。 |
| 28 | 3 | 2024-01-03 | 新しい機能の設計を行った。 |
| 29 | 4 | 2024-01-02 | データベースの最適化を行った。 |
| 30 | 5 | 2024-01-03 | APIのテストを実施。 |
| 31 | 6 | 2024-01-04 | レポートのまとめを作成した。 |
| 32 | 7 | 2024-01-05 | システムのリリース準備を進めた。 |
+----+---------+-------------+--------------------------------------------------------+テーブルの結合 (JOIN)
usersテーブルとreportsテーブルを結合して、ユーザーごとの日報を取得します。
INNER JOIN (内部結合)
INNER JOINは、両方のテーブルに共通するデータだけを取得する方法です。結合するコードを記述します。
SELECT users.id, users.name, reports.reports_date, reports.content
FROM users
INNER JOIN reports ON users.id = reports.user_id;SELECT:SELECTは、データベースからどんな情報を取り出すかを指定します。ここではusers.id(ユーザーのID)、users.name(ユーザーの名前)、reports.report_date(レポートの日付)、reports.content(ポートの内容)を取り出すことを指定しています。FROM users:FROMは、データを取り出す元のテーブルを指定します。ここではusersテーブルから情報を取り出しています。INNER JOIN reports ON users.id = reports.user_id:ON users.id = reports.user_idは、結合の条件を指定しています。つまり、usersテーブルのidとreportsテーブルのuser_idが一致するデータを結びつけるということです。この条件に一致するユーザーとレポートが1つの結果にまとめられます。
結合後のデータ
usersテーブルと reportsテーブルが idを基準に結びつけられ、日報を書いたユーザーの情報を取得できました。
+----+--------------+-------------+-----------------------------------------------+
| id | name | report_date | content |
+----+--------------+-------------+-----------------------------------------------+
| 1 | 山田太郎 | 2024-01-01 | 今日はプロジェクトの仕様を確認した。 |
| 1 | 山田太郎 | 2024-01-02 | プログラムのバグを修正した。 |
| 2 | 佐藤大樹 | 2024-01-01 | クライアントのミーティングに参加。 |
| 3 | 山本早紀 | 2024-01-03 | 新しい機能の設計を行った。 |
| 4 | 田中瑞希 | 2024-01-02 | データベースの最適化を行った。 |
| 5 | 工藤高次 | 2024-01-03 | APIのテストを実施。 |
| 6 | 石田裕子 | 2024-01-04 | レポートのまとめを作成した。 |
| 7 | 加藤裕二 | 2024-01-05 | システムのリリース準備を進めた。 |
+----+--------------+-------------+-----------------------------------------------+
LEFT JOIN (左外部結合)
LEFT JOINは、左側 (usersテーブル) のデータをすべて取得し、対応するreportsのデータがない場合は NULL を返します。
SELECT users.id, users.name, reports.report_date, reports.content
FROM users
LEFT JOIN reports ON users.id = reports.user_id;実行結果
reportsテーブルに対応するデータがない場合、report_dateとcontentが NULLになります。今回は、全員がレポートを書いているのでこのような結果になります。
+----+--------------+-------------+-----------------------------------------------+
| id | name | report_date | content |
+----+--------------+-------------+-----------------------------------------------+
| 1 | 山田太郎 | 2024-01-01 | 今日はプロジェクトの仕様を確認した。 |
| 1 | 山田太郎 | 2024-01-02 | プログラムのバグを修正した。 |
| 2 | 佐藤大樹 | 2024-01-01 | クライアントのミーティングに参加。 |
| 3 | 山本早紀 | 2024-01-03 | 新しい機能の設計を行った。 |
| 4 | 田中瑞希 | 2024-01-02 | データベースの最適化を行った。 |
| 5 | 工藤高次 | 2024-01-03 | APIのテストを実施。 |
| 6 | 石田裕子 | 2024-01-04 | レポートのまとめを作成した。 |
| 7 | 加藤裕二 | 2024-01-05 | システムのリリース準備を進めた。 |
+----+--------------+-------------+-----------------------------------------------+RIGHT JOIN (右外部結合)
RIGHT JOINは、右側 (reportsテーブル) のデータをすべて取得し、対応するusersのデータがない場合は NULLを返します。
SELECT users.id, users.name, reports.report_date, reports.content
FROM users
RIGHT JOIN reports ON users.id = reports.user_id;このケースでは、reportsテーブルにデータがあるすべてのユーザーを取得します。