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 defines a named temporary result set (CTE) inside a query; instead of writing a subquery repeatedly, you define it once and refer to it by name. It breaks complex queries into readable pieces. Supported since Oracle 9i.

PostgreSQL

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

A CTE splits complex queries into named blocks. Before PostgreSQL 12 a CTE was an optimization fence (always materialized); from 12 on it can be inlined, and the old behavior can be forced with 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;

A CTE defines a temporary named result set within a query and improves readability. Supported from MySQL 8.0 (and MariaDB 10.2); older versions require a derived table (a subquery in FROM).

SQL Server

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

A CTE is a named temporary result set. In SQL Server the statement immediately preceding a CTE must end with a semicolon, which is why WITH is often written as ';WITH'. It is valid for the duration of a single statement (SELECT/INSERT/UPDATE/DELETE).

MongoDB

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

MongoDB has no SQL/CTE; aggregation pipeline stages do the same job. Each stage takes the previous stage's output as input, giving step-by-step processing similar to CTE chains. $facet or $lookup can also be used for intermediate results.

Redis

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

Redis is a key-value data store with no query language. You combine intermediate results on the client side, or use a Lua script (EVAL) for server-side logic.

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 (the _sql API) does not support WITH/CTE syntax; grouping and filtering are written in a single SELECT. With the native approach, the same is done with a 'terms' aggregation and a 'bucket_selector' pipeline aggregation.