Módulo 2: SQL Queries y Relationships

CTEs (Common Table Expressions)

Siguiente →

Descripción

Una CTE (Common Table Expression) es una "tabla temporal" que defines al inicio de una query con WITH ... AS (...) y luego usas como si fuera una tabla real. Convierte queries de 3 niveles de subqueries anidados en algo lineal y legible.

CTEs no son un tema "avanzado" — son la mejor práctica moderna para queries complejas. Cualquier query que tenga subqueries anidadas o lógica multi-paso se vuelve más clara con CTEs. PostgreSQL las optimiza igual o mejor que subqueries equivalentes.

Esta cápsula cubre CTEs simples y CTEs recursivas (para árboles, jerarquías, grafos).

Sintaxis básica

WITH nombre_cte AS (
  SELECT ...  -- una query
)
SELECT * FROM nombre_cte;

Múltiples CTEs separadas por coma:

WITH 
  cte1 AS (SELECT ...),
  cte2 AS (SELECT ... FROM cte1 ...)
SELECT * FROM cte2;

Cada CTE puede usar las anteriores. La query final puede combinarlas con JOINs.

Ejemplo 1: descomponer una query compleja

Pregunta: "Top 3 autores por número de comentarios recibidos en sus posts publicados"

Sin CTE (con subqueries anidados)

SELECT username, total_comments FROM (
  SELECT 
    u.username,
    (SELECT COUNT(*) FROM comments c 
     INNER JOIN posts p ON p.id = c.post_id 
     WHERE p.author_id = u.id AND p.published = TRUE) AS total_comments
  FROM users u
) AS stats
WHERE total_comments > 0
ORDER BY total_comments DESC
LIMIT 3;

Funciona, pero hay que leerlo dos veces para entenderlo.

Con CTE

WITH author_stats AS (
  SELECT 
    u.id AS author_id,
    u.username,
    COUNT(c.id) AS total_comments
  FROM users u
  INNER JOIN posts p ON p.author_id = u.id AND p.published = TRUE
  LEFT JOIN comments c ON c.post_id = p.id
  GROUP BY u.id, u.username
)
SELECT username, total_comments
FROM author_stats
WHERE total_comments > 0
ORDER BY total_comments DESC
LIMIT 3;

La lógica es: primero agrupa stats por autor, después filtra y ordena. Lectura lineal de arriba hacia abajo.

Ejemplo 2: CTEs en cadena

Pregunta: "Para cada categoría, el post más comentado, junto con el username del autor".

WITH 
  -- Paso 1: contar comments por post
  post_comments AS (
    SELECT 
      p.id AS post_id,
      p.title,
      p.author_id,
      p.category_id,
      COUNT(c.id) AS num_comments
    FROM posts p
    LEFT JOIN comments c ON c.post_id = p.id
    WHERE p.published = TRUE
    GROUP BY p.id, p.title, p.author_id, p.category_id
  ),
  
  -- Paso 2: ranking por categoría
  ranked AS (
    SELECT 
      *,
      ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY num_comments DESC) AS rn
    FROM post_comments
  )

-- Query final: top 1 por categoría con info del autor y category
SELECT 
  cat.name AS categoria,
  r.title AS post,
  u.username AS autor,
  r.num_comments
FROM ranked r
INNER JOIN users u ON u.id = r.author_id
LEFT JOIN categories cat ON cat.id = r.category_id
WHERE r.rn = 1
ORDER BY cat.name;

Cada CTE tiene un propósito claro:

  1. post_comments: estadísticas básicas
  2. ranked: aplica window function para ranking
  3. Query final: presenta resultados con names

Window functions (ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)) son tema avanzado. Aquí solo se introduce; profundización fuera del scope de esta guía.

CTEs en INSERT, UPDATE, DELETE

CTEs no son solo para SELECT. Funcionan con cualquier statement.

INSERT con CTE

WITH new_post AS (
  INSERT INTO posts (author_id, title, slug, content, published, published_at)
  VALUES (
    (SELECT id FROM users WHERE username = 'maria'),
    'Post creado con CTE',
    'post-creado-cte',
    'Contenido',
    TRUE,
    NOW()
  )
  RETURNING id
)
INSERT INTO post_tags (post_id, tag_id)
SELECT new_post.id, t.id 
FROM new_post, tags t 
WHERE t.slug IN ('postgresql', 'tutorial');

Inserta un post y al mismo tiempo lo etiqueta. Una sola operación atómica desde el punto de vista del cliente (en módulo 3 vemos transacciones formales).

DELETE con CTE

-- Borra todos los comments de posts no publicados
WITH unpublished_posts AS (
  SELECT id FROM posts WHERE published = FALSE
)
DELETE FROM comments
WHERE post_id IN (SELECT id FROM unpublished_posts)
RETURNING id, content;

CTEs recursivas: jerarquías y árboles

Una CTE recursiva se llama a sí misma. Se usa para datos jerárquicos: árboles, grafos, organigramas, hilos de comments anidados.

Sintaxis

WITH RECURSIVE nombre_cte AS (
  -- Caso base (anchor): primera query
  SELECT ...
  
  UNION ALL
  
  -- Caso recursivo: usa la CTE misma
  SELECT ... FROM nombre_cte ... JOIN otra_tabla ...
)
SELECT * FROM nombre_cte;

Ejemplo: árbol de comments con replies

Recordamos que comments tiene parent_comment_id (self-reference). Vamos a obtener un comment raíz con todas sus respuestas a cualquier nivel de profundidad:

-- Insertar varios niveles de replies para el ejemplo
WITH base_comment AS (
  INSERT INTO comments (post_id, author_id, content)
  VALUES (
    (SELECT id FROM posts LIMIT 1),
    (SELECT id FROM users WHERE username = 'carlos'),
    'Comment raíz'
  )
  RETURNING id, post_id
)
INSERT INTO comments (post_id, author_id, parent_comment_id, content)
SELECT 
  base_comment.post_id,
  (SELECT id FROM users WHERE username = 'maria'),
  base_comment.id,
  'Reply nivel 1'
FROM base_comment;

-- Recursive CTE: toda la conversación
WITH RECURSIVE comment_tree AS (
  -- Anchor: comment raíz (sin parent)
  SELECT 
    id,
    parent_comment_id,
    content,
    author_id,
    0 AS depth,
    ARRAY[id] AS path
  FROM comments
  WHERE parent_comment_id IS NULL
  
  UNION ALL
  
  -- Recursivo: replies a cada nivel
  SELECT 
    c.id,
    c.parent_comment_id,
    c.content,
    c.author_id,
    ct.depth + 1,
    ct.path || c.id
  FROM comments c
  INNER JOIN comment_tree ct ON c.parent_comment_id = ct.id
)
SELECT 
  REPEAT('  ', depth) || content AS comment_indented,
  depth,
  array_length(path, 1) AS thread_position
FROM comment_tree
ORDER BY path;

Cómo funciona:

  1. Anchor: comments sin parent (raíces).
  2. Recursivo: para cada comment ya en el árbol, encontrar sus replies.
  3. PostgreSQL itera hasta que la query recursiva no produce nuevas filas.
  4. path (array de IDs) sirve para ordenar la conversación cronológicamente dentro del thread.

Ejemplo 2: organigrama (employees)

-- Tabla hipotética
-- CREATE TABLE employees (
--   id UUID PRIMARY KEY,
--   name TEXT,
--   manager_id UUID REFERENCES employees(id)
-- );

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE manager_id IS NULL  -- CEOs
  
  UNION ALL
  
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT REPEAT('  ', level) || name AS hierarchy, level
FROM org_chart
ORDER BY level, name;

CTEs recursivas son la forma idiomática de manejar jerarquías en PostgreSQL.

Cuidado con recursiones infinitas. Si tu data tiene ciclos (A → B → A), la recursive CTE corre infinitamente. Limita con WHERE level < 10 o usa array para detectar ciclos.

CTEs vs subqueries vs vistas

AbstracciónPersistenciaCuándo usar
Subquery anidadaSolo dentro de la queryLógica simple, una sola vez
CTE (WITH ... AS)Solo dentro de la query (con nombre)Lógica multi-paso, query única
VIEWPersiste en el schemaLógica usada en múltiples queries
MATERIALIZED VIEWPersiste y cachea resultadosReportes pesados que cambian rara vez

Las views (CREATE VIEW) son tema fuera del scope de esta cápsula pero útil saber que existen.

CTEs y performance

Mito viejo: "Las CTEs son barreras de optimización en PostgreSQL".

Realidad (2026): Falso desde PostgreSQL 12 (2019). El planner inlinea CTEs cuando es beneficioso. Solo si fuerzas con WITH ... AS MATERIALIZED (...), PostgreSQL respeta la CTE como tabla temporal.

-- Sin MATERIALIZED: el planner inlinea (default desde PG 12)
WITH ranked AS (...) SELECT ...

-- MATERIALIZED: fuerza ejecutar la CTE como tabla temp y reutilizar
WITH ranked AS MATERIALIZED (...) SELECT ...

Usa MATERIALIZED solo cuando midas que ayuda — la mayoría de las veces no es necesario.

Ejercicios

Ejercicio 1. Reescribe esta query con una CTE para que sea más legible:

SELECT * FROM (
  SELECT u.username, COUNT(p.id) AS num
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  GROUP BY u.id, u.username
) s WHERE num >= 2;
Solución 
WITH user_post_counts AS (
  SELECT u.username, COUNT(p.id) AS num
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  GROUP BY u.id, u.username
)
SELECT * FROM user_post_counts WHERE num >= 2;

Ejercicio 2. Usando CTEs, calcula: el promedio de comments por post, y luego lista posts cuyos comments superan ese promedio.

Solución 
WITH 
  comment_counts AS (
    SELECT p.id AS post_id, p.title, COUNT(c.id) AS num_comments
    FROM posts p
    LEFT JOIN comments c ON c.post_id = p.id
    GROUP BY p.id, p.title
  ),
  avg_comments AS (
    SELECT AVG(num_comments) AS avg_count FROM comment_counts
  )
SELECT cc.title, cc.num_comments, ac.avg_count
FROM comment_counts cc, avg_comments ac
WHERE cc.num_comments > ac.avg_count
ORDER BY cc.num_comments DESC;

Ejercicio 3. Usando una recursive CTE, encuentra todos los descendientes (replies a cualquier nivel) de un comment específico.

Solución 
WITH RECURSIVE descendants AS (
  -- Anchor: el comment seleccionado
  SELECT id, parent_comment_id, content, 0 AS depth
  FROM comments
  WHERE id = '<UUID_DEL_COMMENT>'  -- reemplaza con un id real
  
  UNION ALL
  
  -- Recursivo: replies a cualquier nivel
  SELECT c.id, c.parent_comment_id, c.content, d.depth + 1
  FROM comments c
  INNER JOIN descendants d ON c.parent_comment_id = d.id
)
SELECT REPEAT('  ', depth) || content AS reply, depth
FROM descendants
WHERE depth > 0  -- excluye el comment original
ORDER BY depth;

Ejercicio 4. Inserta un nuevo post y, en el mismo statement, asocia 2 tags usando CTE.

Solución 
WITH new_post AS (
  INSERT INTO posts (author_id, title, slug, content, published, published_at)
  VALUES (
    (SELECT id FROM users WHERE username = 'sofia'),
    'Diseñando para todos',
    'disenando-para-todos',
    'Sobre accesibilidad inclusiva.',
    TRUE,
    NOW()
  )
  RETURNING id
),
tags_to_add AS (
  SELECT id FROM tags WHERE slug IN ('tutorial', 'backend')
)
INSERT INTO post_tags (post_id, tag_id)
SELECT new_post.id, tags_to_add.id
FROM new_post, tags_to_add;

Resumen

  • CTE (WITH name AS (...)): tabla temporal con nombre, lectura lineal
  • Múltiples CTEs en cadena: cada una puede usar las previas
  • CTEs en INSERT/UPDATE/DELETE funcionan con RETURNING
  • WITH RECURSIVE para jerarquías, árboles, grafos
  • Performance: PostgreSQL 12+ inlinea CTEs por default; usa MATERIALIZED solo cuando lo midas
  • Cuándo usar CTE vs subquery: CTE cuando hay multi-paso o se vuelve a usar; subquery para casos simples

En la siguiente cápsula vemos aggregations: COUNT, SUM, AVG, GROUP BY, HAVING — las herramientas para responder preguntas analíticas.

Recursos Adicionales

  1. WITH Queries — PostgreSQL Docs — Documentación oficial completa
  2. "The PostgreSQL Optimizer Inside Out: CTEs" — Cómo optimiza el planner
  3. Recursive Queries — PostgreSQL Wiki — Ejemplos avanzados
  4. "Modern SQL: WITH" — CTEs explicadas con detalle

Siguiente: Cápsula 07 — Aggregations: GROUP BY y HAVING.

← AnteriorSubqueriesSiguiente →Aggregations: GROUP BY y HAVING