💾

SQLのJOIN句をなんとなくで使ってたのでちゃんと理解する

2025/02/04に公開

きっかけ

こんにちは。
東京でフルスタックエンジニアをしてます。
今回、バックエンドでなんとなくの理解で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