Creación de tablas temporales usando WITH

Las tablas temporales son una forma genial de guardar resultados intermedios para luego trabajar con ellos. Es como en programación: en vez de copiar el mismo trozo de código pesado una y otra vez, lo guardas en una variable y lo usas cuando quieras. En el mundo de SQL, esas "variables" suelen ser tablas temporales.

Pero el CTE lo hace aún más fácil — te permite crear tablas temporales sin tanto lío:

• Nada de preocuparse por borrar cosas.

  El CTE solo vive durante la ejecución de la consulta. Cuando SQL termina — el CTE desaparece, como un asistente invisible que hace su trabajo y se esfuma sin dejar rastro.

• El código es más claro.

  Crear un CTE es mucho más sencillo que andar creando y borrando una tabla temporal física. Todo está a la vista, todo tiene sentido.

• Perfecto para tareas "de una sola vez".

  Si solo necesitas procesar datos en un paso — el CTE es ideal. Rápido, limpio, sin efectos secundarios.

Sintaxis de CTE como tabla temporal

Primero, refresquemos la sintaxis de un CTE:

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

Aquí WITH crea una "expresión de tabla" temporal, que está disponible en toda la consulta donde se define. Esta expresión parece una tabla, pero solo vive hasta que termina la consulta.

Ejemplo: vamos a contar los sobresalientes

Vamos a crear una tabla temporal usando un CTE para encontrar estudiantes cuyo promedio (grade) sea mayor que 90. Luego mostraremos su lista.

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Aquí:

• usamos WITH para crear la tabla temporal high_achievers.
• dentro del CTE agrupamos las notas (grades) de cada estudiante (student_id) y calculamos el promedio.
• en la consulta SELECT * simplemente referenciamos la tabla temporal high_achievers como si fuera una tabla normal.

Comparación entre CTE y tablas temporales

A veces surge la pregunta: ¿cuál es la diferencia entre usar un CTE y una tabla temporal creada con el comando CREATE TEMP TABLE?

Aquí tienes un ejemplo de una tabla temporal tradicional (CREATE TEMP TABLE) para los mismos datos:

CREATE TEMP TABLE high_achievers_temp AS
	SELECT student_id, AVG(grade) AS avg_grade
	FROM grades
	GROUP BY student_id
	HAVING AVG(grade) > 90;

SELECT *
FROM high_achievers_temp;

DROP TABLE high_achievers_temp;  -- ¡No olvides borrar la tabla!

Y la consulta equivalente usando un CTE:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

¿Cuándo usar CTE y cuándo tablas temporales?

El CTE es perfecto cuando quieres preparar datos intermedios rápido y trabajar con ellos al instante — sin complicaciones. No tienes que preocuparte por borrar nada: el CTE desaparece solo cuando termina la consulta. Además, está visible en la estructura del código SQL, no se esconde fuera como una tabla temporal. Esto hace que las consultas sean más limpias, simples y fáciles de entender, sobre todo si tienes varios pasos de procesamiento de datos seguidos. Incluso puedes combinar CTEs, anidarlos y montar lógica compleja — de eso hablaremos más adelante.

Esta estructura va genial cuando la consulta es de una sola vez y los datos solo se necesitan dentro de esa consulta. Pero si planeas usar el resultado varias veces en diferentes partes del sistema o quieres guardar los datos intermedios durante toda la sesión — las tablas temporales serán una opción más fiable. Especialmente si tienes grandes volúmenes de datos y necesitas alto rendimiento: en esos casos, las tablas temporales físicas funcionan de forma más estable y rápida.

Todo depende de la tarea: el CTE es una herramienta rápida y elegante para procesar datos localmente. Y la tabla temporal es el caballo de batalla para escenarios más complejos y duraderos.

Ejemplo: Agregación de datos

Supón que tienes una tabla enrollments donde se guardan los registros de qué estudiantes están inscritos en qué cursos. Queremos saber cuántos estudiantes hay en cada curso, pero solo contar los cursos con más de 5 estudiantes.

Con un CTE se hace así:

WITH course_counts AS (
    SELECT course_id, COUNT(student_id) AS student_count
    FROM enrollments
    GROUP BY course_id
    HAVING COUNT(student_id) > 5
)

SELECT *
FROM course_counts
ORDER BY student_count DESC;

Múltiples CTE: Construyendo etapas

¿Y si necesitas dividir la tarea en varios pasos? Por ejemplo, primero seleccionar estudiantes con promedio alto, y luego encontrar sus cursos. ¡Fácil!

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Ahora la consulta tiene dos CTE:

1. high_achievers encuentra los sobresalientes.
2. student_courses relaciona estudiantes con cursos.

El resultado — una lista de estudiantes con promedio alto y sus cursos.

Errores típicos al usar CTE

Datos demasiado grandes. Los CTE se materializan en la memoria de PostgreSQL. Si creas un CTE con un resultado enorme, puede ralentizar la consulta o superar el límite de memoria.

Abuso. Usar CTE donde una subconsulta normal sería más simple puede complicar las cosas.

Índices olvidados. Si los datos del CTE vienen de tablas grandes sin índices, la consulta irá más lenta.