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 define um conjunto de resultados temporário nomeado (CTE) dentro de uma consulta; em vez de escrever uma subconsulta repetidamente, você a define uma vez e a referencia pelo nome. Divide consultas complexas em partes legíveis. Suportado desde o Oracle 9i.

PostgreSQL

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

Uma CTE divide consultas complexas em blocos nomeados. Antes do PostgreSQL 12, uma CTE era uma barreira de otimização (sempre materializada); a partir do 12, pode ser incorporada (inlined), e o comportamento antigo pode ser forçado com 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;

Uma CTE define um conjunto de resultados temporário nomeado dentro de uma consulta e melhora a legibilidade. Suportada a partir do MySQL 8.0 (e MariaDB 10.2); versões mais antigas exigem uma derived table (uma subconsulta no FROM).

SQL Server

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

Uma CTE é um conjunto de resultados temporário nomeado. No SQL Server, a instrução imediatamente anterior a uma CTE deve terminar com ponto e vírgula; por isso, WITH costuma ser escrito como ';WITH'. Vale pela duração de uma única instrução (SELECT/INSERT/UPDATE/DELETE).

MongoDB

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

O MongoDB não tem SQL/CTE; os estágios do aggregation pipeline fazem o mesmo trabalho. Cada estágio recebe como entrada a saída do estágio anterior, permitindo um processamento passo a passo semelhante a cadeias de CTEs. $facet ou $lookup também podem ser usados para resultados intermediários.

Redis

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

O Redis é um armazenamento de dados chave-valor sem linguagem de consulta. Você combina resultados intermediários no lado do cliente ou usa um script Lua (EVAL) para lógica no lado do servidor.

Elasticsearch

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

O Elasticsearch SQL (a API _sql) não suporta a sintaxe WITH/CTE; agrupamento e filtragem são escritos em um único SELECT. Na abordagem nativa, o mesmo é feito com uma agregação 'terms' e uma pipeline aggregation 'bucket_selector'.