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:

view plainprint?

1. CREATE TABLE #Empleados(idEmpleado INT, Nombre VARCHAR(10), idJefe INT)  
2.   
3. INSERT INTO #Empleados  
4. SELECT 0, 'Bob', null UNION  
5. SELECT 1, 'Tom', 0 UNION  
6. SELECT 2, 'Joe', null UNION  
7. SELECT 3, 'John', 2 UNION  
8. SELECT 4, 'Ringo', 1 UNION  
9. SELECT 5, 'Paul', 4   
10.   
11. 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.

view plainprint?

1. SELECT e.Nombre Empleado, j.Nombre Jefe  
2. FROM #Empleados e  
3. 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.

view plainprint?

1. WITH cte  
2. AS(  
3. -- Caso base  
4. SELECT e.idEmpleado, e.Nombre Nombre, e.idEmpleado Jefe  
5. FROM #Empleados e  
6. WHERE e.idJefe IS NULL  
7. -- Fin Caso base  
8. UNION ALL  
9. -- Caso recursivo  
10. SELECT e.idEmpleado, e.Nombre Nombre, j.Jefe  
11. FROM #Empleados e  
12. INNER JOIN cte j ON e.idJefe=j.idEmpleado  
13. -- Fin Caso recursivo  
14. )  
15. SELECT c.Nombre Empleado, j.Nombre Jefe  
16. FROM cte c  
17. INNER JOIN #Empleados j ON c.Jefe=j.idEmpleado  
18.   
19. 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.

Visita mi Blog dentro de los Blogs de Informática en Coobis.