【業務効率化】SQLで同一テーブルを2つの外部キーで結合してデータ分析を効率化

2024-04-02

同一テーブルの2つの外部キーで結合する方法

SQLで同一テーブルを2つの外部キーで結合するには、JOIN句を使用します。具体的には、ON句で結合条件を指定します。

例

以下のemployeesテーブルがあるとします。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  department_id INT,
  manager_id INT,
  name VARCHAR(255)
);

このテーブルには、従業員のID、所属部門ID、上司ID、名前が格納されています。

部門名と上司の名前を取得

従業員の部門名と上司の名前を取得するには、次のようなクエリを使用します。

SELECT
  e.name,
  d.name AS department_name,
  m.name AS manager_name
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN employees m ON e.manager_id = m.id;

このクエリは、employeesテーブルを2回結合します。

1回目は、employeesテーブルのdepartment_id列とdepartmentsテーブルのid列を結合します。

SELECT
  e.name,
  s.name AS subordinate_name
FROM employees e
JOIN employees s ON e.id = s.manager_id;

結合条件は、employeesテーブルのid列とemployeesテーブルのmanager_id列です。

その他の結合方法

上記以外にも、さまざまな結合方法があります。

INNER JOIN: 結合条件を満たすレコードのみを返します。
LEFT JOIN: 左側のテーブルのすべてのレコードを返し、右側テーブルとの一致レコードがあれば結合します。
FULL JOIN: 両方のテーブルのすべてのレコードを返し、一致レコードがない場合はNULL値を返します。

SELECT
  e.name,
  d.name AS department_name,
  m.name AS manager_name
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN employees m ON e.manager_id = m.id;

従業員とその部下の名前を取得

SELECT
  e.name,
  s.name AS subordinate_name
FROM employees e
JOIN employees s ON e.id = s.manager_id;

INNER JOIN:

SELECT
  e.name,
  d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN:

SELECT
  e.name,
  d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

SELECT
  e.name,
  d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

FULL JOIN:

SELECT
  e.name,
  d.name AS department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.id;

同一テーブルの2つの外部キーで結合するその他の方法

SELECT
  e.name,
  (SELECT d.name FROM departments d WHERE d.id = e.department_id) AS department_name,
  (SELECT m.name FROM employees m WHERE m.id = e.manager_id) AS manager_name
FROM employees e;

このクエリは、サブクエリを使用して部門名と上司の名前を取得します。

CASE式を使用する

SELECT
  e.name,
  CASE e.department_id
    WHEN 1 THEN 'Sales'
    WHEN 2 THEN 'Marketing'
    ELSE 'Other'
  END AS department_name,
  CASE e.manager_id
    WHEN 1 THEN 'John Doe'
    WHEN 2 THEN 'Jane Doe'
    ELSE 'Other'
  END AS manager_name
FROM employees e;

結合テーブルを使用する

CREATE TABLE employee_departments (
  employee_id INT,
  department_id INT
);

CREATE TABLE employee_managers (
  employee_id INT,
  manager_id INT
);

INSERT INTO employee_departments (employee_id, department_id)
VALUES
  (1, 1),
  (2, 2),
  (3, 3);

INSERT INTO employee_managers (employee_id, manager_id)
VALUES
  (1, 2),
  (2, 3),
  (3, 1);

SELECT
  e.name,
  d.name AS department_name,
  m.name AS manager_name
FROM employees e
JOIN employee_departments d ON e.id = d.employee_id
JOIN employee_managers m ON e.id = m.employee_id;