SQL · Guías · 30 de octubre de 2026 · 14 min de lectura

CTE en SQL (cláusula WITH): consultas legibles paso a paso

Una CTE (common table expression, expresión de tabla común) es un resultado temporal con nombre que solo existe durante una consulta. La defines con WITH nombre AS (SELECT ...) y luego usas nombre en la consulta principal como si fuera una tabla. Por debajo no pasa nada que una subconsulta no pudiera hacer. La gracia está en la legibilidad: en lugar de anidar consultas a tres niveles de profundidad, las escribes de arriba abajo, un paso por CTE, y le das a cada paso un nombre que dice qué contiene.

WITH step_one AS (
  SELECT ...
),
step_two AS (
  SELECT ... FROM step_one ...
)
SELECT ... FROM step_two;

Los ejemplos usan la base de datos de una aerolínea del curso de SQL de Nomto: 33.121 vuelos entre 104 aeropuertos en el verano de 2026, con reservas, billetes, asientos y un programa de fidelización (valores en inglés, dinero en dólares). El SQL usa sintaxis de PostgreSQL y cada resultado sale de ejecutar la consulta; puedes comprobarlo en la sala de SQL de Nomto.

Tu primera CTE

¿Cuántos billetes suelen tener los socios del programa de fidelización? Hacen falta dos niveles de agregación: billetes por socio y luego socios por número de billetes. Con una CTE, cada nivel tiene su propio bloque:

WITH per_member AS (
  SELECT member_id, COUNT(*) AS tickets
  FROM ticket_members
  GROUP BY member_id
)
SELECT tickets, COUNT(*) AS members
FROM per_member
GROUP BY tickets
ORDER BY tickets
LIMIT 8;

per_member tiene una fila por cada socio con billetes. La consulta principal la lee como una tabla. Exactamente la misma lógica cabe en una tabla derivada (FROM (SELECT ...) AS per_member), y para un solo paso está bien. La diferencia se nota cuando hay varios pasos.

(El repunte en siete y ocho billetes, después de que los números vinieran bajando, está en los datos; no es un fallo de la consulta. Merece una mirada antes de que alguien haga un gráfico con ello, y una consulta de dos pasos como esta suele ser la forma de detectar estas cosas).

Varias CTE: un paso por idea

¿Cuál es la ocupación media (pasajeros entre asientos) de los vuelos que llegaron, por modelo de avión? Hacen falta tres ingredientes: asientos por modelo, pasajeros por vuelo y los propios vuelos. Cada uno se convierte en una CTE, y las CTE posteriores pueden usar las anteriores:

WITH seats_per_model AS (
  SELECT aircraft_code, COUNT(*) AS seats
  FROM seats
  GROUP BY aircraft_code
),
passengers AS (
  SELECT flight_id, COUNT(*) AS pax
  FROM ticket_flights
  GROUP BY flight_id
),
flight_load AS (
  SELECT f.flight_id,
         f.aircraft_code,
         COALESCE(p.pax, 0) AS pax,
         s.seats,
         COALESCE(p.pax, 0) * 1.0 / s.seats AS load_factor
  FROM flights f
  JOIN seats_per_model s ON s.aircraft_code = f.aircraft_code
  LEFT JOIN passengers p ON p.flight_id = f.flight_id
  WHERE f.status = 'Arrived'
)
SELECT aircraft_code,
       COUNT(*) AS flights,
       MAX(seats) AS seats,
       ROUND(AVG(pax), 1) AS avg_pax,
       ROUND(100 * AVG(load_factor), 1) AS avg_load_pct
FROM flight_load
GROUP BY aircraft_code
ORDER BY avg_load_pct DESC;

Cada CTE hace una cosa, y el SELECT final solo resume. Agregar asientos y pasajeros antes del JOIN es además lo que mantiene los números correctos: unir flights con seats y ticket_flights en bruto multiplicaría las filas e inflaría todos los conteos.

Esta es la misma consulta escrita con tablas derivadas anidadas (recortada a las tres primeras filas):

SELECT aircraft_code, COUNT(*) AS flights, ROUND(100 * AVG(load_factor), 1) AS avg_load_pct
FROM (
  SELECT f.aircraft_code, COALESCE(p.pax, 0) * 1.0 / s.seats AS load_factor
  FROM flights f
  JOIN (SELECT aircraft_code, COUNT(*) AS seats FROM seats GROUP BY aircraft_code) s
    ON s.aircraft_code = f.aircraft_code
  LEFT JOIN (SELECT flight_id, COUNT(*) AS pax FROM ticket_flights GROUP BY flight_id) p
    ON p.flight_id = f.flight_id
  WHERE f.status = 'Arrived'
) AS flight_load
GROUP BY aircraft_code
ORDER BY avg_load_pct DESC
LIMIT 3;

La misma respuesta. Pero hay que leerla de dentro hacia fuera, y los pasos no tienen nombre. En una revisión de código, «revisa passengers» es mucho más fácil de decir que «revisa la segunda subconsulta dentro de la primera».

Depurar una cadena de CTE paso a paso

La verdadera ventaja de los pasos con nombre: puedes inspeccionar cualquiera de ellos cambiando el SELECT final. Sustitúyelo por SELECT * FROM flight_load LIMIT 20, o por un conteo, y verás exactamente qué produce ese paso.

Ese hábito atrapa errores como este. Cambia el LEFT JOIN passengers por un JOIN normal y la ocupación se dispara:

WITH seats_per_model AS (
  SELECT aircraft_code, COUNT(*) AS seats FROM seats GROUP BY aircraft_code
),
passengers AS (
  SELECT flight_id, COUNT(*) AS pax FROM ticket_flights GROUP BY flight_id
)
SELECT f.aircraft_code,
       COUNT(*) AS flights,
       ROUND(100 * AVG(p.pax * 1.0 / s.seats), 1) AS avg_load_pct
FROM flights f
JOIN seats_per_model s ON s.aircraft_code = f.aircraft_code
JOIN passengers p ON p.flight_id = f.flight_id
WHERE f.status = 'Arrived'
GROUP BY f.aircraft_code
ORDER BY avg_load_pct DESC;

La ocupación del Cessna pasa del 14 % al 50 %, porque sus 3.328 vuelos vacíos se han descartado sin avisar. Una comprobación rápida del paso passengers explica por qué: de 16.707 vuelos llegados, solo 11.438 tienen algún billete, y un INNER JOIN conserva solo esos. Ninguna de las dos versiones es «la» respuesta por sí sola, pero necesitas saber qué pregunta estás respondiendo, y los pasos con nombre convierten esa comprobación en una línea.

Usar una CTE más de una vez

Una CTE se puede referenciar varias veces en la consulta principal, algo que una tabla derivada no permite sin copiarla. Un uso clásico: un valor y su porcentaje sobre el total.

WITH route_revenue AS (
  SELECT f.departure_airport || '-' || f.arrival_airport AS route,
         SUM(tf.amount) AS revenue
  FROM ticket_flights tf
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE f.status = 'Arrived'
  GROUP BY route
)
SELECT route,
       ROUND(revenue) AS revenue,
       ROUND(100 * revenue / (SELECT SUM(revenue) FROM route_revenue), 2) AS pct_of_total
FROM route_revenue
ORDER BY revenue DESC
LIMIT 5;

route_revenue se usa dos veces: como tabla principal y dentro de la subconsulta escalar del total. Sumando las 451 rutas, los vuelos llegados ingresaron 114,3 millones de dólares, y la primera ruta, de Domodédovo a Jabárovsk, se llevó casi un 4 %.

Una CTE también puede guardar un resumen de una sola fila que unes a todas las filas. ¿Qué días de reservas fueron los más intensos comparados con un día medio?

WITH daily AS (
  SELECT CAST(book_date AS date) AS day, COUNT(*) AS bookings
  FROM bookings
  GROUP BY day
),
stats AS (
  SELECT AVG(bookings) AS avg_bookings FROM daily
)
SELECT d.day, d.bookings, ROUND(d.bookings / s.avg_bookings, 2) AS vs_avg
FROM daily d
CROSS JOIN stats s
ORDER BY d.bookings DESC
LIMIT 5;

stats lee de daily, y la consulta principal lee de las dos. (La media, 4.692,6 reservas al día, se calcula sobre 56 días, del 17 de junio al 11 de agosto; el último día solo está en parte en los datos, lo que baja un poco la media. En este tipo de comparación lo normal es quitar los días incompletos).

Dar nombre a las columnas de una CTE

Puedes poner una lista de nombres de columna después del nombre de la CTE. Sustituyen a los nombres que tengan las columnas en la consulta interior:

WITH model_seats (code, seat_count) AS (
  SELECT aircraft_code, COUNT(*)
  FROM seats
  GROUP BY aircraft_code
)
SELECT code, seat_count
FROM model_seats
ORDER BY seat_count DESC
LIMIT 3;

Casi todo el mundo pone los alias dentro del SELECT (COUNT(*) AS seat_count). La lista de columnas es una opción ordenada cuando la consulta interior tiene expresiones sin nombre, y deja la «interfaz» de la CTE a la vista en su primera línea.

CTE recursivas

WITH RECURSIVE permite que una CTE se refiera a sí misma. Tiene dos partes unidas por UNION o UNION ALL: un ancla que produce las filas iniciales y una parte recursiva que produce filas nuevas a partir de las filas de la vuelta anterior. Se detiene cuando una vuelta no produce nada nuevo.

El ejemplo más pequeño genera números:

WITH RECURSIVE n AS (
  SELECT 1 AS i
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 5
)
SELECT i FROM n;

WHERE i < 5 es la condición de parada. Sin ella, la consulta no termina nunca. (Para números y fechas, generate_series es más sencillo; lo tienes en la guía del calendario de fechas, en inglés).

La recursión de verdad merece la pena con grafos y jerarquías. ¿Cuántos vuelos hacen falta para llegar desde Yuzhno-Sajalinsk (UUS) a los demás aeropuertos de la red?

WITH RECURSIVE routes AS MATERIALIZED (
  SELECT DISTINCT departure_airport AS src, arrival_airport AS dst
  FROM flights
),
reach AS (
  SELECT 'UUS'::VARCHAR AS airport, 0 AS hops
  UNION
  SELECT r.dst, reach.hops + 1
  FROM reach
  JOIN routes r ON r.src = reach.airport
  WHERE reach.hops < 4
),
best AS (
  SELECT airport, MIN(hops) AS hops
  FROM reach
  GROUP BY airport
)
SELECT hops, COUNT(*) AS airports
FROM best
GROUP BY hops
ORDER BY hops;

Desde UUS hay cuatro destinos directos, 76 aeropuertos más necesitan una escala y los 23 restantes, dos escalas (tres vuelos). En total son 1 + 4 + 76 + 23 = 104: toda la red es alcanzable. La búsqueda podía llegar hasta cuatro saltos, pero ningún aeropuerto necesita un cuarto vuelo.

Cuatro detalles hacen que esta consulta sea segura:

• WITH RECURSIVE se escribe una sola vez, arriba del todo, aunque solo reach sea recursiva. Las CTE normales (routes, best) pueden ir en el mismo WITH.
• UNION (no UNION ALL) elimina las filas (airport, hops) duplicadas, lo que evita que las filas se disparen cuando muchos caminos llevan al mismo aeropuerto.
• hops < 4 es un límite de profundidad estricto. En un grafo con ciclos (y las rutas van en los dos sentidos), una recursión sin límite puede no terminar nunca.
• routes AS MATERIALIZED calcula la lista de rutas una sola vez, antes de que empiece la recursión. En DuckDB (el motor del sandbox del curso), la misma consulta sin esa palabra a veces se detiene antes de tiempo y devuelve solo las dos primeras filas; con MATERIALIZED el resultado es estable. PostgreSQL también acepta la palabra clave.

CTE frente a subconsulta, tabla temporal y vista

Usa una CTE para los pasos dentro de un mismo análisis. Si el mismo resultado intermedio alimenta varias consultas separadas, una tabla temporal te ahorra recalcularlo. Si todo un equipo necesita esa lógica, su sitio es una vista o un modelo en tu pipeline de datos.

¿Las CTE hacen las consultas más rápidas o más lentas?

Ni una cosa ni otra, por defecto. En PostgreSQL 12 y posteriores, una CTE no recursiva que se referencia una sola vez se integra en la consulta principal exactamente igual que una subconsulta, y el optimizador ve a través de ella. Una CTE referenciada más de una vez normalmente se calcula una vez y se reutiliza.

Puedes forzar otra cosa. WITH big AS MATERIALIZED (...) obliga a PostgreSQL a calcular la CTE una vez y guardarla, lo que ayuda cuando la CTE es cara y el optimizador se empeña en recalcularla. NOT MATERIALIZED fuerza la integración, de modo que los filtros de la consulta principal se pueden empujar hacia dentro. DuckDB acepta las mismas palabras clave MATERIALIZED y NOT MATERIALIZED.

El viejo consejo de que «las CTE son una barrera para el optimizador» viene de PostgreSQL 11 y anteriores, que siempre las materializaban. Si lo lees en un artículo antiguo, ese es el motivo.

Notas de portabilidad

• PostgreSQL, DuckDB, MySQL 8+, SQLite, BigQuery y Snowflake admiten WITH y WITH RECURSIVE.
• SQL Server admite CTE y recursión, pero no usa la palabra RECURSIVE: se escribe WITH a secas. Además necesita un punto y coma antes de WITH si la sentencia anterior no terminó con uno, y por eso ves ;WITH en código de SQL Server.
• Las CTE que modifican datos (WITH deleted AS (DELETE ... RETURNING *) SELECT ...) son una característica de PostgreSQL. DuckDB y la mayoría de bases de datos solo permiten SELECT dentro de una CTE.
• El cast 'UUS'::VARCHAR del ancla fija el tipo de la columna para las dos partes de la recursión. PostgreSQL exige que los tipos del ancla y de la parte recursiva coincidan; CAST('UUS' AS VARCHAR) es la forma portable.

Errores frecuentes

1. Una coma después de la última CTE. Las CTE se separan con comas, pero entre la última y el SELECT principal no va coma. ), SELECT ... es un error de sintaxis.
2. Escribir WITH dos veces. Una sentencia tiene un solo WITH; las demás CTE van detrás, separadas por comas: WITH a AS (...), b AS (...).
3. Esperar que una CTE sobreviva a la sentencia. Después del punto y coma, el nombre desaparece. Si la siguiente consulta lo necesita, repite la CTE o crea una tabla temporal.
4. Confiar en un ORDER BY dentro de una CTE. No fija el orden del resultado final. Ordena en la consulta principal.
5. Una CTE recursiva sin condición de parada. Limita siempre la profundidad o asegúrate de que cada vuelta acabe devolviendo cero filas. UNION en lugar de UNION ALL ayuda cuando los caminos vuelven a pasar por las mismas filas.
6. Una cadena larga que nadie revisa. Siete CTE solo son legibles si cada una es correcta. Mira el número de filas de cada paso mientras construyes la cadena, sobre todo después de los JOIN.

Si quieres ver las formas con subconsultas que las CTE sustituyen, lee la guía de subconsultas SQL. Muchas cadenas de CTE analíticas terminan en una función de ventana; la guía de funciones de ventana y la de retención por cohortes, en inglés muestran cadenas reales más largas.

Preguntas frecuentes

¿Qué es una CTE en SQL?

Una expresión de tabla común: un resultado temporal con nombre que se define con WITH nombre AS (SELECT ...) y que usa la consulta que va a continuación. Solo existe durante esa sentencia.

¿Qué diferencia hay entre una CTE y una subconsulta?

Lógicamente, ninguna: una CTE que se usa una vez se comporta como una tabla derivada. Las diferencias son prácticas. Una CTE tiene nombre, va arriba del todo para que la consulta se lea en orden, se puede referenciar varias veces y puede ser recursiva.

¿Puedo usar varias CTE en una consulta?

Sí. Escribe WITH una vez y separa las CTE con comas. Cada CTE puede usar las que se han definido antes.

¿Las CTE son más lentas que las subconsultas?

No en las versiones actuales de PostgreSQL ni en DuckDB: una CTE que se usa una vez se integra como una subconsulta. En PostgreSQL 11 y anteriores, las CTE siempre se materializaban, lo que podía bloquear optimizaciones. Usa MATERIALIZED o NOT MATERIALIZED si necesitas controlarlo.

¿Para qué sirve una CTE recursiva?

Para jerarquías y grafos: organigramas, árboles de categorías, rutas entre aeropuertos, cadenas de eventos. Aplica una consulta una y otra vez sobre su propio resultado anterior hasta que no aparecen filas nuevas.

También en English, Português (Brasil), Tiếng Việt, 简体中文