Cómo encontrar todos los empleados a cargo de cada gerente con SQL

¿Alguna vez te encargaron la elaboración de un organigrama, es decir, la tarea de identificar qué empleados están bajo qué gerente dentro de una empresa? En SQL, puedes hacerlo más fácilmente de lo que imaginas. Este artículo te enseñará a hacerlo utilizando una consulta recursiva.

En el mundo de los negocios, es una tarea muy común listar los empleados que están subordinados a un gerente determinado. ¿Acaso esto resulta tan difícil como para justificar todo un artículo? Si te lo imaginas como un organigrama, parece bastante sencillo: buscas al gerente, enumeras a todos sus subordinados y ya está. ¿Cuál es el problema?

El problema es que SQL trabaja con tablas, no con organigramas. Cuando un organigrama se traduce en una tabla, puede resultar un poco complicado conseguir lo que se quiere.

La técnica que utilizaré en este artículo se basa principalmente en las uniones SQL y las consultas recursivas. Si necesitas algo de práctica (o explicaciones más completas), echa un vistazo a nuestros cursos sobre Tipos de JOIN en SQL y Consultas recursivas y expresiones de tabla comunes.

Primero, examinemos cómo se suele mostrar un organigrama en una tabla adecuada para la consulta SQL.

Tabla de empleados

Utilizaremos una tabla llamada employee (empleado) que tiene las siguientes columnas:

• employee_id: El ID del empleado.
• first_name: El nombre del empleado.
• last_name: El apellido del empleado.
• manager_id: El ID del gerente del empleado.

En este ejemplo, hay diez empleados. Vamos a examinar los registros:

Nada complicado hasta aquí, es sólo una lista de empleados. La característica clave es la columna manager_id, que contiene el ID del jefe de cada empleado. Por ejemplo:

Nancy Young (employee_id 4529) tiene un jefe. El ID de su jefe es 4125, que se muestra en la columna manager_id. Si buscas ese valor en la columna employee_id, descubrirás que es el ID de Mafalda Ranieri.

Ahora, echemos un vistazo a la Sra. Mafalda Ranieri:

Hay un valor NULL en la columna manager_id, lo que significa que Mafalda Ranieri no tiene ningún gerente por encima de ella, es decir, que es la presidenta de la empresa.

La lógica dice que siempre que haya un valor NOT NULL en la columna manager_id, ese empleado depende de un gerente. Por supuesto, es posible que un empleado sea gerente y también tenga a alguien por encima de él en la jerarquía.

Contar todos los empleados que hay a cargo de cada gerente

Hagamos este ejercicio de "calentamiento". Utilizando sólo la tabla employee¿cómo contarías todos los empleados que hay a cargo de cada gerente? Así es como podrías hacerlo, siendo la auto-unión de la tabla (self-join) el truco mágico:

SELECT	
sup.employee_id,
	sup.first_name,
	sup.last_name,
	COUNT (sub.employee_id) AS number_of_employees
FROM employee sub 
JOIN employee sup 
ON sub.manager_id = sup.employee_id
GROUP BY sup.employee_id, sup.first_name, sup.last_name;

Si no tienes mucha familiaridad con las autouniones (self-join), consulta este artículo que explica las autouniones con ejemplos.

Al auto-unir la tabla employeehe creado dos alias para que sea más fácil seguir lo que estoy haciendo. Una tabla tendrá el alias sub (para el empleado subordinado), y el alias de la otra será sup (para el empleado superior). Como estoy buscando los datos analíticos de los gerentes, le he dicho a la consulta que devuelva las columnas employee_id, first_name, y last_name de la tabla sup.

A continuación, el código cuenta el número de empleados utilizando la función COUNT() en la columna sub.employee_id. Obtendría el mismo resultado si utilizara sup.employee_id en su lugar. Sólo quería utilizar la tabla sub para explicar este paso de la siguiente manera: "Ajá, la tabla sub es para los empleados subordinados, así que, lógicamente, cuento el número de subordinados en esa tabla".

Como ya he dicho anteriormente, auto-uno la tabla employee utilizando dos alias. La condición de unión es sub.manager_id = sup.employee_id. Parece lógico, ya que el valor de la columna manager_id es el ID del gerente como empleado y naturalmente estará en la columna employee_id.

He utilizado la función agregada COUNT(), por lo que tengo que agrupar el resultado por las columnas employee_id, first_name, y last_name.

Cuando ejecute el código, devolverá este resultado:

La tabla muestra cuatro gerentes y el número de sus subordinados (number_of_employees).

Encontrar todos los subordinados directos a cargo de cada gerente

Encontrar a los subordinados directos es similar al problema anterior. Es lógico pensar que si he encontrado el número de empleados subordinados, entonces también podría encontrar sus nombres. A decir verdad, la solución para este ejercicio es sólo una variación del código anterior:

SELECT	
sub.employee_id AS subordinate_id,
	sub.first_name AS subordinate_first_name,
	sub.last_name AS subordinate_last_name,
	sup.employee_id AS superior_id,
	sup.first_name AS superior_first_name,
	sup.last_name AS superior_last_name
FROM employee sub 
JOIN employee sup 
ON sub.manager_id = sup.employee_id
ORDER BY superior_id;

Aquí, el principio: la tabla employee está auto-unida, donde la columna sub.manager_id = sup.employee_id. Para ello, vuelvo a utilizar los alias sub y sup.

En primer lugar, tomo las columnas sub.employee_id, sub.first_name, y sub.last_name. Les cambio el nombre para que reflejen que los datos se refieren a los empleados subordinados. Hago lo mismo con estas columnas para la tabla sup, pero esta vez el nombre refleja la posición del empleado como superior.

Finalmente, el resultado se ordena por la columna superior_id:

Parece que la tabla muestra lo que yo pretendía: tenemos los nombres de los empleados y los de sus superiores. Pero mi ojo de águila se da cuenta de que sólo hay nueve empleados, sin embargo sé que hay diez empleados en la empresa. ¿Qué está sucediendo? La razón es Mafalda Ranieri: ella es la presidenta de la empresa, por lo que no tiene un superior y tampoco es subordinada de nadie. Esto se representa como un valor NULL en la columna manager_id:

Ella no aparece en el resultado de la consulta debido a la naturaleza de la autounión; he utilizado JOIN (es decir, INNER JOIN). Este tipo de unión no devuelve filas con valores NULL. Si deseas ver esas filas también en el resultado de la consulta, tendrás que utilizar LEFT JOIN en lugar de JOIN; todo lo demás en la consulta permanece igual. Esta parte del código tendrá el siguiente aspecto:

...
FROM employee sub LEFT JOIN employee sup ON sub.manager_id = sup.employee_id
...

Al ejecutar el código modificado se obtendrán todos los empleados, incluso quienes no tengan un superior:

Sin embargo, este resultado puede seguir siendo insatisfactorio porque sólo devolverá a los subordinados directos. En organizaciones más complejas, algunos jefes tienen subordinados directos que dirigen a otros empleados. Esos empleados tienen un jefe directo, pero también responden al jefe de su jefe. ¿Es posible elegir un jefe y obtener la lista de todos sus subordinados directos e indirectos con SQL? ¡Claro que sí!, con un poco de ayuda de nuestra amiga: la consulta recursiva.

Si no tienes familiaridad con el concepto de consultas recursivas, sería recomendable que leyeras este artículo que explica cómo funcionan las consultas recursivas antes de continuar.

Encontrar subordinados directos e indirectos a cargo de cada gerente

En la tabla employeetenemos una empleada llamada Nancy Young. Ella sólo tiene un superior, la presidenta de la empresa. Nancy es una alta ejecutiva y,naturalmente, tiene subordinados a cargo. Sus subordinados también tienen sus propios subordinados. Lo que me gustaría mostrarte es cómo obtener todos los subordinados, tanto directos como indirectos, de un directivo, en este caso, Nancy Young. Lo haré utilizando una CTE recursiva.

El código que me dará lo que quiero es el siguiente:

WITH RECURSIVE subordinate AS (
	SELECT	employee_id,
			first_name,
			last_name,
			manager_id,
			0 AS level
	FROM employee
	WHERE employee_id = 4529

	UNION ALL

	SELECT	e.employee_id, 
			e.first_name,
			e.last_name,
			e.manager_id,
			level + 1
	FROM employee e 
JOIN subordinate s 
ON e.manager_id = s.employee_id
)

SELECT	
s.employee_id,
	s.first_name AS subordinate_first_name,
	s.last_name AS subordinate_last_name,
	m.employee_id AS direct_superior_id,
	m.first_name AS direct_superior_first_name,
	m.last_name AS direct_superior_last_name,
	s.level
FROM subordinate s 
JOIN employee m 
ON s.manager_id = m.employee_id
ORDER BY level;

En la sintaxis estándar de SQL, si tu intención es tener una CTE recursiva, empiezas escribiendo el código WITH RECURSIVE. ¡Aviso importante! Si estás usando SQL Server, esto no funcionará; tendrás que escribir la consulta anterior sin la palabra RECURSIVE. En síntesis, la primera línea del código debería quedar así:

WITH subordinate AS (
...

Ahora, volvamos a explicar lo que hace el código anterior. Crea una CTE recursiva llamada subordinate (subordinado). La primera sentencia SELECT de esta CTE devolverá las columnas de la tabla employee. He añadido también una nueva columna, level (nivel). Nancy Young será la gerente de nivel 0. Más adelante, verás el propósito de esta columna. Como el ID de empleado de Nancy Young es 4529, he añadido ese ID en la cláusula WHERE.

Quiero que el resultado de esta sentencia SELECT se "fusione" con el resultado de la segunda sentencia SELECT. Para ello, ambas sentencias SELECT tienen que tener el mismo número de columnas en el resultado. Para que UNION ALL tenga sentido, pondré las columnas employee_id, first_name, last_name, y manager_id en la segunda SELECT.

La última columna de la declaración será el valor de level de la primera declaración SELECT (que es 0). Añadiremos un 1 a este valor con cada recursión, lo que devolverá los niveles de jerarquía. Esto será útil para ordenar los datos y seguir fácilmente quién es el gerente de quién. He unido la tabla employee con la propia CTE. Estoy tratando la CTE como una tabla (que, de hecho, lo es), dándole un alias y uniendo ambas tablas en e.manager_id = s.employee_id.

Finalmente, llego a la sentencia SELECT fuera de la CTE. En esta parte del código, he unido la propia CTE con la tabla employee. Primero, selecciona las columnas employee_id, first_name, y last_name de la CTE, ya que estoy utilizando la CTE como fuente de datos de los subordinados. También he cambiado el nombre de esas columnas para evitar confusiones.

El siguiente paso es seleccionar las mismas columnas de la tabla employee. Estas columnas contendrán los datos de los jefes directos de los empleados.

Al fin, el resultado se ordena por la columna level, que organiza a los empleados jerárquicamente. Una consulta tan larga sólo podría devolver un bonito resultado; compruébalo tú mismo:

La tabla nos muestra todos los subordinados directos e indirectos de Nancy Young junto con sus superiores directos. Examina la tabla y verás que Nancy Young tiene dos subordinados directos: Jakub Hrabal y Sandra Rajkovic. Jakub no tiene subordinados, pero Sandra tiene dos, Moira Areas y Jon Nilssen.

¿Ves? No es tan fácil conseguir algo que se podría pensar que es sencillo. Por suerte, las consultas recursivas resultan muy útiles en momentos como éste.