CTE en SQL: qué son y cómo usarlas con ejemplos

Una CTE en SQL (Common Table Expression) es un resultado temporal con nombre que existe durante una sola sentencia. Se define con WITH y permite dividir una consulta compleja en pasos legibles, reutilizar un cálculo y recorrer jerarquías mediante recursividad.

No crea una tabla permanente. Es una forma de organizar la lógica para que una consulta sea más fácil de leer, probar y mantener.

Sintaxis básica de una CTE

WITH ventas_por_cliente AS (
    SELECT
        cliente_id,
        SUM(total) AS importe_total
    FROM ventas
    GROUP BY cliente_id
)
SELECT
    cliente_id,
    importe_total
FROM ventas_por_cliente
WHERE importe_total > 10000;

La consulta dentro de paréntesis produce un conjunto llamado ventas_por_cliente. La sentencia principal lo usa como si fuera una tabla.

Su alcance termina al finalizar la sentencia. No puedes ejecutar otro SELECT después y esperar que siga existiendo.

Para qué sirve una CTE

• Separar transformaciones largas en etapas.
• Evitar repetir la misma subconsulta.
• Dar nombres claros a cálculos intermedios.
• Usar el resultado en SELECT, INSERT, UPDATE o DELETE, según el motor.
• Recorrer organigramas, categorías y otras jerarquías.
• Facilitar revisiones y pruebas por bloques.

Una CTE mejora la estructura, pero no garantiza por sí misma mayor velocidad.

Ejemplo con varias CTE

Queremos identificar clientes activos y calcular su ticket medio:

WITH ventas_validas AS (
    SELECT
        cliente_id,
        fecha,
        total
    FROM ventas
    WHERE estado = 'completada'
),
resumen_cliente AS (
    SELECT
        cliente_id,
        COUNT(*) AS pedidos,
        SUM(total) AS facturacion,
        AVG(total) AS ticket_medio
    FROM ventas_validas
    GROUP BY cliente_id
)
SELECT
    cliente_id,
    pedidos,
    facturacion,
    ticket_medio
FROM resumen_cliente
WHERE pedidos >= 3
ORDER BY facturacion DESC;

Cada bloque tiene una responsabilidad. Puedes ejecutar su consulta interior por separado para validar el resultado.

CTE vs subconsulta

Ambas pueden expresar la misma lógica:

SELECT *
FROM (
    SELECT cliente_id, SUM(total) AS facturacion
    FROM ventas
    GROUP BY cliente_id
) AS resumen
WHERE facturacion > 10000;

Criterio	CTE	Subconsulta
Legibilidad en consultas largas	Alta	Puede anidarse demasiado
Reutilización dentro de la sentencia	Sí	Normalmente requiere repetir
Recursividad	Sí	No de la misma forma
Consulta corta de una sola vez	Puede ser excesiva	Muy práctica
Rendimiento	Depende del optimizador	Depende del optimizador

No uses CTE como adorno. Una subconsulta SQL pequeña dentro de EXISTS puede ser más natural.

CTE con funciones de ventana

Una aplicación común es conservar el registro más reciente por cliente:

WITH pedidos_ordenados AS (
    SELECT
        pedido_id,
        cliente_id,
        fecha,
        total,
        ROW_NUMBER() OVER (
            PARTITION BY cliente_id
            ORDER BY fecha DESC, pedido_id DESC
        ) AS posicion
    FROM pedidos
)
SELECT
    pedido_id,
    cliente_id,
    fecha,
    total
FROM pedidos_ordenados
WHERE posicion = 1;

No puedes filtrar ROW_NUMBER() directamente en el mismo nivel de WHERE en muchos motores, porque la ventana se calcula después. La CTE crea la etapa necesaria.

Cómo actualizar con una CTE

La compatibilidad cambia por motor, pero en SQL Server y PostgreSQL puedes usar el resultado para acotar una actualización. Un patrón portable es:

WITH clientes_objetivo AS (
    SELECT cliente_id
    FROM ventas
    GROUP BY cliente_id
    HAVING SUM(total) >= 50000
)
UPDATE clientes
SET nivel = 'premium'
WHERE cliente_id IN (
    SELECT cliente_id
    FROM clientes_objetivo
);

Ejecuta primero un SELECT con la misma condición y usa transacciones en cambios masivos.

Qué es una CTE recursiva

Una CTE recursiva se refiere a sí misma. Contiene:

1. un miembro ancla que inicia el recorrido;
2. un miembro recursivo que encuentra el siguiente nivel;
3. una condición que detiene la expansión.

Ejemplo de jerarquía de empleados:

WITH RECURSIVE organigrama AS (
    SELECT
        empleado_id,
        jefe_id,
        nombre,
        0 AS nivel
    FROM empleados
    WHERE jefe_id IS NULL

    UNION ALL

    SELECT
        e.empleado_id,
        e.jefe_id,
        e.nombre,
        o.nivel + 1
    FROM empleados AS e
    JOIN organigrama AS o
      ON e.jefe_id = o.empleado_id
)
SELECT *
FROM organigrama
ORDER BY nivel, jefe_id, empleado_id;

PostgreSQL, MySQL y SQLite utilizan WITH RECURSIVE; SQL Server omite la palabra RECURSIVE. Añade protección contra ciclos y límites de profundidad cuando los datos puedan contener relaciones defectuosas.

¿Una CTE mejora el rendimiento?

No necesariamente. El optimizador puede:

• integrarla en la consulta principal;
• materializar el resultado;
• recalcular referencias;
• aplicar filtros antes de producir todas las filas.

El comportamiento varía por motor, versión y consulta. Revisa el plan de ejecución. Si un resultado pesado se reutiliza muchas veces, una tabla temporal con índices puede ser mejor.

Buenas prácticas

• Usa nombres que describan el resultado, no cte1.
• Especifica columnas y evita SELECT *.
• Filtra pronto cuando sea correcto.
• Separa etapas lógicas, no cada línea.
• Evita recursión sin condición de salida.
• Ordena solo en el resultado final salvo que una operación lo requiera.
• Comprueba índices en columnas de filtros y joins.
• Documenta supuestos de fechas, estados y duplicados.

Antes de usar CTE avanzadas, conviene dominar los fundamentos para recopilar datos con SQL, incluidos GROUP BY y los distintos tipos de JOIN.

Preguntas frecuentes

¿Qué significa CTE en SQL?
Significa Common Table Expression: una expresión de tabla común que asigna un nombre temporal al resultado de una consulta.

¿Una CTE crea una tabla física?
No. Su nombre solo existe durante la sentencia, aunque el motor puede materializar internamente el resultado como parte del plan.

¿Puedo usar varias CTE en una consulta?
Sí. Se separan con comas después de un único WITH, y una CTE posterior puede usar una anterior.

¿CTE y vista son lo mismo?
No. La vista queda guardada como objeto de base de datos; la CTE solo vive en una sentencia.

¿Cuándo usar una CTE recursiva?
Para recorrer jerarquías, árboles, rutas o secuencias cuando cada nivel depende del anterior.

¿Una CTE siempre es más rápida que una subconsulta?
No. El rendimiento depende del optimizador y del plan. El beneficio principal suele ser claridad y capacidad recursiva.