inner ... join、left join、right join - SQL

結合

「 INNER ... JOIN 」は、テーブル同士の主キーと外部キーを結合し、一致する行だけを取り出します。

「 RIGHT ... JOIN 」は、一致しているかどうかに関わらず、右のテーブルの内容がすべて取り出されます。

「 LEFT ... JOIN 」は、一致しているかどうかに関わらず、左のテーブルの内容がすべて取り出されます。

データベースとテーブルの作成

Node.js でデータベースとテーブルを作成します。

const mysql = require('mysql2/promise');
const db_name = "practice"

// データベースへの接続設定
let conf = {
  host:"localhost",
  user:"root",
  password:"password"
}

const init_pool = mysql.createPool(conf);

conf.database = db_name;
const pool = mysql.createPool(conf);

(async () => {
  find_db = `SHOW DATABASES LIKE "${db_name}"`;
  create_db = `CREATE DATABASE ${db_name}`;
  find_employee_table = 'SHOW TABLES LIKE "employee"';
  find_department_table = 'SHOW TABLES LIKE "department"';

  employee_table = `CREATE TABLE employee (
  id INT AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  department_id INT NULL,
  PRIMARY KEY (id));`;

  department_table = `CREATE TABLE department (
  id INT AUTO_INCREMENT,
  department VARCHAR(255) NOT NULL,
  PRIMARY KEY (id));`

  try {
    // データベースの有無
    const [results, fields] = await init_pool.query(find_db);

    // データベースがなければ作成
    if(!results.length){
      const [results, fields] = await init_pool.query(create_db);
      console.log("データベースを作成しました。")
    }

    // テーブルの有無の確認
    const [employee_results, employee_fields] = await pool.query(find_employee_table);
    const [department_results, department_fields] = await pool.query(find_department_table);

    // テーブルがなければテーブルを作成
    if(!employee_results.length){
      const [employee_results, employee_fields] = await pool.query(employee_table);
      console.log("employee　テーブルを作成しました。");
    }
    if(!department_results.length){
      const [department_results, department_fields] = await pool.query(department_table);
      console.log("department　テーブルを作成しました。");
    }

  } catch (err) {
    console.log(err);
  }
  pool.end()
})();

データの登録

employee と department のテーブルにデータを登録します。

const mysql = require('mysql2/promise');
const db_name = "practice"

// データベースへの接続設定
let conf = {
  host:"localhost",
  user:"",
  password:"",
  database: db_name
}

const employee = [
  { name: "Taro", department_id: 1 },
  { name: "Jiro" },
  { name: "Hanako", department_id: 6 },
  { name: "Saburo", department_id: 2 },
  { name: "Shirou", department_id: 3 },
]

const departments = [
  { department: "総務部" },
  { department: "人事部" },
  { department: "経理部" },
  { department: "営業部" },
  { department: "マーケティング部" },
  { department: "情報システム部" },
]

const employee_pool = mysql.createPool(conf);
const departments_pool = mysql.createPool(conf);

(async () => {
  try {
    for(let e of employee){
      if("department_id" in e){
        create_record = `INSERT INTO employee(name, department_id) VALUES ("${e.name}", ${e.department_id})`;
      } else {
        create_record = `INSERT INTO employee(name) VALUES ("${e.name}")`;
      }
      const [results, fields] = await employee_pool.query(create_record);
    }

    for(let d of departments){
      create_record = `INSERT INTO department(department) VALUES ("${d.department}")`;
      const [results, fields] = await departments_pool.query(create_record);
    }
    
  } catch (err) {
    console.log(err);
  }
  employee_pool.end()
  departments_pool.end()
})();

INNER ... JOIN

INNER ... JOIN を利用してデータを取り出してみます。

内部結合

SELECT
  e.id,
  e.name,
  e.department_id,
  d.id,
  d.department
FROM
  employee AS e
INNER JOIN
  department AS d
ON
  e.department_id = d.id;
id name department_id id department
1 Taro 1 1 総務部
3 Hanako 6 6 情報システム部
4 Saburo 2 2 人事部
5 Shirou 3 3 経理部

inner ... join を利用してデータを取り出しても、「 Jiro 」の employee_id の値が「 NULL 」のため、表示されるのは「 Taro 」と「 Hanakot 」のデータのみ取得されます。

LEFT JOIN

FROM で指定したテーブルを基準としてデータを取得します。

employee テーブルのレコードがすべて取得されます。

左外部結合

SELECT
  e.id,
  e.name,
  e.department_id,
  d.id,
  d.department
FROM
  employee AS e
LEFT JOIN
  department AS d
ON
  e.department_id = d.id;
id name department_id id department
1 Taro 1 1 総務部
2 Jiro NULL NULL NULL
3 Hanako 6 6 情報システム部
4 Saburo 2 2 人事部
5 Shirou 3 3 経理部

RIGHT JOIN

RIGTH JOIN で指定したテーブルを基準としてデータを取得します。

department テーブルのすべてのレコードに関連づけられているデータを取得します。

右外部結合

SELECT
  e.id,
  e.name,
  e.department_id,
  d.id,
  d.department
FROM
  employee AS e
RIGHT JOIN
  department AS d
ON
  e.department_id = d.id;
id name department_id id department
1 Taro 1 1 総務部
3 Hanako 2 2 情報システム部
4 Saburo 2 2 人事部
5 Shirou 3 3 経理部
NULL NULL NULL 4 営業部
NULL NULL NULL 5 マーケティング部

2つのテーブルの値を更新

UPDATE
  employee AS e
JOIN
  department AS d
ON
  e.department_id = d.id
SET
  e.name = "Tanaka", 
  department_id = 1
WHERE e.id = 5;