DB SQL Query CTE

Overview

CTE（Common Table Expression：共通テーブル式） は、SQLクエリ内で 一時的な結果セットに名前を付けて定義する構文。
WITH 句を使用して定義し、その後のクエリ内で 一時テーブルのように参照できる。

CTEを利用すると、複雑なクエリを複数のステップに分割できるため、可読性・保守性の高いSQLを書くことが可能になる。

主に以下の用途で利用される

• 複雑なクエリの分割
• 同じサブクエリの再利用
• 階層データの処理（再帰CTE）
• 分析クエリの中間結果の整理

基本構文

WITH cte_name (column1, column2, ...) AS (
    SELECT ...
)
SELECT *
FROM cte_name;

構成要素

要素	説明
WITH	CTEを定義するキーワード
cte_name	CTEの名前
column list	CTEで返す列名（省略可能）
AS (...)	一時テーブルとして扱うクエリ

CTEは そのクエリの実行中のみ有効であり、クエリ終了後は破棄される。

シンプルなCTEの例

以下は 売上が1000を超える注文を抽出するCTE の例。

WITH HighValueOrders AS (
    SELECT customer_id, order_total
    FROM orders
    WHERE order_total > 1000
)
SELECT customer_id
FROM HighValueOrders;

処理の流れ

1. HighValueOrders というCTEを作成
2. orders テーブルから order_total > 1000 のデータを抽出
3. その結果を一時テーブルとして利用
4. メインクエリで HighValueOrders を参照

CTEを使うことで、中間結果を明示的に名前付けして扱えるため、クエリが読みやすくなる。

複数CTEの定義

CTEは カンマ区切りで複数定義することもできます。

WITH
sales AS (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
),
large_customers AS (
    SELECT customer_id
    FROM sales
    WHERE total_sales > 10000
)
SELECT *
FROM large_customers;

このように 段階的にデータを加工するクエリを書くことができます。

---

再帰CTE（Recursive CTE）

CTEの大きな特徴の一つが 再帰処理を行えることです。

再帰CTEは 階層構造のデータ を処理する際に利用されます。

例:

• 組織ツリー
• カテゴリ階層
• フォルダ構造
• グラフ探索

再帰CTEの基本構造

WITH RECURSIVE cte_name AS (

    -- 基底クエリ
    SELECT ...

    UNION ALL

    -- 再帰クエリ
    SELECT ...
    FROM table
    JOIN cte_name ON ...
)
SELECT * FROM cte_name;

再帰CTEの例（社員階層）

WITH RECURSIVE EmployeeHierarchy AS (

    -- 基底部
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 再帰部
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh
        ON e.manager_id = eh.employee_id
)
SELECT *
FROM EmployeeHierarchy;

このクエリは 組織のツリー構造を再帰的に展開します。

---

CTEとサブクエリの違い

項目	CTE	サブクエリ
可読性	高い	低くなりやすい
再利用	可能	基本不可
再帰処理	可能	不可
ネスト	フラット	深くなりやすい

サブクエリのネストが深くなる場合、CTEを使うことで より読みやすいSQLを書くことができます。

---

CTEのメリット

1. 可読性の向上

複雑なクエリを分割できるため、SQLの意図が明確になります。

2. クエリの構造化

データ処理を 段階的な処理フローとして記述できます。

3. 再帰処理が可能

階層構造などのデータ処理に対応できます。

4. サブクエリのネスト削減

ネストが深くなりがちなSQLをフラットに書けます。

---

CTEがサポートされている主なデータベース

Database	対応
PostgreSQL	✔
MySQL	8.0+
SQL Server	✔
Oracle	✔
SQLite	✔

---

まとめ

CTE（Common Table Expression）は、SQLにおいて 複雑なクエリを整理して記述するための重要な構文です。

特に以下のようなケースで有効です。

• 複雑な分析クエリ
• 再帰処理（階層データ）
• 中間結果を整理したい場合

モダンなSQLでは サブクエリよりCTEを使う方が可読性が高いケースが多く、実務でも広く利用されています。

CTE（Common Table Expression、共通テーブル式）

CTE（Common Table Expression、共通テーブル式）は、SQLで一時的な結果セットを名前付きで定義し、それをクエリ内で利用できる構文。
クエリを分かりやすく構造化するために使われ、特に複雑なクエリや再帰的なクエリで便利。

-- CTEの基本構文
WITH CTE名 (列名1, 列名2, ...) AS (
    サブクエリ -- 一時的なテーブルとして扱うクエリ
)
SELECT *
FROM CTE名;

-- 例1: シンプルなCTE
-- 以下は、売上が1000を超える注文を取得するCTEの例
WITH HighValueOrders AS (
    SELECT customer_id, order_total
    FROM orders
    WHERE order_total > 1000
)
SELECT customer_id
FROM HighValueOrders;

-- 1. HighValueOrdersは、一時的なテーブルのような役割を果たします。
-- 2. その後、メインクエリでHighValueOrdersを参照して必要なデータを取得します。

-- 例2: 再帰的CTE
-- 再帰CTEは、階層構造（例: 組織のツリー構造やカテゴリ）を処理する場合に使用されます。
WITH RECURSIVE EmployeeHierarchy AS (
    -- 基底部
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL -- トップマネージャー

    UNION ALL

    -- 再帰部
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT *
FROM EmployeeHierarchy;
-- この例では、EmployeeHierarchyを使って階層構造を再帰的に処理しています。

CTEのメリット

1. クエリの可読性向上 複雑なクエリを分割して記述できるため、理解しやすくなる。
2. 再利用性 一時的な結果セットを名前付きで保存し、クエリ内で再利用可能。
3. 分割統治が可能 大きなクエリを分割することで、個別の部分を独立して理解しやすくなる。
4. ネストの削減 サブクエリを減らし、よりフラットな構造でクエリを記述可能。

CTEと副問い合わせの違い

• クエリが複雑で、分割して記述したいとき
• 同じサブクエリを複数回利用するとき
• 再帰的な処理（ツリー構造や階層構造の探索）が必要なとき

CTEはモダンなSQLエンジン（PostgreSQL, SQL Server, MySQL 8.0+, Oracleなど）でサポートされている。