El manejo de bases de datos es una competencia fundamental en el mundo del desarrollo y la analítica de datos. Las consultas complejas en SQL pueden volverse difíciles de leer y mantener, especialmente cuando incluyen múltiples subconsultas anidadas o joins con varias tablas. Aquí es donde las CTEs (Common Table Expressions) se convierten en una herramienta imprescindible, ya que permiten escribir consultas más limpias, legibles y reutilizables.

En este artículo vais a aprender qué son las CTEs, cómo se construyen, cómo aplicarlas en consultas complejas y cuáles son las mejores prácticas para utilizarlas en entornos de producción.

Qué es una CTE

Una CTE es básicamente una subconsulta temporal que podéis definir dentro de una consulta más grande. La diferencia principal con una subconsulta tradicional es que las CTEs son:

• Nombradas: podéis referiros a ellas varias veces en la misma consulta.

• Legibles: la estructura de la consulta se vuelve más clara.

• Reutilizables: permiten simplificar lógica compleja sin repetir código.

La sintaxis básica de una CTE es la siguiente:

WITH nombre_cte AS (
    SELECT columna1, columna2
    FROM tabla
    WHERE condición
)
SELECT *
FROM nombre_cte;

Con esto, la CTE nombre_cte actúa como una tabla temporal que solo existe durante la ejecución de la consulta.

Ventajas de usar CTEs en SQL

Utilizar CTEs ofrece varias ventajas:

1. Legibilidad: Separar la lógica en bloques hace que la consulta sea más comprensible.

2. Mantenimiento más fácil: Si necesitas modificar la lógica de una parte de la consulta, solo cambias la CTE correspondiente.

3. Reutilización: Podéis usar la misma CTE varias veces dentro de la consulta principal.

4. Soporte para recursividad: SQL permite crear CTEs recursivas para resolver problemas como jerarquías o recorridos de grafos.

CTEs simples

Empecemos con un ejemplo sencillo. Imaginad que tenéis una tabla empleados y queréis obtener solo los empleados de un departamento específico y luego contar cuántos hay.

WITH empleados_ventas AS (
    SELECT id, nombre, departamento
    FROM empleados
    WHERE departamento = 'Ventas'
)
SELECT COUNT(*) AS total_ventas
FROM empleados_ventas;

En este caso:

• La CTE empleados_ventas selecciona solo los empleados del departamento de ventas.

• La consulta principal cuenta cuántos empleados hay en esa CTE.

Sin CTE, tendríais que escribir la subconsulta directamente dentro del FROM o el WHERE, lo que puede dificultar la lectura.

CTEs múltiples

Podéis definir varias CTEs dentro de la misma consulta, separadas por comas. Esto es útil cuando queréis construir la consulta paso a paso.

WITH empleados_ventas AS (
    SELECT id, nombre, salario
    FROM empleados
    WHERE departamento = 'Ventas'
),
empleados_marketing AS (
    SELECT id, nombre, salario
    FROM empleados
    WHERE departamento = 'Marketing'
)
SELECT 'Ventas' AS departamento, AVG(salario) AS salario_medio
FROM empleados_ventas
UNION ALL
SELECT 'Marketing', AVG(salario)
FROM empleados_marketing;

Aquí podéis ver cómo se usan dos CTEs para separar los datos de ventas y marketing y luego calcular el salario medio de cada departamento.

CTEs recursivas

Una de las funcionalidades más potentes de las CTEs en SQL es la posibilidad de crear CTEs recursivas, ideales para trabajar con datos jerárquicos como árboles o estructuras organizativas.

La sintaxis general es:

WITH RECURSIVE nombre_cte AS (
    -- Caso base
    SELECT columna1, columna2
    FROM tabla
    WHERE condición_base
    UNION ALL
    -- Paso recursivo
    SELECT t.columna1, t.columna2
    FROM tabla t
    INNER JOIN nombre_cte cte
        ON t.columna_padre = cte.columna1
)
SELECT *
FROM nombre_cte;

Ejemplo práctico: obtención de todos los subordinados de un empleado en la tabla empleados:

WITH RECURSIVE subordinados AS (
    SELECT id, nombre, jefe_id
    FROM empleados
    WHERE jefe_id IS NULL  -- Director principal
    UNION ALL
    SELECT e.id, e.nombre, e.jefe_id
    FROM empleados e
    INNER JOIN subordinados s
        ON e.jefe_id = s.id
)
SELECT *
FROM subordinados;

En este ejemplo:

• El caso base selecciona al director principal (jefe_id IS NULL).

• El paso recursivo añade a los empleados que dependen de los ya seleccionados.

Con este patrón, podéis explorar estructuras jerárquicas de cualquier profundidad sin escribir múltiples joins manuales.

Reglas y buenas prácticas al usar CTEs

Aunque las CTEs son muy útiles, es importante seguir algunas recomendaciones:

1. No abusar de la recursividad: Una CTE recursiva mal planteada puede generar loops infinitos. Usad LIMIT o condiciones claras de terminación.

2. Nombrar las CTEs de forma clara: Nombres descriptivos ayudan a que la consulta sea legible.

3. Evitar CTEs muy grandes en bases de datos lentas: Las CTEs no siempre se optimizan como subconsultas materializadas. Si el rendimiento es crítico, probad con tablas temporales.

4. Usar comentarios: Explicar la lógica de cada CTE facilita el mantenimiento.

5. Encadenar CTEs: Aprovechad la capacidad de encadenar varias CTEs para dividir la lógica en pasos claros y secuenciales.

Ejemplo avanzado con SQL

Supongamos que queréis analizar las ventas de una empresa y obtener, para cada departamento, los tres empleados con mayores ventas. Podéis estructurar la consulta así:

WITH ventas_totales AS (
    SELECT id_empleado, departamento, SUM(monto) AS total_ventas
    FROM ventas
    GROUP BY id_empleado, departamento
),
ranking_ventas AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY departamento ORDER BY total_ventas DESC) AS ranking
    FROM ventas_totales
)
SELECT departamento, id_empleado, total_ventas
FROM ranking_ventas
WHERE ranking <= 3
ORDER BY departamento, total_ventas DESC;

En este ejemplo:

• La primera CTE (ventas_totales) calcula la suma de ventas por empleado y departamento.

• La segunda CTE (ranking_ventas) asigna un ranking por departamento usando la función ROW_NUMBER().

• La consulta final selecciona solo los tres mejores empleados por departamento.

Gracias a las CTEs, esta consulta es mucho más legible que si anidase subconsultas con joins y agregaciones.

Ventajas de usar CTEs frente a subconsultas

Como podéis ver, las CTEs son la opción preferida en SQL moderno para consultas complejas.

Integración con otras funcionalidades de SQL

Las CTEs se integran muy bien con:

• Funciones de ventana (ROW_NUMBER(), RANK(), SUM() OVER())

• Joins complejos entre varias tablas

• Operaciones de agregación con GROUP BY

• Filtros avanzados usando WHERE o HAVING

Por ejemplo, podéis calcular medias móviles, totales acumulados o rankings jerárquicos de manera mucho más clara con CTEs que con subconsultas anidadas.

Conclusión

Las CTEs son una herramienta poderosa en SQL que simplifica la construcción de consultas complejas, mejora la legibilidad y permite la reutilización de lógica. Tanto para desarrolladores como para analistas de datos, dominar esta técnica es clave para trabajar de manera más eficiente y mantener el código limpio.

Al aplicar CTEs simples, múltiples o recursivas, podréis abordar problemas que antes requerían múltiples subconsultas o joins complicados, optimizando vuestro flujo de trabajo y reduciendo errores.

En definitiva, las CTEs en SQL no solo facilitan la escritura de consultas avanzadas, sino que también permiten organizar la lógica de manera clara, preparar datos para análisis complejos y mantener proyectos más sostenibles a largo plazo.

Aprende sobre Bases de Datos con la Ruta de Frogames Formación

Si te ha interesado lo que te hemos contado en este post, te encantará saber que puedes profundizar en este tema y en todas las habilidades relacionadas con las Bases de Datos a través de la ruta de aprendizaje de Frogames Formación.

Esta ruta está diseñada para quienes quieren empezar desde cero y avanzar con paso firme, aprendiendo de forma práctica y sencilla todo lo necesario para dominar el mundo de las Bases de Datos, desde el diseño de modelos relacionales y consultas SQL hasta la administración avanzada, optimización de rendimiento y bases de datos en memoria.

Además, la ruta de Bases de Datos de Frogames Formación abarca diferentes módulos que cubren desde los fundamentos esenciales hasta técnicas avanzadas, para que podáis aplicar vuestros conocimientos en proyectos reales con confianza y profesionalidad.

Si queréis convertir el dominio de las Bases de Datos en una de vuestras principales competencias y descubrir cómo diseñar, gestionar y escalar sistemas eficientes, esta ruta formativa es la opción perfecta para vosotros. ¡No dejéis pasar la oportunidad de mejorar vuestras habilidades y crecer profesionalmente!