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 définit un jeu de résultats temporaire nommé (CTE) dans une requête ; au lieu d'écrire plusieurs fois une sous-requête, vous la définissez une fois et y faites référence par son nom. Il découpe les requêtes complexes en éléments lisibles. Pris en charge depuis Oracle 9i.

PostgreSQL

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

Une CTE découpe les requêtes complexes en blocs nommés. Avant PostgreSQL 12, une CTE était une barrière d'optimisation (toujours matérialisée) ; à partir de 12, elle peut être intégrée (inlined), et l'ancien comportement peut être forcé avec 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;

Une CTE définit un jeu de résultats nommé temporaire dans une requête et améliore la lisibilité. Prise en charge à partir de MySQL 8.0 (et MariaDB 10.2) ; les versions plus anciennes nécessitent une table dérivée (une sous-requête dans FROM).

SQL Server

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

Une CTE est un jeu de résultats temporaire nommé. Dans SQL Server, l'instruction qui précède immédiatement une CTE doit se terminer par un point-virgule, c'est pourquoi WITH s'écrit souvent ';WITH'. Elle est valable le temps d'une seule instruction (SELECT/INSERT/UPDATE/DELETE).

MongoDB

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

MongoDB n'a ni SQL ni CTE ; les étapes du pipeline d'agrégation font le même travail. Chaque étape prend en entrée la sortie de la précédente, ce qui donne un traitement pas à pas semblable aux chaînes de CTE. $facet ou $lookup peuvent aussi servir pour les résultats intermédiaires.

Redis

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

Redis est un magasin de données clé-valeur sans langage de requête. Vous combinez les résultats intermédiaires côté client, ou utilisez un script Lua (EVAL) pour la logique côté serveur.

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 (l'API _sql) ne prend pas en charge la syntaxe WITH/CTE ; le regroupement et le filtrage s'écrivent dans un seul SELECT. Avec l'approche native, on fait la même chose avec une agrégation 'terms' et une pipeline aggregation 'bucket_selector'.