SQLのJOIN句をなんとなくで使ってたのでちゃんと理解する
きっかけ
こんにちは。
東京でフルスタックエンジニアをしてます。
今回、バックエンドでなんとなくの理解でJOIN句を理解していたので、デバッグの際になぜこの出力になるのかちゃんと理解できず困りました。
今回はちゃんとこの曖昧な部分を理解するためにリマインドの意味を込めてJOIN句を復習しました。
今回使うテーブルの例
Employees
employee_id | employee_name | department_id |
---|---|---|
1 | John Smith | 101 |
2 | Mary Johnson | 102 |
3 | Sam Brown | 103 |
4 | Lisa White | NULL |
Departments
department_id | department_name |
---|---|
101 | HR |
102 | Finance |
103 | Engineering |
104 | Marketing |
テーブル作成とデータ挿入
まず、クエリを実行してテーブルを作成し、データを挿入します。
-- Departmentsテーブルの作成
CREATE TABLE Departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL
);
-- Employeesテーブルの作成
CREATE TABLE Employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50) NOT NULL,
department_id INT,
FOREIGN KEY (department_id) REFERENCES Departments(department_id) ON DELETE SET NULL
);
-- Departmentsテーブルにデータを挿入(先に実行)
INSERT INTO Departments (department_id, department_name)
VALUES
(101, 'HR'),
(102, 'Finance'),
(103, 'Engineering'),
(104, 'Marketing');
-- Employeesテーブルにデータを挿入(後に実行)
INSERT INTO Employees (employee_id, employee_name, department_id)
VALUES
(1, 'John Smith', 101),
(2, 'Mary Johnson', 102),
(3, 'Sam Brown', 103),
(4, 'Lisa White', NULL);
1. INNER JOIN
INNER JOINは、両方のテーブルで一致するレコードのみを返します。
クエリ
SELECT e.employee_id, e.employee_name, d.department_name
FROM Employees e
INNER JOIN Departments d ON e.department_id = d.department_id;
出力
employee_id | employee_name | department_name |
---|---|---|
1 | John Smith | HR |
2 | Mary Johnson | Finance |
3 | Sam Brown | Engineering |
2. LEFT JOIN
LEFT JOINは、左側のテーブルのすべてのレコードと、右側のテーブルの一致するレコードを返します。右側に一致するレコードがない場合はNULLが返されます。
左側というのはFROMの後に割り当てられたテーブルのこと(Employees)を指します。
クエリ
SELECT e.employee_id, e.employee_name, d.department_name
FROM Employees e
LEFT JOIN Departments d ON e.department_id = d.department_id;
出力
employee_id | employee_name | department_name |
---|---|---|
1 | John Smith | HR |
2 | Mary Johnson | Finance |
3 | Sam Brown | Engineering |
4 | Lisa White | NULL |
3. RIGHT JOIN
RIGHT JOINは、右側のテーブルのすべてのレコードと、左側のテーブルの一致するレコードを返します。左側に一致するレコードがない場合はNULLが返されます。
右側というのはJOINされたテーブルのこと(Departments )を指します。
クエリ
SELECT e.employee_id, e.employee_name, d.department_name
FROM Employees e
RIGHT JOIN Departments d ON e.department_id = d.department_id;
出力
employee_id | employee_name | department_name |
---|---|---|
1 | John Smith | HR |
2 | Mary Johnson | Finance |
3 | Sam Brown | Engineering |
NULL | NULL | Marketing |
4. FULL OUTER JOIN
FULL OUTER JOINは、両方のテーブルのすべてのレコードを返し、一致しないレコードにはNULLが返されます。
クエリ
SELECT e.employee_id, e.employee_name, d.department_name
FROM Employees e
FULL OUTER JOIN Departments d ON e.department_id = d.department_id;
出力
employee_id | employee_name | department_name |
---|---|---|
1 | John Smith | HR |
2 | Mary Johnson | Finance |
3 | Sam Brown | Engineering |
4 | Lisa White | NULL |
NULL | NULL | Marketing |
5. CROSS JOIN
CROSS JOINは、2つのテーブルのすべての組み合わせを生成します。
クエリ
SELECT e.employee_id, e.employee_name, d.department_id, d.department_name
FROM Employees e
CROSS JOIN Departments d;
出力
employee_id | employee_name | department_id | department_name |
---|---|---|---|
1 | John Smith | 101 | HR |
1 | John Smith | 102 | Finance |
1 | John Smith | 103 | Engineering |
1 | John Smith | 104 | Marketing |
2 | Mary Johnson | 101 | HR |
... | ... | ... | ... |
4 | Lisa White | 104 | Marketing |
まとめ
各JOINの動作をしっかり理解することで、デバッグ時の出力結果の理由を明確に把握できます。INNER JOINは一致するレコードのみを返し、LEFT JOINは左側のすべてのレコードを保持、RIGHT JOINは右側のすべてのレコードを保持、FULL OUTER JOINは両テーブルのすべてのレコードを返します。CROSS JOINは全組み合わせを生成するため、使用用途に応じて適切なJOINを選択しましょう。
Discussion