Docs ejecutables de SQLite: CTEs recursivas

Q: ¿Qué es una CTE recursiva en SQLite?

Una CTE recursiva es una consulta WITH RECURSIVE que va construyendo el resultado refiriéndose a sí misma una y otra vez. Tiene dos partes unidas con UNION ALL: una consulta ancla, que devuelve las filas iniciales, y una consulta recursiva, que genera más filas a partir del paso anterior. SQLite repite la parte recursiva hasta que deja de producir filas nuevas.

Q: ¿Cuándo conviene usar WITH RECURSIVE en SQLite?

Cuando necesitas recorrer un árbol o un grafo (empleados y jefes, categorías y subcategorías, comentarios anidados) o generar una secuencia (todas las fechas de un rango, los números del 1 al 100). Con un JOIN normal te apañas para uno o dos niveles, pero una CTE recursiva te permite bajar a profundidad arbitraria sin saber de antemano cuántos niveles hay.

Q: ¿Cómo evito bucles infinitos en una CTE recursiva de SQLite?

Asegúrate de que la parte recursiva tenga una condición de parada: un WHERE que acabe devolviendo cero filas, o un contador con un tope. Si trabajas con grafos que pueden tener ciclos, guarda el camino recorrido en una columna y descarta las filas que ya estén ahí. Como red de seguridad, añade un LIMIT en la consulta externa para que una recursión descontrolada no se coma toda la memoria.

La recursión en SQL suena raro… hasta que la ves en acción

La mayoría de las consultas devuelven filas a partir de datos que ya existen. Una CTE recursiva en SQLite funciona distinto: va construyendo filas tomando su propia salida como entrada, paso a paso, hasta que ya no hay nada nuevo que añadir. Así es como recorres un árbol de profundidad desconocida, o generas los números del 1 al 100 sin necesidad de una tabla de números.

La estructura siempre es la misma:

WITH RECURSIVE name(columns) AS (
    -- ancla: las filas iniciales
    SELECT ...
    UNION ALL
    -- recursivo: filas derivadas del paso anterior
    SELECT ... FROM name WHERE ...
)
SELECT * FROM name;

Ancla arriba, UNION ALL en medio y la consulta recursiva debajo. SQLite ejecuta el ancla una sola vez y luego repite la parte recursiva una y otra vez —usando en cada vuelta las filas que produjo la iteración anterior— hasta que ya no aparecen filas nuevas. En ese momento se detiene.

Contar del 1 al 10 con una CTE recursiva

El ejemplo más sencillo de CTE recursiva en SQLite es generar una serie numérica. Y lo mejor: sin necesidad de tablas:

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

Vamos paso a paso:

El ancla produce una fila: n = 1.
El paso recursivo toma esa fila, calcula n + 1 = 2, y como 2 < 10 se cumple, se queda con la fila.
La siguiente iteración parte de n = 2 y genera n = 3. Y así sucesivamente.
Cuando n llega a 10, 10 < 10 es falso, el paso recursivo no devuelve filas y SQLite se detiene.

El WHERE n < 10 es la condición de parada. Sin ella, la consulta se ejecuta hasta el infinito.

Generar una serie de fechas en SQLite

La misma idea, pero muy útil en informes reales: rellenar todos los días de un rango, incluso aquellos en los que no pasó nada:

WITH RECURSIVE days(d) AS (
    SELECT date('2024-01-01')
    UNION ALL
    SELECT date(d, '+1 day') FROM days WHERE d < '2024-01-07'
)
SELECT d FROM days;

Lo habitual es hacer un LEFT JOIN de esto contra una tabla de eventos para contar bien los días sin actividad. Un GROUP BY date a secas se salta los días vacíos; en cambio, la serie de fechas te garantiza una fila por cada día, haya o no eventos.

Recorrer un árbol padre-hijo en SQLite

El caso de uso clásico de la CTE recursiva. Aquí tienes una tabla de empleados donde cada fila apunta a su jefe:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE org(id, name, manager_id, depth) AS (
    SELECT id, name, manager_id, 0
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, org.depth + 1
    FROM employees e
    JOIN org ON e.manager_id = org.id
)
SELECT printf('%.*c%s', depth * 2, ' ', name) AS tree
FROM org;

El ancla selecciona la raíz (la persona que no tiene jefe). El paso recursivo hace un join entre la tabla de empleados y la propia CTE, buscando a todos aquellos cuyo manager_id coincida con algún id que ya esté dentro de la CTE. Cada iteración baja un nivel más en la jerarquía. La columna depth no es más que un contador que agregamos para indentar la salida.

Esto funciona para árboles de cualquier profundidad. Dos niveles, diez niveles… la consulta es siempre la misma.

Encontrar todos los ancestros de una fila concreta

Cambiemos el sentido. En vez de descender desde la raíz, vamos a recorrer el árbol hacia arriba partiendo de un empleado concreto para obtener toda su cadena de jefes:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE chain(id, name, manager_id) AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE name = 'Diego'
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain ON chain.manager_id = e.id
)
SELECT name FROM chain;

El ancla es el empleado de partida. En cada paso recursivo saltamos al jefe directo. SQLite corta cuando llega a la raíz: como manager_id IS NULL, el join no encuentra nada y la recursión termina.

Este patrón viene de perlas para migas de pan, comentarios anidados, rutas de categorías y, en general, cualquier escenario donde necesites "subir hasta el tope" en una consulta jerárquica SQLite.

Condiciones de parada y bucles infinitos en CTE recursivas

El bug más típico al escribir una CTE recursiva en SQLite es olvidar la condición de parada, o ponerla de tal forma que nunca se cumple. Compara los dos casos:

-- Se ejecuta indefinidamente:
WITH RECURSIVE bad(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM bad
)
SELECT n FROM bad;

No hay ninguna cláusula WHERE que llegue a devolver cero filas, así que SQLite se pondrá feliz a contar hasta el infinito.

Dos hábitos defensivos que te conviene adoptar:

Incluye siempre una cláusula WHERE en la parte recursiva que acote el crecimiento.
Añade un LIMIT al SELECT externo como red de seguridad mientras estás desarrollando: si te equivocas con la condición de parada, al menos la consulta termina.

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter
)
SELECT n FROM counter LIMIT 5;

El CTE en sí no tiene límite, pero el LIMIT 5 corta la consulta externa cuanto antes. SQLite es lo bastante listo como para no seguir recursando más allá de lo que pide el LIMIT. Va bien para explorar, pero no sustituye a una condición de parada real en código de producción.

Ciclos en grafos

Los árboles no pueden tener ciclos. Los grafos en general sí, y una CTE recursiva ingenua se quedará en un bucle infinito si los datos los contienen. La solución es ir guardando el camino recorrido y evitar volver a pasar por los mismos nodos:

CREATE TABLE edges (a INTEGER, b INTEGER);
INSERT INTO edges VALUES (1, 2), (2, 3), (3, 1), (3, 4);

WITH RECURSIVE walk(node, path) AS (
    SELECT 1, ',1,'
    UNION ALL
    SELECT e.b, path || e.b || ','
    FROM edges e
    JOIN walk ON e.a = walk.node
    WHERE instr(path, ',' || e.b || ',') = 0
)
SELECT node, path FROM walk;

path es una cadena de nodos ya visitados, separados por comas. Antes de añadir un nodo nuevo, la cláusula WHERE comprueba que no esté ya ahí. Sin esa protección, el ciclo 1 → 2 → 3 → 1 se repetiría hasta el infinito.

En SQL no existe un "conjunto de visitados" nativo: te toca construirlo tú, normalmente como una cadena de texto o haciendo un join contra el propio CTE acumulado.

CTE recursiva vs self join

Si solo necesitas bajar uno o dos niveles, un self join es más sencillo y rápido:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

SELECT e.name AS empleado, m.name AS jefe
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Eso resuelve "quién es el jefe directo de cada persona". Pero si necesitas "todas las personas que dependen, directa o indirectamente, de Ada, sin importar cuántos niveles haya", la profundidad es desconocida y solo una CTE recursiva lo resuelve con elegancia. Elige la herramienta según la profundidad que necesites:

Profundidad fija y pequeña: un self join, o quizá dos o tres encadenados.
Profundidad desconocida o arbitraria: WITH RECURSIVE.

Modelo mental

Una CTE recursiva en SQLite es un bucle, pero escrito de forma declarativa:

El ancla es el valor inicial del bucle.
La consulta recursiva es el cuerpo del bucle: produce el siguiente lote de filas a partir de las actuales.
La condición de parada es la prueba de salida: cuando devuelve cero filas, el bucle termina.
UNION ALL va acumulando todo en el conjunto de resultados final.

Cuando esto te encaja en la cabeza, la sintaxis deja de parecerte rara. Lo que estás escribiendo es, básicamente, un for en SQL.

Lo que viene: índices

Una CTE recursiva recorre muchísimas filas, y el join del paso recursivo se ejecuta en cada iteración. Si la columna del join no está indexada, el rendimiento se desploma enseguida. Los índices son el tema del próximo capítulo, y manager_id es justo el tipo de columna que sale ganando con uno.

Preguntas frecuentes

¿Qué es una CTE recursiva en SQLite?

Una CTE recursiva es una consulta WITH RECURSIVE que va construyendo el resultado refiriéndose a sí misma una y otra vez. Tiene dos partes unidas con UNION ALL: una consulta ancla, que devuelve las filas iniciales, y una consulta recursiva, que genera más filas a partir del paso anterior. SQLite repite la parte recursiva hasta que deja de producir filas nuevas.

¿Cuándo conviene usar WITH RECURSIVE en SQLite?

Cuando necesitas recorrer un árbol o un grafo (empleados y jefes, categorías y subcategorías, comentarios anidados) o generar una secuencia (todas las fechas de un rango, los números del 1 al 100). Con un JOIN normal te apañas para uno o dos niveles, pero una CTE recursiva te permite bajar a profundidad arbitraria sin saber de antemano cuántos niveles hay.

¿Cómo evito bucles infinitos en una CTE recursiva de SQLite?

Asegúrate de que la parte recursiva tenga una condición de parada: un WHERE que acabe devolviendo cero filas, o un contador con un tope. Si trabajas con grafos que pueden tener ciclos, guarda el camino recorrido en una columna y descarta las filas que ya estén ahí. Como red de seguridad, añade un LIMIT en la consulta externa para que una recursión descontrolada no se coma toda la memoria.

Conceptos relacionados

CTEs recursivas en SQLite: WITH RECURSIVE paso a paso