テーブルの結合(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を使うのがおすすめです。