SQLのCTEs（Common Table Expressions）とは、クエリAの中だけで定義・利用できる、クエリBの結果を格納する変数みたいなものである。

SQL特有のCTEs

CTEsは、日本語では共通テーブル式という。

SQL特有の概念で、特に複数のサブクエリから構成される巨大なクエリの中で使われる。その目的は、任意のクエリで得られたデータセットに名前をつけ、使い回すことである。

共通テーブル式の「共通」は、巨大なクエリの中で、定義から最後まで共通して使えることを指す。また、「テーブル式」は、その結果が正確にはテーブルではなく、一時的なものであることを示している。

というのは勝手な考察だが、たぶんそれほど的外れではないと思う。

公式ドキュメントでの表現

PostgreSQLのドキュメントでは「1つの問い合わせのためだけに存在する一時テーブル」。

SQLiteのドキュメントでは「1つのSQL文の間だけ存在する一時的なビューのようなもの」。

MySQLのドキュメントでは「1つの文の範囲内に存在し、その文の中で後から（場合によっては複数回）参照できる、名前付きの一時的な結果セットのこと」などと表現されている。

普通のCTEs

CTEsには、普通の式(Ordinary CTEs)と再帰的な式(Recursive CTEs)の2種類がある。

まず普通のCTEは、主に複雑なクエリの構造を整理するために使う。つまり、メインのクエリからサブクエリを外に出して、簡単な変数名（テーブル式の名前）に置き換える。

以下に、基本的な普通のCTEsの書き方を示す。

sql
WITH テーブル式の名前 AS (任意のクエリ)
SELECT * FROM テーブル式の名前;--👈INSERT文、DELETE文、UPDATE文でも可

1つのWITHキーワードで、複数のCTEを定義することもできる。以下は、普通のCTEsの働きを示す簡単なサンプルコマンドである。

sql
-- サンプル用のテーブルを準備
CREATE TABLE table_name (id, kanji, parent_id);
INSERT INTO table_name
VALUES  (0, '零', null), (1, '壱', 0), (2, '弍', 1);

-- ここからOrdinary CTEのサンプル
WITH ordinary_table_cte AS (
  SELECT id, kanji FROM table_name WHERE parent_id IS NULL
)
SELECT * FROM ordinary_table_cte; --👈INSERT文、DELETE文、UPDATE文でも可。

-- サンプル用のテーブルを削除
DROP TABLE table_name;

WITHを使う必要性のないサンプルだ。むしろ使わないほうがわかり良い。

ただ、クエリが長くなった場合に、CTEsを使うと見通しが良くなることは、何となくイメージできるかと思う。

再帰的CTEs

再帰的CTEsは、ツリー構造や階層構造を走査する際に使う。

WITHキーワードの後ろにRECURSIVEキーワードを続ける以外、基本的な構造は普通のCTEsと変わらない。ただし自己参照的な構造を持ち、再帰的な処理を行う点で、その実態は大きく異なる。

sql
WITH RECURSIVE テーブル式の名前 AS (
  任意のSELECT文1
  UNION ALL -- あるいはUNION
  任意のSELECT文2
)
SELECT * FROM テーブル式の名前; --👈INSERT文、DELETE文、UPDATE文でも可。

任意のSELECT文1は’anchor member’や’base case’などと呼ばれる。最初に処理・保持される、初期値的なテーブルである。

これに対し、任意のSELECT文2は、’recursive member’や’recursive case’などと呼ばれる。この文は、WHERE句あるいはJOIN...ON句、あるいは両者の組み合わせを含んでいる必要がある。厳密には両者なしでも、例えばLIMITなどを使えば再帰CTEsは作れるが、あまり実用的でない。

また、UNIONキーワードで区切っている通り、アンカーメンバーと再帰メンバーは、各列の型も数も同じでなければならない。

再帰的CTEsの処理の流れ

再帰的CTEsは、以下のような流れで処理が進む。

  1. アンカーメンバーを処理し、結果のテーブルを保持する。
  2. 再帰メンバーを処理し、得られた行をアンカーメンバーに結合する。
  3. 得られた行（=’直前の’再帰メンバーの処理結果）に対し、再び再帰メンバーの結合処理を行う。
  4. 3を、再帰メンバーの条件句を満たす行がなくなるまで繰り返す。
  5. すべての結合が済んだら、そのWITH句のCTEが完成する。

一見ややこしく、がんばって理解しようとしてもやっぱりややこしい。理解のキモは、何を入れて、何が出てくるかに目を向けることだ。

再帰的CTEsの場合、入力は直前のSELECT文の出力である。ムカデ人間みたいなものだ。初回はアンカーメンバーのSELECT文の出力。2回目以降は、直前の再帰メンバーのSELECT文の出力が、次の再帰メンバーの入力になる。

やっぱりややこしいや。

再帰的CTEのコマンドサンプル

具体例を挙げる。

以下は、parent_idで親子関係を管理するテーブルに対して、各行の経路を出力するSQLiteのサンプルコマンドである。

sql
-- サンプル用のテーブルを準備
CREATE TABLE table_name (id INTEGER, kanji TEXT, parent_id INTEGER);
INSERT INTO table_name
VALUES (0, '零', NULL),(1, '壱', 0),(2, '弍', 1);

-- ここからRecursive CTEのサンプル
WITH RECURSIVE recursive_cte(id, kanji, parent_id, path_col) AS (
  -- アンカーメンバー
  SELECT
    id,
    kanji,
    parent_id,
    kanji as path_col
  FROM
    table_name
  WHERE
    parent_id IS NULL
  UNION ALL
  -- 再帰メンバー
  SELECT
    t.id,
    t.kanji,
    t.parent_id,
    r.path_col || '->' || t.kanji
  FROM
    table_name t
    JOIN recursive_cte r ON t.parent_id = r.id
)
SELECT * FROM recursive_cte; --👈INSERT文、DELETE文、UPDATE文でも可。

-- サンプル用のテーブルを削除
DROP TABLE table_name;

初回はアンカーメンバーのSELECT文が処理され、parent_idがNULLの行(世代で言うと初代)が選択される。再帰メンバーは、これをrecursive_cteテーブルとしてSELECT文を処理する。parent_idがidと等しい行が選択される。

次の再帰メンバーは、’直前の’再帰メンバーの実行結果（世代で言うと2代目）をrecursive_cteテーブルとして、SELECT文を実行する。こうして、再帰メンバーが何も返さなくなるまで再帰は続く。どこまでも。

最終的な結果を出力するまで、recursive_cteがポインタのように使われているようなイメージである。再帰的CTEが累積的に作られるのか、最後にそれぞれのステップの差分が合体されるのかは知らない。

参考資料