lunes, 12 de septiembre de 2011

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.

3 comentarios:

  1. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  2. 0 Bob NULL
    1 Tom 0
    2 Joe NULL
    3 John 2
    4 Ringo 1
    5 Paul 4

    Empleado Jefe
    Bob Bob
    Tom Bob
    Ringo Bob
    Paul Bob
    Joe Joe
    John Joe

    no me entrega el jefe superior inmediato, si no el jefe del jefe.
    Ringo seria el caso, pues su jefe sería Tom. Pero sin embargo me sale Bob.

    ResponderEliminar
  3. Solo vine a comentar que esto me acaba de salvar la vida, muchas gracias

    ResponderEliminar