Come trovare tutti i dipendenti sotto ciascun manager in SQL

Vi è mai capitato di dover costruire un organigramma, cioè di dover trovare i dipendenti sotto ogni manager di un'azienda? Con SQL è più facile di quanto si pensi! Questo articolo vi mostrerà come ottenere la risposta utilizzando una query ricorsiva.

Nel mondo degli affari, è un compito molto comune elencare tutti i dipendenti subordinati di un determinato manager. È abbastanza difficile da giustificare un intero articolo? Se lo si immagina come un organigramma, sembra piuttosto semplice: si trova il manager, si elencano tutti i suoi subordinati e il gioco è fatto. Qual è il problema?

Il problema è che SQL lavora con tabelle, non con organigrammi. Quando un organigramma viene tradotto in una tabella, può diventare un po' complicato ottenere ciò che si desidera.

La tecnica utilizzata in questo articolo si basa principalmente su join SQL e le query ricorsive. Se avete bisogno di fare un po' di pratica (o di spiegazioni più esaurienti), date un'occhiata ai nostri corsi SQL JOINs e Recursive Queries.

Esaminiamo innanzitutto come un organigramma viene solitamente visualizzato in una tabella adatta a una query SQL.

Tabella dipendenti

Utilizzeremo una tabella denominata employee con le seguenti colonne:

• employee_id: L'ID del dipendente.
• first_name: Il nome del dipendente.
• last_name: Il cognome del dipendente.
• manager_id: L'ID del manager del dipendente.

In questo esempio, ci sono dieci dipendenti. Esaminiamo i risultati:

Niente di complicato, solo un elenco di dipendenti. La caratteristica principale è la colonna manager_id, che contiene l'ID del capo di ogni dipendente. Ad esempio:

Nancy Young (employee_id 4529) ha un capo. L'ID del suo capo è 4125, indicato nella colonna manager_id. Se si cerca questo valore nella colonna employee_id, si scopre che è l'ID di Mafalda Ranieri.

Ora diamo un'occhiata alla signora Mafalda Ranieri:

Nella colonna manager_id c'è un valore NULL, il che significa che Mafalda Ranieri non ha un manager al di sopra di lei, cioè è il presidente dell'azienda.

La logica dice che ogni volta che c'è un valore NOT NULL nella colonna manager_id, quel dipendente dipende da un manager. Naturalmente, è possibile che un dipendente sia un manager e che abbia a sua volta dei superiori.

Contare tutti i dipendenti sotto ogni manager

Facciamo questo esercizio di "riscaldamento". Utilizzando solo la tabella employee come si potrebbero contare tutti i dipendenti sotto ogni manager? Ecco come si potrebbe fare, con il trucco magico di una self-join della tabella:

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;

Se non avete molta familiarità con le self-join, consultate questo articolo che spiega le self-join con esempi.

Nell'eseguire la self-join della tabella employee ho creato due alias, in modo che sia più facile seguire quello che sto facendo. Una tabella avrà l'alias sub (per i subordinati); l'alias dell'altra è sup (per i superiori). Poiché sto cercando i dati analitici dei manager, ho richiesto alla query di restituire le colonne employee_id, first_name e last_name dalla tabella sup.

Il codice conta quindi il numero di dipendenti utilizzando la funzione COUNT() sulla colonna sub.employee_id. Si otterrebbe lo stesso risultato se si utilizzasse invece sup.employee_id. Volevo solo usare la tabella sub per spiegare questo passaggio nel modo seguente: "Ah, la tabella sub è per i dipendenti subordinati, quindi, logicamente, conto il numero di subordinati in quella tabella".

Come ho spiegato in precedenza, eseguo la self-join sulla tabella employee utilizzando due alias. La condizione di join è sub.manager_id = sup.employee_id. Sembra logico, poiché il valore della colonna manager_id è l'ID del manager come dipendente e sarà naturalmente presente nella colonna employee_id.

Ho usato la funzione aggregata COUNT(), quindi devo raggruppare il risultato delle colonne employee_id, first_name e last_name.

Quando si esegue il codice, viene restituito questo risultato:

La tabella mostra quattro manager e il numero dei loro subordinati (number_of_employees).

Trovare tutti i subordinati diretti di ciascun manager

La ricerca dei subordinati diretti è simile al problema precedente. È logico pensare che se ho trovato il numero di dipendenti subordinati, allora posso trovare anche i loro nomi. A dire il vero, la soluzione per questo esercizio è solo una variazione del codice precedente:

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;

Il principio è lo stesso: la tabella employee è self-joined dove la colonna sub.manager_id = sup.employee_id. Per farlo, utilizzo nuovamente gli alias sub e sup.

Innanzitutto, prendo le colonne sub.employee_id, sub.first_name e sub.last_name. Le rinomino in modo che riflettano il fatto che i dati si riferiscono ai dipendenti subordinati. Faccio lo stesso con le colonne per la tabella sup, ma questa volta il nome riflette la posizione del dipendente come superiore.

Infine, il risultato viene ordinato in base alla colonna superior_id:

Sembra che la tabella mostri ciò che volevo: abbiamo i nomi dei dipendenti e i nomi dei loro manager. Ma il mio occhio di lince da ex-revisore nota che ci sono solo nove dipendenti, mentre io so che i dipendenti dell'azienda sono dieci. Perché? Il motivo è che Mafalda Ranieri è il presidente dell'azienda, non ha un superiore e non è nemmeno un subordinato. Questo è rappresentato come valore NULL nella colonna manager_id:

Mafalda manca nel risultato della query a causa della natura della self-join; ho usato JOIN (o meglio INNER JOIN). Questo tipo di join non restituisce righe con valori NULL. Se si desidera vedere anche queste righe nel risultato della query, occorre usare LEFT JOIN invece di JOIN; tutto il resto della query rimane invariato. Questa parte del codice sarà così:

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

L'esecuzione del codice modificato restituirà tutti i dipendenti, anche quelli che non hanno un superiore:

Tuttavia, questo risultato può essere insoddisfacente perché restituisce solo i dipendenti diretti. Nelle organizzazioni più complesse, alcuni manager hanno dei subordinati diretti che gestiscono altri dipendenti. Questi dipendenti hanno un capo diretto, ma rispondono anche al capo del loro capo. È possibile scegliere un manager e ottenere l'elenco di tutti i suoi subordinati diretti e indiretti in SQL? Certo che sì, con un piccolo aiuto da parte della nostra amica: la query ricorsiva.

Se non avete molta dimestichezza con il concetto di query ricorsiva, prima di continuare vi consiglio di leggere questo articolo che spiega il funzionamento delle query ricorsive.

Trovare i subordinati diretti e indiretti di ogni manager

Nella tabella employee abbiamo un'impiegata di nome Nancy Young. Ha un solo superiore, il presidente dell'azienda. Nancy è un dirigente di alto livello; naturalmente ha dei subordinati. Anche i suoi subordinati hanno a loro volta dei subordinati. Vorrei mostrarvi come ottenere tutti i subordinati, diretti e indiretti, di un manager; in questo caso quelli di Nancy Young. Lo farò utilizzando una CTE ricorsiva.

Il codice che mi permetterà di ottenere ciò che voglio è il seguente:

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;

Nella sintassi SQL standard, se l'intenzione è quella di avere una CTE ricorsiva, si inizia a scrivere il codice con WITH RECURSIVE. Nota bene! Questo non funziona se si usa SQL Server, è necessario scrivere la query di cui sopra senza RECURSIVE. In altre parole, la prima riga del codice dovrebbe risultare così:

WITH subordinate AS (
...

Ora torniamo a spiegare cosa fa il codice precedente. Crea una CTE ricorsiva denominata subordinata. La prima istruzione SELECT di questa CTE restituirà le colonne della tabella employee. Ho anche aggiunto una nuova colonna, level. Nancy Young sarà il manager di livello 0; in seguito vedrete lo scopo di questa colonna. Poiché l'ID del dipendente Nancy Young è 4529, ho aggiunto tale ID nella clausola WHERE.

Voglio che il risultato dell'istruzione SELECT sia "unito" al risultato della seconda istruzione SELECT. Per fare ciò, entrambe le istruzioni SELECT devono avere lo stesso numero di colonne nel risultato. Affinché UNION ALL abbia senso, metterò le colonne employee_id, first_name, last_name e manager_id nella seconda istruzione SELECT.

L'ultima colonna della istruzione sarà il valore di level dalla prima istruzione SELECT (che è 0). Ad ogni ricorrenza aggiungeremo un 1 a questo valore, che restituirà i livelli gerarchici. Questo sarà utile per ordinare i dati e capire facilmente chi è il manager di chi. Ho unito la tabella employee con la CTE stessa. Tratto la CTE come se fosse una tabella (e lo è), le do un alias e unisco le due tabelle su e.manager_id = s.employee_id.

Infine, si arriva all'istruzione SELECT al di fuori della CTE. In questa parte del codice, ho unito la CTE stessa con la tabella employee. L'istruzione per prima cosa seleziona le colonne employee_id, first_name e last_name dalla CTE, poiché la CTE è l'origine dei dati dei subordinati. Ho anche rinominato queste colonne di conseguenza, per evitare confusione.

Il passo successivo consiste nel selezionare le stesse colonne dalla tabella employee. Queste colonne conterranno i dati dei manager diretti dei dipendenti.

Infine, il risultato è ordinato dalla colonna level, che ordinerà i dipendenti in modo gerarchico. Una query così lunga non può che restituire un bel risultato; date un'occhiata voi stessi:

La tabella mostra tutti i subordinati diretti e indiretti di Nancy Young così come i suoi diretti superiori. Esaminando la tabella si nota che Nancy Young ha due subordinati diretti: Jakub Hrabal e Sandra Rajkovic. Jakub non ha subordinati, ma Sandra ne ha due, Moira Areas e Jon Nilssen.

Vedete? Non è poi così facile ottenere qualcosa che potrebbe inizialmnte sembrare semplice. Per fortuna, le query ricorsive sono molto utili in questi casi.