【SQL】共通テーブル式（Common Table Expressions、CTE）とは（備忘録）

１．はじめに

・共通テーブル式とは、一時的なクエリの実行結果を定義し、その定義結果を用い新たなクエリを実行する際に使用する構文です。
・共通テーブル式を利用すると、複雑なクエリを分かりやすくすることが可能で、ソースコードの可読性・保守性が向上します。
・共通テーブル式は、共通テーブルが定義されたクエリ内であれば何度も再利用可能です。
・WITH句を使用して、共通テーブル式を定義します。

共通テーブル式のクエリ例

WITH 共通テーブル名 AS (
    SELECT
        列１,
        列２,
        ・・・
    FROM テーブル名
        WHERE ・・・
)
SELECT
    列１,
    列２、
　　・・・
FROM 共通テーブル名
;

２．サンプルプログラム

〇例１：

Sample_1.sql

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10),
    department VARCHAR(10),
    monthly_salary DECIMAL(10, 0)
);

INSERT INTO employees (
    name,
    department,
    monthly_salary
)
VALUES
('田中', '営業', 400000), ('鈴木', '営業', 450000), ('山田', '開発', 650000)
,('大谷', '開発', 700000), ('高橋', '人事', 600000);

-- 共通テーブル式を定義したクエリ
WITH HighSalaryEmployees AS (
    SELECT
        name,
        department,
        monthly_salary
    FROM employees
        WHERE monthly_salary >= 600000
)
SELECT *
FROM HighSalaryEmployees
;     

実行結果

name	department	monthly_salary
山田	開発	650000
大谷	開発	700000
高橋	人事	600000

〇例２：共通テーブルを使用して、テーブルを更新

Sample_2.sql

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10),
    department_id INT,
    monthly_salary DECIMAL(10, 0)
);

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(10),
    average_salary DECIMAL(10, 0)
);

INSERT INTO employees (name, department_id, monthly_salary)
VALUES
('田中', 1, 400000), ('鈴木', 1, 450000), ('山田', 2, 650000)
,('大谷', 2, 700000), ('高橋', 3, 600000),('山本', 3, 650000);

INSERT INTO departments (department_name, average_salary)
VALUES
('営業', 0),('開発', 0),('企画', 0);

-- 定義した共通式の内容を基に、他のテーブルを更新
WITH AvgSalary AS (
    SELECT
        department_id,
        -- 部門毎の平均月収
        AVG(monthly_salary) AS avg_salary
    FROM employees
        GROUP BY department_id
)
UPDATE departments AS a1
    INNER JOIN AvgSalary AS a2
        ON a1.department_id = a2.department_id
SET a1.average_salary = a2.avg_salary
;

-- 更新結果確認
SELECT * FROM departments;

実行結果

department_id	department_name	average_salary
1	営業	425000
2	開発	675000
3	企画	625000

重要：
MySQL では、JOIN句の後にSETを記述するようにしてください。

３．参考

・[SQLの基本]一時テーブルとCTE（WITH句）入門: 効率的なデータ操作のためのツール
・mysqlでjoinした結果をupdate

４．その他

・実行環境