技術資料

SQLの中でサブクエリを重ねていると、「どのデータを取得して、どこで集計しているのか」が追いにくくなることがあります。

そんなときに使えるのが、WITH句です。SELECTで取得する結果に名前を付け、その名前を後続のSELECTから参照できます。取得する部分と、加工・集計して出力する部分を分けて書けるので、SQLの役割を整理しやすくなります。

WITH句で定義する名前付きの結果を、CTE（Common Table Expression、共通テーブル式）と呼びます。今回は、単一のCTEで取得結果を加工する例から、複数のCTEをつなぐ書き方までを説明します。SELECTと、FROM句にサブクエリを書く基本が分かる人向けの内容です。

対象はMySQL 8.4です。仕様は2026年10月9日時点のMySQL公式資料で確認しています。

WITH句でSELECTの結果に名前を付ける

基本的な形は、WITH 名前 AS (SELECT ...)に続けて、その名前を使うSELECTを書く、というものです。

まずは、テーブルを使わない小さな例で見てみます。

WITH sample_amount AS (
    SELECT 1500.00 AS amount
)
SELECT amount
FROM sample_amount;

括弧の中のSELECTが返す結果に、sample_amountという名前を付けています。後続のSELECTでは、FROM sample_amountとして、その結果のamount列を取得できます。この例の期待結果は、amountが1500.00の1行です。

WITH句から最後のSELECTまでで一つのSQL文です。途中の閉じ括弧の後にはセミコロンを付けず、文末だけに付けます。

CTEを参照できるのは、そのSQL文の中です。SQLを実行した後、次のSQLからそのCTEを参照することはできません。CREATE TABLEやCREATE VIEWのように、後から参照するためのオブジェクトを作成する操作とは用途が異なります。構文の詳細は、MySQL公式のWITH句の説明で確認できます。

説明用の注文データを用意する

ここからは、注文データの取得と加工を例にします。次のSQLは、検証用DBに説明用のテーブルを作成するものです。既存の業務テーブルへ実行せず、cte_demo_ordersというテーブルがまだ存在しない検証用DBで使ってください。

CREATE TABLE cte_demo_orders (
    id           BIGINT UNSIGNED NOT NULL,
    customer_id  BIGINT UNSIGNED NOT NULL,
    status       TINYINT UNSIGNED NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO cte_demo_orders (id, customer_id, status, total_amount)
VALUES
    (1, 101, 1, 1500.00),
    (2, 101, 1, 2360.00),
    (3, 101, 0,  980.00),
    (4, 102, 1, 5000.00),
    (5, 103, 1,    0.00),
    (6, 103, 1,   49.99);

この例では、status = 1を完了、status = 0を未完了とします。total_amountは円単位の金額で、加工前後を確認するために0円や小数部分を持つ値も含めています。

SELECTで取得した結果を、後続のSELECTで加工する

まず、完了した注文だけを取得するSELECTを考えます。

SELECT id, customer_id, total_amount
FROM cte_demo_orders
WHERE status = 1
ORDER BY id;

この条件では、未完了のidが3の注文を除いた5件が取得対象になります。このSELECTの取得部分をWITH句の括弧内へ置き、completed_ordersという名前を付けてみます。

WITH completed_orders AS (
    SELECT id, customer_id, total_amount
    FROM cte_demo_orders
    WHERE status = 1
)
SELECT id, customer_id, total_amount
FROM completed_orders
ORDER BY id;

この段階では、取得する列と値は元のSELECTと同じです。名前を付けた結果を、後続のSELECTが参照する形になりました。出力順は、最後のSELECTのORDER BYで指定しています。

続いて、後続のSELECTで金額を千円単位へ加工します。

WITH completed_orders AS (
    SELECT id, customer_id, total_amount
    FROM cte_demo_orders
    WHERE status = 1
)
SELECT
    id,
    customer_id,
    total_amount,
    ROUND(total_amount / 1000, 1) AS amount_in_thousands
FROM completed_orders
ORDER BY id;

CTEの中では、完了した注文の必要な列を取得しています。後続のSELECTでは、そのtotal_amountを1000で割り、ROUND(..., 1)で小数第1位へ丸めた列を追加しています。元の金額も残しているので、加工前後を比較できます。

期待結果は次のとおりです。

例えば、2360円は2.36千円なので、丸めた値は2.4です。49.99円は0.04999千円なので、0.0になります。表示用に丸めた値から元の金額は復元できないため、計算や保存に必要な元の金額とは使い分けます。数値の表示形式は、使用するSQLクライアントによって異なる場合があります。

ROUND()の丸め方は引数の型にも依存します。ここでは、DECIMAL型の金額を整数の1000で割る例です。詳細は、MySQL公式のROUND関数の説明と、算術演算子の説明を参照してください。

このように、「どの行を取得するか」をCTEへ、「取得した値をどう加工して出力するか」を後続のSELECTへ分けられます。WITH句で定義した結果を、そのまま出力する必要はありません。

複数のCTEはカンマで区切る

CTEを複数定義するときは、WITH a AS (...), b AS (...) SELECT ...のようにカンマで区切ります。同じ階層ではWITH句を一つにまとめ、各CTEには異なる名前を付けます。

先ほどの完了した注文を、今度は顧客ごとに集計してみます。

WITH completed_orders AS (
    SELECT id, customer_id, total_amount
    FROM cte_demo_orders
    WHERE status = 1
),
customer_totals AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_amount
    FROM completed_orders
    GROUP BY customer_id
)
SELECT
    customer_id,
    order_count,
    total_amount,
    ROUND(total_amount / 1000, 1) AS amount_in_thousands
FROM customer_totals
ORDER BY customer_id;

役割は次の三つに分かれます。

1. completed_orders：完了した注文を取得する。
2. customer_totals：その結果を顧客ごとにまとめ、件数と金額を集計する。
3. 最後のSELECT：集計した金額を千円単位へ加工し、顧客ID順に出力する。

二つ目のCTEのFROM completed_ordersで、一つ目のCTEを参照しています。最後のSELECTからは、二つ目のcustomer_totalsを参照しています。

この例の期待結果は次のとおりです。

顧客101の完了した注文は1500円と2360円なので、合計は3860円です。未完了の980円は含みません。顧客103は0円の注文も条件に一致する1行なので、件数は2件になります。

order_countとtotal_amountは、二つ目のCTE内で付けた列の別名です。後続のSELECTでは、その名前を使えます。

CTEの定義順序と参照する列に注意する

同じWITH句の中では、先に定義したCTEを、後から定義するCTEで参照できます。今回の例では、completed_ordersを先に定義し、それを使うcustomer_totalsを後に置きます。定義順を逆にして、後方のCTEを先に参照することはできません。

また、CTEから参照できる列は、そのCTEのSELECTが返す列です。例えば、completed_ordersは条件にstatusを使っていますが、取得列には含めていません。そのため、後続のSELECTでcompleted_orders.statusを出力したい場合は、CTE内の取得列にもstatusを追加する必要があります。

CTE名には、aやbでも構文上の問題はありません。ただ、後から読んだときに役割を追えるよう、completed_ordersやcustomer_totalsのような名前にしておくと分かりやすいかと思います。

サブクエリで書く場合との違い

顧客ごとの集計は、FROM句にサブクエリを書く形でも表現できます。

SELECT
    customer_id,
    order_count,
    total_amount,
    ROUND(total_amount / 1000, 1) AS amount_in_thousands
FROM (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_amount
    FROM (
        SELECT id, customer_id, total_amount
        FROM cte_demo_orders
        WHERE status = 1
    ) AS completed_orders
    GROUP BY customer_id
) AS customer_totals
ORDER BY customer_id;

このSQLと先ほどの複数CTEの例は、同じ列と値を返す構成です。サブクエリ版は内側の取得部分から外側へ読むと処理のまとまりを追えます。CTE版は、それぞれの定義をSQLの先頭に並べて、上から順に読めます。

今回の集計自体は、さらに短いSQLでも書けます。ここでは書き方を比較するために、取得と集計を分けています。短いSELECTならそのままでも十分ですし、処理のまとまりが増えたときにWITH句を使う、と考えればよいかと思います。

読みやすさと実行速度は別に確認する

CTEを並べると、説明上は「取得して、集計して、出力する」という段階に分かれます。ただし、MySQL内部で必ずその順に結果全体を作ってから次へ進む、という意味ではありません。

MySQLは、CTEの中のSELECTと後続のSELECTをまとめて処理することがあります。また、CTEの結果を内部で一時的に保存し、それを後続のSELECTで使うこともあります。どちらの方法を使うかは、MySQLがSQLの内容などに応じて判断します。

WITH句へ分けて書いても、その書き方だけで実際の処理方法が決まるわけではなく、速くなるとも限りません。詳しい仕組みは、MySQL公式のCTEの処理方法に関する説明を参照してください。

書き換える場合は、まず取得列・値・件数が一致することを確認します。性能も比較するなら、同じデータと条件を使って実行計画や処理時間を確認します。実行計画の読み方は、MySQLのEXPLAIN入門の記事を参照してください。

なお、CTE自身を参照して階層データなどをたどる再帰CTEには、WITH RECURSIVEという書き方もあります。今回は、単一・複数の非再帰CTEに絞っています。

まずは、既存のSELECTをWITH句へ置き、その結果を後続のSELECTで加工するところから試してみると、CTEの役割をつかみやすいかと思います。集計などの処理が増えたら、複数のCTEへ分けて名前を付けると、SQLの見通しをよくできます。

参考資料

• MySQL 8.4 Reference Manual: WITH (Common Table Expressions)
• MySQL 8.4 Reference Manual: CTEの処理方法と最適化
• MySQL 8.4 Reference Manual: ROUND関数
• MySQL 8.4 Reference Manual: Arithmetic Operators
• MySQL 8.4 Reference Manual: Aggregate Functions