WITH(共通テーブル式 / CTE)

Oracle

WITH region AS (
  SELECT city, COUNT(*) AS n
  FROM customer
  GROUP BY city
)
SELECT city, n FROM region WHERE n > 10;

WITH はクエリ内に名前付きの一時的な結果セット(CTE)を定義する。サブクエリを繰り返し書く代わりに、一度定義して名前で参照する。複雑なクエリを読みやすい部分に分割できる。Oracle 9i 以降でサポートされる。

PostgreSQL

WITH region AS (
  SELECT city, COUNT(*) AS n
  FROM customer
  GROUP BY city
)
SELECT city, n FROM region WHERE n > 10;

CTE は複雑なクエリを名前付きのブロックに分割する。PostgreSQL 12 より前の CTE は最適化の障壁(常にマテリアライズされる)だった。12 以降はインライン化でき、WITH ... AS MATERIALIZED で旧来の挙動を強制できる。

MySQL

WITH region AS (
  SELECT city, COUNT(*) AS n
  FROM customer
  GROUP BY city
)
SELECT city, n FROM region WHERE n > 10;

CTE はクエリ内で一時的な名前付きの結果セットを定義し、可読性を向上させる。MySQL 8.0(および MariaDB 10.2)以降でサポートされる。古いバージョンでは派生テーブル(FROM 内のサブクエリ)が必要。

SQL Server

WITH region AS (
  SELECT city, COUNT(*) AS n
  FROM customer
  GROUP BY city
)
SELECT city, n FROM region WHERE n > 10;

CTE は名前付きの一時的な結果セット。SQL Server では CTE の直前の文をセミコロンで終える必要があり、そのため WITH はしばしば ';WITH' と書かれる。単一の文(SELECT/INSERT/UPDATE/DELETE)の間だけ有効。

MongoDB

// Closest equivalent: aggregation pipeline stages
db.customer.aggregate([
  { $group: { _id: "$city", n: { $sum: 1 } } },
  { $match: { n: { $gt: 10 } } }
])

MongoDB には SQL/CTE がなく、集計パイプラインのステージが同じ役割を果たす。各ステージは前のステージの出力を入力として受け取り、CTE のチェーンに似た段階的な処理となる。中間結果には $facet や $lookup も使用できる。

Redis

# Not applicable: Redis has no concept of SQL or CTEs.

Redis はクエリ言語を持たないキーバリューのデータストア。中間結果はクライアント側で組み合わせるか、サーバー側のロジックには Lua スクリプト(EVAL)を使用する。

Elasticsearch

# CTEs are not supported in Elasticsearch SQL.
POST _sql
{ "query": "SELECT city, COUNT(*) n FROM customer GROUP BY city HAVING COUNT(*) > 10" }

Elasticsearch SQL(_sql API)は WITH/CTE 構文をサポートしない。グループ化とフィルタリングは単一の SELECT に記述する。ネイティブの方法では、'terms' 集計と 'bucket_selector' パイプライン集計で同じことを行う。