¿Cómo funcionan las CTEs y las consultas recursivas en PostgreSQL?

Question

Accepted Answer

Una **CTE** (Common Table Expression), definida con **`WITH`**, es un conjunto de resultados temporal nombrado que hace que las consultas complejas sean legibles. PostgreSQL es totalmente compatible con CTEs, incluidas las CTEs **recursivas** (`WITH RECURSIVE`) para recorrer datos jerárquicos/de árbol.

## Por qué es importante

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

Las CTEs dividen una consulta compleja en pasos nombrados y legibles (encadenados, cada uno haciendo referencia a los anteriores) — mucho más claro que subconsultas anidadas.

## CTEs recursivas — recorrer jerarquías

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

Una **CTE recursiva** tiene un caso base (las filas iniciales) y un paso recursivo (`UNION ALL` que vuelve a unirse a la CTE misma), iterando hasta que no hay filas nuevas — perfecto para **árboles y jerarquías** (organigramas, árboles de categorías, listas de materiales, rutas de gráficos).

## Una nota sobre la materialización de CTEs (específica de Postgres)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

Antes de Postgres 12, las CTEs siempre eran una "barrera de optimización" (materializadas); ahora pueden ser inlineadas para mejor rendimiento — `MATERIALIZED`/`NOT MATERIALIZED` controla esto.

## Por qué es importante

Las CTEs y las consultas recursivas son características valiosas de PostgreSQL para **legibilidad y datos jerárquicos**, por lo que comprenderlas es útil para escribir consultas complejas mantenibles y manejar datos estructurados en árbol.

El beneficio cotidiano es la **legibilidad**: las consultas complejas con múltiples niveles de subconsultas se vuelven difíciles de mantener, mientras que las CTEs permiten descomponerlas en pasos lógicos nombrados y secuenciales (leer de arriba a abajo), mejorando dramáticamente la claridad — haciendo que las CTEs sean una herramienta importante para escribir SQL mantenible.

La capacidad destacada son las **CTEs recursivas** (`WITH RECURSIVE`), que permiten consultar **datos jerárquicos y estructurados en árbol** (organigramas, árboles de categorías, listas de materiales, comentarios enhebrados, recorrido de gráficos) — una clase de consultas de otro modo muy difíciles en SQL, haciendo que las CTEs recursivas sean la solución estándar para el recorrido jerárquico.

Comprender la estructura de la CTE recursiva (caso base + paso recursivo que se une a sí mismo) es conocimiento clave para esta necesidad común.

Un detalle útil específico de Postgres es el comportamiento de **materialización de CTE** (Postgres 12+ puede inline CTEs para mejor rendimiento, controlable con `MATERIALIZED`/`NOT MATERIALIZED`) — relevante para optimización.

Ya que las consultas complejas son comunes (y la legibilidad importa para el mantenimiento), y ya que los datos jerárquicos (que las CTEs recursivas manejan bien únicamente) aparecen frecuentemente (estructuras organizacionales, categorías anidadas), entender las CTEs y las consultas recursivas es conocimiento valioso y práctico de PostgreSQL que mejora la mantenibilidad de las consultas y desbloquea consultas jerárquicas — una capacidad frecuentemente relevante para SQL limpio y sofisticado y un tema común, especialmente la forma recursiva para la necesidad recurrente de consultar datos estructurados en árbol.