CTE recursivos: qué son y para qué sirven

Hoy vamos a dar un paso más y nos meteremos en la magia de la recursión. Si ya has programado alguna vez en un lenguaje que soporte recursión (por ejemplo, Python), más o menos te suena de qué va esto. Pero tranqui, si te suena a algo misterioso — lo vamos a desglosar todo muy a fondo.

Los CTE recursivos son una herramienta potente para trabajar con estructuras de datos jerárquicas, tipo árbol, como las estructuras organizativas de empresas, árboles genealógicos o catálogos de archivos.

Dicho fácil, son expresiones que pueden "llamarse a sí mismas" para ir recorriendo y procesando todos los niveles de los datos.

Características clave de los CTE recursivos:

1. Usan la palabra clave WITH RECURSIVE.
2. Un CTE recursivo tiene dos partes:
   • Consulta base: define el punto de inicio (o "raíz") de la recursión.
   • Consulta recursiva: procesa el resto de los datos usando el resultado del paso anterior.

El algoritmo de un CTE recursivo es como subir una escalera:

• Primero te pones en el primer escalón (esa es la consulta base).
• Luego subes al segundo escalón usando el resultado del primero (consulta recursiva).
• Este proceso se repite hasta que se acaban los escalones (cuando se cumple la condición de parada).

Sintaxis de un CTE recursivo

Vamos a ver un ejemplo típico:

WITH RECURSIVE cte_name AS (
    -- Consulta base
    SELECT column1, column2
    FROM table_name
    WHERE condition_for_base_case

    UNION ALL

    -- Consulta recursiva
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON some_condition
    WHERE stop_condition
)
SELECT * FROM cte_name;

El papel de UNION y UNION ALL en los CTE recursivos

Cada CTE recursivo debe usar los operadores UNION o UNION ALL entre la parte base y la recursiva.

¿Qué operador elegir: UNION o UNION ALL?

Si no tienes claro cuál usar — casi siempre elige UNION ALL. ¿Por qué? Porque va más rápido: simplemente junta los resultados sin mirar si hay duplicados. Eso significa menos cálculos, menos recursos y resultados más rápidos.

Esto es especialmente importante en CTE recursivos. Cuando construyes jerarquías — por ejemplo, un árbol de comentarios o la estructura de subordinados en una empresa — UNION ALL es casi siempre lo que necesitas. Si usas solo UNION, la base de datos puede pensar que algunos pasos ya se hicieron y “cortar” parte del resultado. Y eso rompe toda la lógica del recorrido.

Usa UNION solo si tienes clarísimo que los duplicados son malos y hay que quitarlos. Pero ojo: siempre es un compromiso entre limpieza y velocidad.

Ejemplo de los dos enfoques

-- UNION: los duplicados se eliminan
SELECT 'A'
UNION
SELECT 'A';     -- Resultado: una fila 'A'

-- UNION ALL: los duplicados se mantienen
SELECT 'A'
UNION ALL
SELECT 'A';     -- Resultado: dos filas 'A'

En consultas recursivas es más seguro usar siempre UNION ALL, para no perder pasos importantes al recorrer la estructura.

Veamos una tarea típica: tenemos una tabla de empleados con las columnas employee_id, manager_id y name. Hay que construir la jerarquía empezando por el director — la persona sin jefe (manager_id = NULL).

Supongamos que tenemos la tabla de empleados: employees

Tenemos que entender quién reporta a quién y saber el nivel de cada empleado en la estructura. Esto es útil, por ejemplo, si quieres mostrar el árbol de empleados en una interfaz o preparar un informe sobre la estructura del equipo.

WITH RECURSIVE employee_hierarchy AS (
    -- Empezamos con los que no tienen jefe
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Añadimos subordinados y subimos el nivel
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

El resultado será así:

Esta consulta muestra claramente cómo puedes "recorrer" la jerarquía de empleados — desde el director hasta los más juniors. El nivel (level) es útil para formatear o visualizar el árbol.

Ejemplo: categorías de productos

Ahora imagina que trabajamos con una tabla de categorías de productos, donde cada categoría puede tener subcategorías, y estas a su vez sus propias subcategorías. ¿Cómo montamos el árbol de categorías?

Tabla categories

Consulta recursiva:

WITH RECURSIVE category_tree AS (
    -- Caso base: encontrar las categorías raíz
    SELECT
        category_id,
        name,
        parent_id,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Parte recursiva: encontrar subcategorías de las categorías actuales
    SELECT
        c.category_id,
        c.name,
        c.parent_id,
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

Resultado:

Ahora vemos el árbol de categorías con los niveles de profundidad.

¿Por qué los CTE recursivos molan?

Los CTE recursivos son de las herramientas más expresivas y potentes de SQL. En vez de lógica anidada complicada, solo describes dónde empezar (caso base) y cómo seguir (parte recursiva) — el resto lo hace PostgreSQL.

Normalmente se usan para recorrer jerarquías: empleados, categorías de productos, directorios en disco, grafos en redes sociales. Se amplían fácil: si metes nuevos datos en la tabla, la consulta los pilla sola. Es cómodo y escalable.

Peeero, ojo con las trampas. Vigila siempre las condiciones de parada — sin ellas la consulta puede irse a un bucle infinito. No te olvides de los índices: en tablas grandes, las consultas recursivas sin ellos pueden ir lentísimas. Y UNION ALL — casi siempre es la mejor opción, sobre todo en tareas jerárquicas, si no te arriesgas a perder pasos de la recursión por eliminar duplicados.

Un CTE recursivo bien montado te deja expresar lógica de negocio compleja en unas pocas líneas — sin procedimientos, bucles ni código extra. Es de esos casos donde SQL no solo funciona bien, sino que queda elegante.

Errores típicos al trabajar con CTE recursivos

• Recursión infinita: si no pones una condición de parada correcta (WHERE), la consulta puede quedarse en bucle.
• Datos de más: usar mal UNION ALL mete duplicados.
• Rendimiento: las consultas recursivas pueden ser pesadas con muchos datos. Índices en columnas clave (por ejemplo, manager_id) ayudan a que vayan más rápido.

Cuándo no puedes vivir sin consultas recursivas

A veces parece que las consultas recursivas son cosa de teoría, pero en realidad salen mucho en el día a día del desarrollo. Por ejemplo:

• para hacer informes sobre la estructura de la empresa o la clasificación de productos;
• para recorrer el árbol de carpetas y sacar la lista de todos los directorios anidados;
• para analizar grafos — relaciones sociales, rutas, dependencias entre tareas;
• para simplemente mostrar relaciones complejas entre objetos de forma legible.

Si tienes que recorrer una estructura donde una cosa depende de otra — seguro que te va a venir bien WITH RECURSIVE.