🦮

[SQL]共通テーブル式(Common Table Expressions、CTE)とは(備忘録)

2025/01/19に公開

1.はじめに

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

共通テーブル式のクエリ例
WITH 共通テーブル名 AS (
    SELECT
        列1,
        列2,
        ・・・
    FROM テーブル名
        WHERE ・・・
)
SELECT
    列1,
    列2、
  ・・・
FROM 共通テーブル名
;

2.サンプルプログラム

〇例1:

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

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

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

3.参考

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

4.その他

実行環境

Discussion