🐕

SQLのINNER / OUTER JOINについて

2023/11/16に公開

SQL

join

tech

背景

しばらくコードを書いていなかったこともあり、JOINの概念をすっかり忘れてしまった。ハンズオンして、徐々に思い出したので、備忘録的に記事を作成

概要

JOINはテーブル間の結合をする際に使用される。今回はハンズオンをしながら、INNER / OUTER JOINについて説明する。

ハンズオン

データ準備

ハンズオンを始めるため、まず下記テーブルを準備する。

従業員テーブル (employees)
- employee_id: 従業員のID
- employee_name: 従業員の名前
- department_id: 従業員が属する部署のID
部署テーブル (departments)
- department_id: 部署のID
- department_name: 部署の名前

上記テーブルは、下記コードで作成できる。

従業員テーブル (employees) の作成

CREATE TABLE employees (
  employee_id INT,
  employee_name VARCHAR(100),
  department_id INT
);

従業員テーブル (employees) へのデータ挿入

INSERT INTO employees (employee_id, employee_name, department_id) VALUES
(1, '山田太郎', 1),
(2, '鈴木花子', 2),
(3, '田中一郎', 3);

部署テーブル (departments) の作成

CREATE TABLE departments (
  department_id INT,
  department_name VARCHAR(100)
);

部署テーブル (departments) へのデータ挿入

INSERT INTO departments (department_id, department_name) VALUES
(1, '営業部'),
(2, '人事部'),
(4, '研究開発部');

INNER JOIN

上記内容でデータ準備ができたので、以下の通りINNER JOINでテーブルを結合する。
表示内容として、従業員テーブルの従業員名、部署テーブルの部署名を選択する。INNER JOINにより、両方のテーブルでdepartment_idが一致する行のみを結合する。

SELECT 
  employees.employee_name, 
  departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

そうすると、以下の通りデータ表示される。
employee_name | department_name
---------------+-----------------
山田太郎 | 営業部
鈴木花子 | 人事部
(2 rows)

OUTER JOIN

OUTER JOINは、LEFT OUTER JOINとRIGHT OUTER JOINがあるので、それぞれ説明する。

LEFT OUTER JOIN

以下例では、左のテーブル（この例ではemployees）のすべての行と、右のテーブル（この例ではdepartments）で一致する行を結合する。

SELECT 
  employees.employee_id,
  employees.employee_name,
  departments.department_name
FROM employees
LEFT OUTER JOIN departments
ON employees.department_id = departments.department_id;

上記内容では、従業員テーブルから従業員idと従業員名、部署テーブルから部署名を出力している。そして、従業員テーブルからはすべての行を出力して、双方のdepartment_idが一致した場合は、department_nameも出力される。

今回の場合、田中一郎のdepartment_idは3で、それに一致するdepartment_idは部署テーブルにない。そのため、田中一郎のdepartment_nameはnullとなる。

RIGHT OUTER JOIN

SELECT 
  employees.employee_id,
  employees.employee_name,
  departments.department_name
FROM employees
RIGHT OUTER JOIN departments
ON employees.department_id = departments.department_id;

RIGHT OUTER JOINの場合、上記ケースとは逆になり、部署テーブルからはすべての行を出力して、双方のdepartment_idが一致した場合は、employee_idとemployee_nameも出力される。

今回の場合、営業部、人事部は、それぞれ山田太郎、鈴木花子と一致する。一方で、研究開発部は従業員テーブルの誰とも一致しない。

応用編

三つのテーブルを使い、そのうち２つを使って、INNER / OUTER JOINを実行する例を見る。まずは、新たにプロジェクトテーブルを作成する。

プロジェクトテーブル (projects) の作成

CREATE TABLE projects (
  project_id INT,
  project_name VARCHAR(100),
  employee_id INT
);

プロジェクトテーブルへのデータ挿入

INSERT INTO projects (project_id, project_name, employee_id) VALUES
(1, 'プロジェクトA', 1),
(2, 'プロジェクトB', 2),
(3, 'プロジェクトC', 4); -- この従業員IDはemployeesテーブルには存在しない

OUTER JOIN

では下記のように、OUTER JOINを使うとどうなるか。

SELECT 
  employees.employee_name,
  departments.department_name,
  projects.project_name
FROM employees
LEFT OUTER JOIN departments
ON employees.department_id = departments.department_id
LEFT OUTER JOIN projects
ON employees.employee_id = projects.employee_id;

まず従業員テーブルからはすべての行が出力される。そして、department_idが一致した場合は部署テーブルから、employee_idが一致した場合はプロジェクトテーブルからデータが出力される。

INNER JOIN

では、INNER JOINの場合はどうなるか。

SELECT 
  employees.employee_name,
  departments.department_name,
  projects.project_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id
INNER JOIN projects
ON employees.employee_id = projects.employee_id;

この場合、部署テーブルでdepartment_idが一致、プロジェクトテーブルでemployee_idが一致という両方の条件が満たされた場合のみ、データが出力される。

背景

概要

ハンズオン

データ準備

INNER JOIN

OUTER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

応用編

OUTER JOIN

INNER JOIN

Discussion