La recursion es divina - Consultas recursivas en SQL

El lenguaje SQL no solía ser lo mas comodo para realizar consultas en donde participe el concepto de "recursión", pero claramente algunos problemas son muy incomodos si los abordamos de otra forma. Por suerte para nosotros, desde la aparición de las CTE, esto ha cambiado y ahora es "simple" realizar consultas recursivas, veremos un ejemplo de como podemos realizarlo.

Consideremos la siguiente tabla con datos de ejemplo:

CREATE TABLE #Empleados(idEmpleado INT, Nombre VARCHAR(10), idJefe INT)

INSERT INTO #Empleados
SELECT 0, 'Bob', null UNION
SELECT 1, 'Tom', 0 UNION
SELECT 2, 'Joe', null UNION
SELECT 3, 'John', 2 UNION
SELECT 4, 'Ringo', 1 UNION
SELECT 5, 'Paul', 4 

SELECT * FROM #Empleados

Esta tabla contiene los empleados de una empresa e indica sus respectivos jefes. Lo divertido es que los jefes son también empleados, por lo tanto son registros de la misma tabla.

Supongamos que quiero averiguar el nombre del jefe de cada empleado. Esto lo puedo realizar con un "self join" sin necesidad de recursividad.

SELECT e.Nombre Empleado, j.Nombre Jefe
FROM #Empleados e
INNER JOIN #Empleados j ON e.idJefe=j.idEmpleado;

Pero ahora supongamos que quiero una consulta mas compleja, una que me retorne no el jefe directo, sino el jefe superior de cada empleado. Cualquiera que tenga conocimientos de algoritmos sabe que la mejor forma de responder a esta pregunta es con una estructura recursiva. No pretendo en éste artículo escribir que es la recursión, sino como utilizarla en SQL, así que daré por sentado que saben de que hablo.

Una estructura recursiva requiere básicamente de dos partes:

1) El caso base

2) El caso recursivo

Para escribir esto utilizaremos una CTE que tendrá estas dos partes, y deben unirse con un UNION ALL. La primer parte será el caso base (en el WHERE debo filtrar para que lo sea) y en la segunda el caso recursivo, el cual hace un JOIN con la CTE. Este join es lo que indica que es un caso recursivo. Nótese que defino la cte utilizandola a ella misma, por eso mismo es recursiva.

WITH cte
AS(
-- Caso base
SELECT e.idEmpleado, e.Nombre Nombre, e.idEmpleado Jefe
FROM #Empleados e
WHERE e.idJefe IS NULL
-- Fin Caso base
UNION ALL
-- Caso recursivo
SELECT e.idEmpleado, e.Nombre Nombre, j.Jefe
FROM #Empleados e
INNER JOIN cte j ON e.idJefe=j.idEmpleado
-- Fin Caso recursivo
)
SELECT c.Nombre Empleado, j.Nombre Jefe
FROM cte c
INNER JOIN #Empleados j ON c.Jefe=j.idEmpleado

DROP TABLE #Empleados

Con esto obtengo el código del jefe superior de cada uno, entonces en el SELECT lo cruzo con la tabla de empleados para obtener su nombre. Parece medio confuso pero es cuestión de acostumbrarse.

Hay algunos inconvenientes que pueden presentarse, pero eso lo comentaré en el próximo artículo.