CTE en SQL: organiza y simplifica tus consultas

Cuando trabajas con consultas SQL complejas —que incluyen subconsultas anidadas, múltiples JOIN o cálculos intermedios— el código puede volverse difícil de leer y mantener.

Ahí es donde entran las CTE (Common Table Expressions), una de las herramientas más poderosas y limpias del lenguaje SQL.

Con una CTE puedes:

Dividir una consulta grande en partes más legibles.
Reutilizar resultados intermedios dentro de una misma sentencia.
Mejorar la estructura lógica de tus consultas.

En este artículo aprenderás qué son las CTE, cómo se crean y cómo aplicarlas con ejemplos que puedes practicar en db-fiddle.com.

¿Qué es una CTE?

Una CTE (Common Table Expression) es una expresión temporal que puedes definir al inicio de una consulta SQL y luego usar dentro de ella como si fuera una tabla o vista.

Se declaran con la palabra clave WITH, justo antes de la instrucción principal (SELECT, INSERT, UPDATE, DELETE).

Sintaxis básica

WITH nombre_cte AS (
    SELECT columnas
    FROM tabla
    WHERE condiciones
)
SELECT *
FROM nombre_cte;

💡 Piensa en una CTE como una “vista temporal” disponible solo durante la ejecución de esa consulta.

Ejemplo 1: Filtrar empleados con salario alto y luego calcular promedio

Usando la tabla empleados:

WITH empleados_altos AS (
    SELECT nombre, puesto, salario, id_departamento
    FROM empleados
    WHERE salario >= 75000
)
SELECT id_departamento,
       AVG(salario) AS salario_promedio
FROM empleados_altos
GROUP BY id_departamento;

✅ Aquí, la CTE empleados_altos actúa como una tabla temporal que almacena empleados con salarios altos, y luego la usamos para obtener el promedio por departamento.

Ejemplo 2: CTE con JOIN

Supongamos que también tenemos la tabla departamentos:

CREATE TABLE departamentos (
    id_departamento INT PRIMARY KEY,
    nombre_departamento VARCHAR(100)
);

INSERT INTO departamentos VALUES
(1, 'Ventas'),
(2, 'Marketing'),
(3, 'Finanzas'),
(4, 'Desarrollo');

Podemos usar una CTE para unir ambas tablas:

WITH empleados_con_departamento AS (
    SELECT e.nombre,
           e.puesto,
           e.salario,
           d.nombre_departamento
    FROM empleados e
    JOIN departamentos d
    ON e.id_departamento = d.id_departamento
)
SELECT *
FROM empleados_con_departamento
WHERE salario > 70000;

Ejemplo 3: CTE anidadas (encadenadas)

Puedes definir varias CTE en una sola consulta, separadas por comas:

WITH empleados_activos AS (
    SELECT * FROM empleados WHERE salario > 60000
),
promedios AS (
    SELECT id_departamento, AVG(salario) AS salario_promedio
    FROM empleados_activos
    GROUP BY id_departamento
)
SELECT *
FROM promedios
WHERE salario_promedio > 70000;

Ejemplo 4: CTE recursiva (nivel avanzado)

Las CTE también pueden ser recursivas, es decir, llamarse a sí mismas.
Esto es útil para trabajar con estructuras jerárquicas (como organigramas o árboles de dependencias).

Ejemplo conceptual:

WITH RECURSIVE jerarquia AS (
    SELECT id_empleado, nombre, jefe_id
    FROM empleados
    WHERE jefe_id IS NULL
    UNION ALL
    SELECT e.id_empleado, e.nombre, e.jefe_id
    FROM empleados e
    INNER JOIN jerarquia j ON e.jefe_id = j.id_empleado
)
SELECT * FROM jerarquia;

(Este ejemplo aplica en motores como PostgreSQL y SQL Server.)

Buenas prácticas

Usa CTE para dividir consultas largas. Mejora la legibilidad.
Nombra tus CTE de forma clara. Ej. ventas_por_mes, empleados_altos.
No abuses de las CTE encadenadas. En exceso pueden impactar el rendimiento.
Reutiliza cálculos intermedios. Evita repetir subconsultas.
Compara rendimiento con vistas o subconsultas. En algunos motores las CTE no se almacenan en memoria y se recalculan.

Diferencias entre motores

Motor SQL	Soporte de CTE	Particularidades
SQL Server	✅ Completo	Soporta CTE recursivas y múltiples CTE por consulta.
PostgreSQL	✅ Completo	Permite `WITH RECURSIVE`. Muy eficiente.
MySQL (8.0+)	✅ Desde versión 8	Antes no se soportaban.
SQLite	✅ Completo	Soporta `WITH RECURSIVE`. Ideal para aprendizaje.

Conclusión

Las CTE son una de las mejores formas de hacer tus consultas SQL más limpias, reutilizables y fáciles de mantener.
Te permiten trabajar paso a paso y mejorar la claridad sin sacrificar potencia.

Practica estos ejemplos en db-fiddle.com y descubre cómo simplifican incluso las consultas más complejas.