WITH（CTE）

対応:		SQL:1999（1999）

『WITH』を使うと、複雑なSQL文を名前を付けて分割できます。例えば「まず月別の売上を集計して、その結果を使って平均と比較する」のように、処理を段階的に書けるようになります。正式には共通テーブル式（CTE: Common Table Expression）と呼ばれます。

構文

CTEを定義して使用します。

WITH CTE名 AS (
    SELECT ...
)
SELECT * FROM CTE名;

複数のCTEを定義します。

WITH
    CTE名1 AS (SELECT ...),
    CTE名2 AS (SELECT ... FROM CTE名1)
SELECT * FROM CTE名2;

再帰CTEを定義します（MySQL 8.0以降・PostgreSQL）。

WITH RECURSIVE CTE名 AS (
    -- 初期クエリ（アンカー）
    SELECT ...
    UNION ALL
    -- 再帰クエリ
    SELECT ... FROM CTE名 WHERE 終了条件
)
SELECT * FROM CTE名;

構文一覧

構文	概要
WITH CTE名 AS (...)	名前付きの一時的な結果セットを定義します。直後の SELECT・INSERT・UPDATE・DELETE で参照できます。
複数CTE	WITH 句に複数のCTEをカンマ区切りで定義できます。後に定義したCTEは前のCTEを参照できます。
WITH RECURSIVE	自分自身を参照する再帰CTEを定義します。階層構造・連番生成などに使用します（MySQL 8.0以降・PostgreSQL 対応）。
UNION ALL	再帰CTEで初期クエリと再帰クエリを繋ぎます。UNION ALL を使用して重複行も保持します。

サンプルで使用するテーブル

以下のサンプルコードでは、次のテーブルを使用します。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50)
);
CREATE TABLE orders (
    id INT PRIMARY KEY,
    employee_id INT,
    total INT,
    ordered_at DATE
);

INSERT INTO employees VALUES
(1, '岡部倫太郎', '未来ガジェット研究所'), (2, '牧瀬紅莉栖', '研究部'),
(3, '椎名まゆり', '未来ガジェット研究所'), (4, '橋田至', '未来ガジェット研究所'),
(5, '阿万音鈴羽', '外勤部');

INSERT INTO orders VALUES
(1, 1, 50000, '2025-01-15'), (2, 1, 80000, '2025-02-20'),
(3, 2, 120000, '2025-01-10'), (4, 3, 30000, '2025-03-05'),
(5, 1, 45000, '2025-03-10'), (6, 4, 60000, '2025-02-28');

テーブルの内容を確認します。

SELECT * FROM employees;
+----+--------------+----------------------------+
| id | name         | department                 |
+----+--------------+----------------------------+
|  1 | 岡部倫太郎   | 未来ガジェット研究所       |
|  2 | 牧瀬紅莉栖   | 研究部                     |
|  3 | 椎名まゆり   | 未来ガジェット研究所       |
|  4 | 橋田至       | 未来ガジェット研究所       |
|  5 | 阿万音鈴羽   | 外勤部                     |
+----+--------------+----------------------------+
5 rows in set

SELECT * FROM orders;
+----+-------------+--------+------------+
| id | employee_id | total  | ordered_at |
+----+-------------+--------+------------+
|  1 |           1 |  50000 | 2025-01-15 |
|  2 |           1 |  80000 | 2025-02-20 |
|  3 |           2 | 120000 | 2025-01-10 |
|  4 |           3 |  30000 | 2025-03-05 |
|  5 |           1 |  45000 | 2025-03-10 |
|  6 |           4 |  60000 | 2025-02-28 |
+----+-------------+--------+------------+
6 rows in set

サンプルコード

以下の『orders』テーブルと『employees』テーブルを例に説明します。

月別売上と全体平均を比較します。

『EXTRACT』は日付から年・月・日などを取り出す関数です。詳細は『EXTRACT / DATE_FORMAT』を参照してください。

WITH monthly_sales AS (
    SELECT
        EXTRACT(MONTH FROM ordered_at) AS month,
        SUM(total) AS sales
    FROM orders
    WHERE EXTRACT(YEAR FROM ordered_at) = 2025
    GROUP BY month
),
avg_sales AS (
    SELECT AVG(sales) AS average FROM monthly_sales
)
SELECT
    ms.month,
    ms.sales,
    av.average,
    ms.sales - av.average AS diff
FROM monthly_sales ms, avg_sales av
ORDER BY ms.month;

+-------+--------+------------+------------+
| month | sales  | average    | diff       |
+-------+--------+------------+------------+
|     1 | 170000 | 128333.333 |  41666.667 |
|     2 | 140000 | 128333.333 |  11666.667 |
|     3 |  75000 | 128333.333 | -53333.333 |
+-------+--------+------------+------------+
3 rows in set

※ FROM句にカンマで複数テーブルを列挙すると、すべての組み合わせが生成されます（CROSS JOINと同じ動作）。ここでは avg_sales が1行だけなので、各月の行に平均値が付与されます。

再帰CTEで1〜10の連番を生成します（MySQL 8.0以降）。

WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;

+----+
| n  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set

再帰CTEは、最初に基本となる行を取得し（非再帰部分）、その結果を使って次の行を繰り返し生成します（再帰部分）。以下は employee_id と manager_id を持つ階層型のテーブルを使って、上司→部下の階層を展開する例です。

-- 階層型 employees テーブル（employee_id / manager_id 列を持つ）
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);
INSERT INTO employees VALUES
(1, '岡部倫太郎', NULL), (2, '牧瀬紅莉栖', 1),
(3, '橋田至',     1),    (4, '椎名まゆり', 2);

WITH RECURSIVE org AS (
    SELECT employee_id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, org.depth + 1
    FROM employees e
    JOIN org ON e.manager_id = org.employee_id
)
SELECT depth, name FROM org ORDER BY depth, name;

+-------+------------+
| depth | name       |
+-------+------------+
|     0 | 岡部倫太郎 |
|     1 | 橋田至     |
|     1 | 牧瀬紅莉栖 |
|     2 | 椎名まゆり |
+-------+------------+
4 rows in set

データベース別の書き方

非再帰CTEの『WITH ... AS』構文は MySQL（8.0以降）・PostgreSQL・SQLite（3.8.3以降）で共通して使用できます。

WITH monthly_sales AS (
    SELECT
        EXTRACT(MONTH FROM ordered_at) AS month,
        SUM(total) AS sales
    FROM orders
    WHERE EXTRACT(YEAR FROM ordered_at) = 2025
    GROUP BY month
)
SELECT * FROM monthly_sales;

再帰CTEでは MySQL・PostgreSQL・SQLite が『WITH RECURSIVE』キーワードを必要とします。

-- 再帰CTE（MySQL・PostgreSQL・SQLite）
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;

よくあるミス1: 再帰CTEの終了条件忘れ

再帰CTEで終了条件（WHERE）を忘れると無限ループになります。

-- NG: 終了条件がないため無限ループする
WITH RECURSIVE bad_seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM bad_seq
)
SELECT n FROM bad_seq;

OK: 必ず終了条件（WHERE）を記述します。

WITH RECURSIVE good_seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM good_seq WHERE n < 10
)
SELECT n FROM good_seq;

+----+
| n  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set

よくあるミス2: 同名CTEの再定義

-- NG: 後に定義したCTEから前のCTEを参照する方向は問題ないが、
-- 同一CTE名を同じWITH句内で再定義するとエラーになる
WITH
    data AS (SELECT 1 AS x),
    data AS (SELECT 2 AS x) -- エラー: 同名のCTEは使えない
SELECT * FROM data;

実践パターン

CTEが最も効果を発揮するのは「集計の集計」（ネストしたサブクエリを避けたい場合）と「再帰的な階層展開」です。

-- パターン1: サブクエリを使わずに上位N件を絞り込む
-- 月別売上上位2ヶ月の社員を取得する
WITH monthly_totals AS (
    SELECT
        EXTRACT(MONTH FROM ordered_at) AS month,
        SUM(total) AS sales
    FROM orders
    GROUP BY month
),
top_months AS (
    SELECT month FROM monthly_totals
    ORDER BY sales DESC
    LIMIT 2
)
SELECT o.*
FROM orders o
WHERE EXTRACT(MONTH FROM o.ordered_at) IN (SELECT month FROM top_months);

-- パターン2: 再帰CTEで日付シーケンスを生成する（カレンダー生成など）
WITH RECURSIVE date_seq AS (
    SELECT CAST('2025-01-01' AS DATE) AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_seq
    WHERE dt < '2025-01-07'
)
SELECT dt FROM date_seq;

+------------+
| dt         |
+------------+
| 2025-01-01 |
| 2025-01-02 |
| 2025-01-03 |
| 2025-01-04 |
| 2025-01-05 |
| 2025-01-06 |
| 2025-01-07 |
+------------+
7 rows in set

概要

CTEはサブクエリをFROM句に書き続けると読みにくくなる複雑なクエリを整理するために有効です。一度定義したCTEは同一クエリ内で複数回参照でき、コードの重複を避けられます。

再帰CTEはツリー構造（組織図・カテゴリ階層・パーツ展開など）の走査に特に便利です。無限ループを避けるため終了条件（WHERE句）を必ず記述してください。MySQLでは再帰の最大深度を『max_sp_recursion_depth』システム変数で制御できます。

一時的なクエリ結果をビューとして保存したい場合は『VIEW』を参照してください。集合の結合には『UNION / INTERSECT / EXCEPT』も参照してください。

記事の間違いや著作権の侵害等ございましたらお手数ですがこちらまでご連絡頂ければ幸いです。

トップページへ

SQL辞典