Cómo usar CTE recursivas en SQL para recorrer estructuras jerárquicas

En el mundo del análisis de datos, las estructuras jerárquicas son más comunes de lo que parecen: empleados y jefes, categorías y subcategorías, cuentas contables, productos agrupados por familias, entre otros.
Cuando se trabaja con este tipo de relaciones, las Consultas Recursivas con CTE (Common Table Expressions) son una herramienta poderosa que permite recorrer datos dependientes entre sí sin recurrir a procedimientos complejos.

En este artículo aprenderás cómo funcionan las CTE recursivas, cómo escribirlas correctamente, y cómo aplicarlas a escenarios reales.

¿Qué es una CTE recursiva?

Una CTE (Common Table Expression) es una consulta temporal que puedes definir dentro de una instrucción SQL.
Cuando se vuelve recursiva, significa que se hace referencia a sí misma, permitiendo recorrer niveles jerárquicos.

Su estructura general es:

WITH RECURSIVE nombre_cte AS (
    -- Parte base (consulta inicial)
    SELECT ...
    FROM ...
    WHERE ...
    
    UNION ALL
    
    -- Parte recursiva (referencia a la misma CTE)
    SELECT ...
    FROM ...
    JOIN nombre_cte
      ON ...
)
SELECT * FROM nombre_cte;

Ejemplo 1: Organigrama de empleados

Supongamos que tenemos una tabla empleados con estructura y datos como los siguientes:

CREATE TABLE empleados (
    id_empleado INT PRIMARY KEY,
    nombre VARCHAR(100),
    puesto VARCHAR(100),
    jefe_id INT
);

INSERT INTO empleados (id_empleado, nombre, puesto, jefe_id) VALUES
(1, 'Ana Torres', 'Directora General', NULL),
(2, 'Luis Pérez', 'Gerente de Ventas', 1),
(3, 'Marta Díaz', 'Gerente de Marketing', 1),
(4, 'Carlos López', 'Ejecutivo de Ventas', 2),
(5, 'Sofía Ruiz', 'Analista de Marketing', 3);

Queremos recorrer toda la jerarquía partiendo desde la directora general:

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

Resultado:

id_empleado	nombre	puesto	jefe_id	nivel
1	Ana Torres	Directora General	NULL	1
2	Luis Pérez	Gerente de Ventas	1	2
3	Marta Díaz	Gerente de Marketing	1	2
4	Carlos López	Ejecutivo de Ventas	2	3
5	Sofía Ruiz	Analista de Marketing	3	3

💡 Con esto puedes recorrer un organigrama completo sin procedimientos almacenados.

Ejemplo 2: Categorías y subcategorías

CREATE TABLE categorias (
    id_categoria INT PRIMARY KEY,
    nombre_categoria VARCHAR(100),
    id_padre INT
);

INSERT INTO categorias (id_categoria, nombre_categoria, id_padre) VALUES
(1, 'Electrónica', NULL),
(2, 'Computadoras', 1),
(3, 'Laptops', 2),
(4, 'Teléfonos', 1);

Consulta:

WITH RECURSIVE jerarquia_categorias AS (
    SELECT id_categoria, nombre_categoria, id_padre, 1 AS nivel
    FROM categorias
    WHERE id_padre IS NULL
    
    UNION ALL
    
    SELECT c.id_categoria, c.nombre_categoria, c.id_padre, j.nivel + 1
    FROM categorias c
    JOIN jerarquia_categorias j ON c.id_padre = j.id_categoria
)
SELECT * FROM jerarquia_categorias;

Buenas prácticas

Siempre define una condición base para evitar bucles infinitos.
Utiliza UNION ALL (no UNION) para mantener el rendimiento.
Usa un campo auxiliar (nivel) para identificar profundidad jerárquica.
Aplica índices en las columnas de relación (jefe_id, id_padre).

Compatibilidad entre motores

Motor	Palabra clave	Soporte
SQL Server	`WITH` (sin `RECURSIVE`)	✅
PostgreSQL	`WITH RECURSIVE`	✅
MySQL	`WITH RECURSIVE` (desde 8.0)	✅
SQLite	`WITH RECURSIVE`	✅

Conclusión

Las CTE recursivas son ideales para recorrer jerarquías de datos, estructuras de árbol o relaciones padre-hijo sin recurrir a bucles de programación.
Dominar su estructura te permitirá escribir consultas más limpias, eficientes y fáciles de mantener.

Practica en línea

Puedes ver y ejecutar estos ejemplos en 👉 db-fiddle.com