WITH(Common Table Expression / 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)부터 지원하며, 이전 버전에서는 derived table(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가 없으며 aggregation pipeline 단계가 같은 역할을 한다. 각 단계는 이전 단계의 출력을 입력으로 받아 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' aggregation과 'bucket_selector' pipeline aggregation으로 같은 작업을 한다.