🐡

【MySQL】WITHについて

に公開
MySQL
DB
DB
with
select
tech

CTE(Common Table Expression：共通テーブル式)

「共通テーブル式（CTE）」とは、WITH句を使って一時的に作成される仮のテーブルのことを指します。

特徴

  • 一時的に名前をつけてデータを保存できる
  • SELECT 文で通常のテーブルのように扱える
  • SQL をシンプルで見やすくできる

使用するテーブル＆データの作成

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 社員ID（自動採番）
    name VARCHAR(100) NOT NULL,         -- 名前
    age INT NOT NULL,                   -- 年齢
    salary DECIMAL(10, 2) NOT NULL,     -- 給与（小数2桁）
    department VARCHAR(50) NOT NULL     -- 部署名
);
INSERT INTO employees (name, age, salary, department) VALUES
('田中 一郎', 25, 300000, '営業'),
('佐藤 花子', 32, 450000, '人事'),
('鈴木 次郎', 29, 380000, '開発'),
('山田 太郎', 45, 600000, '営業'),
('高橋 由美', 50, 700000, '開発'),
('中村 健', 28, 350000, '営業'),
('井上 さくら', 40, 580000, '人事'),
('小林 正', 33, 420000, '開発');

WITHとは？

一時的なテーブル(仮の結果)を作成し、そのデータをSELECTなどで利用できる機能。
特に複雑なクエリを整理し、見やすくするために便利。

  • WITH で定義したテーブル(CTE)は、一時的なデータとして後のSELECTで使うことが出来る
  • FROMでCTEを通常のテーブルのように扱える
  • WITHを使うと、サブクエリよりも見やすく、管理しやすくなる

1. 基本構文

WITH 共通テーブル式名1 AS (
    -- ここに1つ目の一時的なテーブルを作るクエリを書く
),
共通テーブル式名2 AS (
    -- ここに2つ目の一時的なテーブルを作るクエリを書く
)
SELECT …
FROM …
WHERE …;

2. 使用例

社員(employees)テーブルから「30歳以上の社員」と「50歳以上の社員」を抽出

CTEを使わない場合

SELECT * FROM (
    SELECT id, name, age FROM employees WHERE age >= 30
) AS over_30
UNION
SELECT * FROM (
    SELECT id, name, age FROM employees WHERE age >= 50
) AS over_50;

CTE を使う場合

「30歳以上」「50歳以上」の CTE を定義し、最後に SELECT で組み合わせる

WITH over_30 AS (
    SELECT id, name, age FROM employees WHERE age >= 30
),
over_50 AS (
    SELECT id, name, age FROM employees WHERE age >= 50
)
SELECT * FROM over_30
UNION
SELECT * FROM over_50;

3. CTEを使ったその他の例

社員の平均給与より高い人を探す

WITH average_salary AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT id, name, salary
FROM employees, average_salary
WHERE employees.salary > average_salary.avg_salary;

まとめ

WITH 句を使うと、一時的なテーブルを作成し、見やすく整理することが出来る！

Udemyで講座を公開中！
https://zenn.dev/codek2/articles/e9e44f3e0023fb

X(旧Twitter)
https://twitter.com/kunchan2_

Zenn 本
https://zenn.dev/codek2?tab=books

Youtube
https://www.youtube.com/@codek2_studio

Discussion