テーブルの結合(JOIN)
今までは一つのテーブルに対しての操作を扱ってきましたが、JOINを使用することで複数のテーブルを結合して出力できます。
1. テストデータの準備
結合を試すために、employee(社員)とdepartment(部署)の2つのテーブルを作成します。
CREATE TABLE employee (
id INT,
name VARCHAR(10),
department_id INT
);
CREATE TABLE department (
id INT,
name VARCHAR(10)
);
employeeテーブルに3件のレコードを追加します。
INSERT INTO employee (id, name, department_id) VALUES (1, 'satou', 10);
INSERT INTO employee (id, name, department_id) VALUES (2, 'suzuki', 20);
INSERT INTO employee (id, name, department_id) VALUES (3, 'tanaka', 40);
departmentテーブルに3件のレコードを追加します。
INSERT INTO department (id, name) VALUES (10, 'system');
INSERT INTO department (id, name) VALUES (20, 'finance');
INSERT INTO department (id, name) VALUES (30, 'general');
実行後、2つのテーブルは以下の状態になります。
employeeテーブル
| id | name | department_id |
|---|---|---|
| 1 | satou | 10 |
| 2 | suzuki | 20 |
| 3 | tanaka | 40 |
departmentテーブル
| id | name |
|---|---|
| 10 | system |
| 20 | finance |
| 30 | general |
ここで注目すべき点は、employeeのtanaka(department_idが40)に対応する部署がdepartmentテーブルに存在しないことと、departmentのgeneral(idが30)に所属する社員がemployeeテーブルに存在しないことです。この不一致が、結合の種類によって結果にどう影響するかを見ていきます。
データが正しく登録されているか確認します。
SELECT * FROM employee;
+------+--------+---------------+
| id | name | department_id |
+------+--------+---------------+
| 1 | satou | 10 |
| 2 | suzuki | 20 |
| 3 | tanaka | 40 |
+------+--------+---------------+
3 rows in set (0.00 sec)
SELECT * FROM department;
+------+---------+
| id | name |
+------+---------+
| 10 | system |
| 20 | finance |
| 30 | general |
+------+---------+
3 rows in set (0.00 sec)
2. INNER JOIN(内部結合)
INNER JOINは、結合条件に対して両方のテーブルに存在するレコードのみを抽出します。
SELECT * FROM employee
INNER JOIN department
ON employee.department_id = department.id;
ONの後に結合条件を記載します。この場合は、employeeのdepartment_idとdepartmentのidが一致するレコードを結合しています。
+------+--------+---------------+------+---------+
| id | name | department_id | id | name |
+------+--------+---------------+------+---------+
| 1 | satou | 10 | 10 | system |
| 2 | suzuki | 20 | 20 | finance |
+------+--------+---------------+------+---------+
2 rows in set (0.00 sec)
employeeのdepartment_idが40のレコード(tanaka)は、departmentにidが40のレコードが存在しないため、抽出対象外となります。同様に、departmentのidが30のレコード(general)も抽出されません。両方のテーブルで一致するレコードのみが結果に含まれます。
| employee.id | employee.name | department_id | department.id | department.name |
|---|---|---|---|---|
| 1 | satou | 10 | 10 | system |
| 2 | suzuki | 20 | 20 | finance |
3. LEFT JOIN(左外部結合)
LEFT JOINは、左側(結合元)テーブルの全レコードを出力し、右側(結合先)テーブルのレコードが存在する場合のみ結合します。存在しない場合はNULLになります。
SELECT * FROM employee
LEFT JOIN department
ON employee.department_id = department.id;
+------+--------+---------------+------+---------+
| id | name | department_id | id | name |
+------+--------+---------------+------+---------+
| 1 | satou | 10 | 10 | system |
| 2 | suzuki | 20 | 20 | finance |
| 3 | tanaka | 40 | NULL | NULL |
+------+--------+---------------+------+---------+
3 rows in set (0.00 sec)
左側のemployeeテーブルの全レコードが表示されます。tanaka(department_idが40)は、該当する部署が存在しないため、department側のカラムがNULLになっています。
| employee.id | employee.name | department_id | department.id | department.name |
|---|---|---|---|---|
| 1 | satou | 10 | 10 | system |
| 2 | suzuki | 20 | 20 | finance |
| 3 | tanaka | 40 | NULL | NULL |
| 💡 ポイント |
|---|
NULLは「値が存在しない」ことを表すデータベース上の特別な値です。PythonでいうNoneと同じ概念です。LEFT JOINやRIGHT JOINでは、結合相手のレコードが見つからない場合に、そのカラムの値がNULLとして表示されます。 |
4. RIGHT JOIN(右外部結合)
RIGHT JOINは、LEFT JOINとは逆で、右側(結合先)テーブルの全レコードをベースに、左側(結合元)テーブルの存在するもののみを表示します。
SELECT * FROM employee
RIGHT JOIN department
ON employee.department_id = department.id;
+------+--------+---------------+------+---------+
| id | name | department_id | id | name |
+------+--------+---------------+------+---------+
| 1 | satou | 10 | 10 | system |
| 2 | suzuki | 20 | 20 | finance |
| NULL | NULL | NULL | 30 | general |
+------+--------+---------------+------+---------+
3 rows in set (0.00 sec)
右側のdepartmentテーブルの全件が表示され、employeeに該当レコードがないgeneralは、employee側のカラムがNULLになっています。
| employee.id | employee.name | department_id | department.id | department.name |
|---|---|---|---|---|
| 1 | satou | 10 | 10 | system |
| 2 | suzuki | 20 | 20 | finance |
| NULL | NULL | NULL | 30 | general |
| 💡 ポイント |
|---|
LEFT JOINとRIGHT JOINはどちらも外部結合ですが、実際にはLEFT JOINが使われることがほとんどです。SQLは左側のテーブルを起点に読むのが自然なため、「全件出力したいテーブルを左側(FROM句)に置いてLEFT JOINする」と読みやすくなります。RIGHT JOINでも同じ結果は得られますが、特別な理由がなければLEFT JOINを使うのがおすすめです。 |