本文へスキップ
用語集のすべての用語

共通テーブル式（CTE）

PostgreSQL の CTE（WITH 問い合わせ）とは？

共通テーブル式は WITH で記述し、サブクエリに名前を付けて、文の残りの部分でテーブルのように使えるようにします。WITH RECURSIVE を使うと木構造や階層を扱えます。

共通テーブル式（CTE）は、WITH 句の中に書く補助文で、1 つのクエリの中だけで使う、名前付きの一時的な結果を定義します。複雑なクエリを読みやすいステップに分割でき、補助文にはデータを挿入・更新・削除する INSERT、UPDATE、DELETE も使えます。

WITH RECURSIVE を使うと CTE が自身の出力を参照できるため、指揮命令系統や入れ子のカテゴリといった階層をたどる標準的な方法になっています。再帰的でなく副作用もない CTE をクエリが 1 回しか参照しない場合、その CTE は外側のクエリに統合され、両方をまとめて最適化できます。個別に評価させたい場合は MATERIALIZED を、統合を求める場合は NOT MATERIALIZED を付けます。

例

この例では、1 人の従業員から出発し、WITH RECURSIVE で管理系統をさかのぼって、各人と出発点からの距離を返します。

CREATE TABLE employees (
  id int PRIMARY KEY,
  name text NOT NULL,
  manager_id int REFERENCES employees (id)
);
INSERT INTO employees VALUES (1, 'Ana', NULL), (2, 'Ben', 1), (3, 'Chen', 2), (4, 'Dee', 1);

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth FROM employees WHERE id = 3
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e JOIN chain c ON e.id = c.manager_id
)
SELECT name, depth FROM chain ORDER BY depth;

拡張機能なしの標準の PostgreSQL 16 または 17 で動くプレーンな SQL です。破棄してかまわない空のデータベースで実行してください。

出典

PostgreSQL 公式ドキュメント（英語）。

関連用語