SQL CTE: expresiones de tabla comunes y la cláusula WITH

Lección 6.4 · Tiempo de lectura: ~8 min · Tareas de práctica ↓

Un CTE en SQL (expresión de tabla común) es un resultado temporal con nombre que defines con la cláusula WITH y después usas en la consulta principal como una tabla. En esta lección aprenderás la sintaxis del CTE, cómo se compara con una subconsulta, cómo encadenar varios CTE y cómo armar un análisis en varios pasos que siga siendo fácil de leer.

En las lecciones anteriores escribiste subconsultas dentro de FROM y WHERE. Funcionan, pero a medida que la lógica crece, las subconsultas anidadas se vuelven difíciles de leer: hay que empezar por la consulta más interna e ir hacia afuera. Un CTE te permite darle un nombre a cada paso y escribir los pasos de arriba abajo, en el orden en que los piensas.

¿Qué es un CTE en SQL?

Una expresión de tabla común es un resultado de consulta con nombre, definido al comienzo de una sentencia. Solo existe mientras se ejecuta esa sentencia: no se guarda nada en la base de datos, y otras consultas no lo ven.

Puedes pensar en un CTE como una subconsulta con nombre. Sus principales ventajas son:

• Legibilidad: cada paso tiene un nombre significativo.
• Reutilización: la consulta principal puede referenciar el mismo CTE varias veces.
• Modularidad: una consulta compleja se divide en pasos lógicos pequeños.
• Depuración: cada CTE se puede ejecutar y revisar por separado.

Sintaxis básica

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

• WITH introduce el CTE.
• cte_name es el nombre que usará la consulta principal.
• La consulta entre paréntesis define las filas del CTE.
• La consulta principal que sigue trabaja con cte_name como con una tabla común.

Tu primer CTE

Busquemos a los clientes que gastaron más de $100 en total:

WITH customer_spending AS (
    SELECT customer_id,
           SUM(amount) AS total_spent,
           COUNT(*) AS payment_count,
           AVG(amount) AS avg_payment
    FROM payment
    GROUP BY customer_id
)
SELECT customer_id, total_spent, payment_count, avg_payment
FROM customer_spending
WHERE total_spent > 100
ORDER BY total_spent DESC;

Resultado: los clientes con más de $100 en pagos, con su cantidad de pagos y su pago promedio, primero los que más gastaron.

El CTE calcula el gasto por cliente, y la consulta principal solo filtra y ordena. El nombre customer_spending le dice al lector qué son los datos intermedios antes de que lea cómo se calculan.

CTE vs subconsulta

Esta es la misma consulta escrita con una subconsulta en FROM:

SELECT customer_id, total_spent, payment_count, avg_payment
FROM (
    SELECT customer_id,
           SUM(amount) AS total_spent,
           COUNT(*) AS payment_count,
           AVG(amount) AS avg_payment
    FROM payment
    GROUP BY customer_id
) AS spending_data
WHERE total_spent > 100
ORDER BY total_spent DESC;

Ambas consultas devuelven las mismas filas. La diferencia está en cómo se leen:

Para un solo paso corto, una subconsulta está bien. Cuando la lógica tiene dos pasos o más, o un mismo resultado se necesita dos veces, un CTE suele ser más claro.

Varios CTE en una consulta

Puedes definir varios CTE separados por comas. Cada uno puede usar los CTE definidos antes que él:

WITH customer_spending AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM payment
    GROUP BY customer_id
),
high_spenders AS (
    SELECT customer_id, total_spent
    FROM customer_spending
    WHERE total_spent > 150
)
SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       c.email,
       hs.total_spent
FROM high_spenders AS hs
JOIN customer AS c ON c.customer_id = hs.customer_id
ORDER BY hs.total_spent DESC;

Resultado: nombres y correos de los clientes que gastaron más de $150, primero los que más gastaron.

La consulta se lee como un pequeño plan:

1. customer_spending suma los pagos de cada cliente.
2. high_spenders conserva solo a los clientes por encima de $150.
3. La consulta principal agrega nombres y correos desde customer.

Fíjate en que WITH se escribe una sola vez, antes del primer CTE.

Referenciar un CTE más de una vez

Un CTE puede aparecer varias veces en la consulta principal, por ejemplo para unir un resultado consigo mismo. Comparemos los ingresos de cada mes con los del mes anterior:

WITH monthly_sales AS (
    SELECT CAST(DATE_FORMAT(payment_date, '%Y-%m-01') AS DATE) AS month_start,
           SUM(amount) AS monthly_total
    FROM payment
    GROUP BY month_start
)
SELECT cur.month_start,
       cur.monthly_total AS current_sales,
       prev.monthly_total AS previous_sales,
       ROUND((cur.monthly_total - prev.monthly_total) / prev.monthly_total * 100, 2) AS percent_change
FROM monthly_sales AS cur
LEFT JOIN monthly_sales AS prev
       ON prev.month_start = cur.month_start - INTERVAL 1 MONTH
ORDER BY cur.month_start;

Resultado: los ingresos de cada mes junto a los del mes anterior y la variación porcentual. Cuando el mes anterior no tiene pagos (mayo de 2005, el primer mes, y febrero de 2006, después de un hueco en los datos), las columnas de comparación son NULL.

monthly_sales se define una vez y se usa dos veces, como cur y como prev. Con subconsultas tendrías que escribir dos veces la misma agregación y mantener sincronizadas ambas copias.

Ejemplo práctico: el último alquiler de cada cliente

Una tarea común es encontrar el evento más reciente de cada cliente. Con un CTE se divide en dos pasos claros: encontrar la fecha más reciente y luego agregar los detalles.

WITH last_rental AS (
    SELECT customer_id, MAX(rental_date) AS last_rental_date
    FROM rental
    GROUP BY customer_id
)
SELECT c.customer_id,
       CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
       lr.last_rental_date
FROM customer AS c
LEFT JOIN last_rental AS lr ON lr.customer_id = c.customer_id
ORDER BY lr.last_rental_date
LIMIT 20;

Resultado: los 20 clientes que hace más tiempo no alquilan nada, con la fecha de su último alquiler.

En el módulo 7 verás cómo las funciones de ventana resuelven las tareas de "última fila por grupo" cuando necesitas la fila completa y no solo la fecha.

Ejemplo práctico: análisis de cohortes

Los CTE son especialmente útiles para análisis de varios pasos. En un análisis de cohortes, los clientes se agrupan por el mes de su primer alquiler, y luego se sigue su actividad mes a mes:

WITH customer_cohort AS (
    SELECT customer_id,
           CAST(DATE_FORMAT(MIN(rental_date), '%Y-%m-01') AS DATE) AS cohort_month
    FROM rental
    GROUP BY customer_id
),
monthly_activity AS (
    SELECT cc.cohort_month,
           CAST(DATE_FORMAT(r.rental_date, '%Y-%m-01') AS DATE) AS rental_month,
           r.customer_id
    FROM customer_cohort AS cc
    JOIN rental AS r ON r.customer_id = cc.customer_id
)
SELECT cohort_month,
       rental_month,
       COUNT(DISTINCT customer_id) AS active_customers,
       COUNT(*) AS rentals
FROM monthly_activity
GROUP BY cohort_month, rental_month
ORDER BY cohort_month, rental_month;

Resultado: para cada cohorte (mes del primer alquiler), cuántos de sus clientes alquilaron películas en cada mes siguiente y cuántos alquileres hicieron.

1. customer_cohort asigna cada cliente al mes de su primer alquiler.
2. monthly_activity agrega la cohorte a cada alquiler.
3. La consulta principal cuenta clientes activos y alquileres por cohorte y mes.

Los CTE y el rendimiento

Un CTE es ante todo una forma de organizar una consulta. En la mayoría de las bases de datos modernas, un CTE simple rinde igual que la subconsulta equivalente, porque el optimizador lo integra en la consulta principal.

Hay diferencias entre bases de datos:

• PostgreSQL antes de la versión 12 siempre materializaba un CTE (lo calculaba una vez en un resultado temporal). Desde la versión 12, un CTE referenciado una sola vez se integra; AS MATERIALIZED y AS NOT MATERIALIZED permiten elegir.
• MySQL (8.0+) y MariaDB (10.2+) pueden fusionar un CTE con la consulta principal o materializarlo. Un CTE referenciado varias veces suele calcularse una vez.

Si una consulta con CTE es lenta, revisa su plan de ejecución con EXPLAIN, como harías con cualquier otra consulta.

Conclusiones clave:

• Un CTE es un resultado temporal con nombre definido con WITH y disponible solo dentro de una sentencia.
• Los CTE permiten escribir lógica de varios pasos de arriba abajo, con un nombre significativo para cada paso.
• Varios CTE se separan con comas, y cada uno puede usar los CTE definidos antes.
• Un CTE se puede referenciar varias veces en la consulta principal, sin repetir la misma subconsulta.
• Para un solo paso corto, una subconsulta está bien; para lógica de varios pasos, un CTE suele leerse mejor.
• El rendimiento suele ser el mismo que con subconsultas, pero las reglas de materialización cambian según la base de datos.

Preguntas frecuentes

¿Cuál es la diferencia entre un CTE y una subconsulta?

Ambos definen un resultado intermedio dentro de una consulta. Un CTE se escribe antes de la consulta principal con un nombre, se puede referenciar varias veces y se lee de arriba abajo. Una subconsulta se escribe directamente donde se usa y hay que repetirla en cada uso.

¿Un CTE es más rápido que una subconsulta?

Por lo general, ninguno es más rápido: la mayoría de los optimizadores procesan igual un CTE simple y la subconsulta equivalente. La diferencia aparece cuando una base de datos materializa un CTE, lo que puede ayudar si el resultado se reutiliza y perjudicar si la consulta principal lo filtra mucho. Revisa el plan con EXPLAIN.

¿Un CTE es lo mismo que una tabla temporal?

No. Un CTE existe solo durante una sentencia y no se guarda. Una tabla temporal se crea explícitamente, dura hasta el final de la sesión, la pueden usar muchas consultas y puede tener índices.

¿Qué bases de datos admiten la cláusula WITH?

PostgreSQL, SQL Server, Oracle, SQLite (3.8.3+), MySQL (8.0+) y MariaDB (10.2+) admiten CTE.