Como Encontrar Todos os Funcionários de Cada Gerente em SQL

Você já ficou encarregado de construir um organograma - ou seja, encontrar os funcionários subordinados a cada gerente de uma empresa? Com o SQL, fazer isso é mais fácil do que você imagina! Neste artigo, mostraremos como realizar a tarefa usando uma consulta recursiva.

No mundo dos negócios, é muito comum precisar listar todos os funcionários que trabalham para um determinado gerente. Será que isso é tão difícil a ponto de precisar de um artigo inteiro? Se você pensar em um organograma, parece bem simples: encontrar o gerente, listar todos os seus subordinados e pronto. Qual é a dificuldade?

A questão é que o SQL funciona com tabelas, não com organogramas. Quando um organograma se traduz em uma tabela, pode ser um pouco mais complicado de executar o que é pedido.

A técnica que usarei neste artigo baseia-se principalmente em SQL JOINs e consultas recursivas. Se você precisar praticar (ou se quiser ver explicações mais detalhadas), confira nossos cursos sobre Cláusulas JOIN em SQL e Consultas Recursivas.

Vamos começar analisando como um organograma é normalmente exibido em uma tabela adequada para consultas em SQL.

Tabela de Funcionários

Usaremos uma tabela chamada funcionario, que tem as seguintes colunas:

• funcionario_id: Número de identificação do funcionário.
• nome: Nome do funcionário.
• sobrenome: Sobrenome do funcionário.
• gerente_id: Número de identificação do gerente.

Neste exemplo, há dez funcionários. Vamos dar uma olhada nos registros:

Nada de outro mundo até aqui, certo? Apenas uma lista de funcionários. A parte mais importante aqui é a coluna gerente_id, que contém a identificação do chefe de cada funcionário. Por exemplo:

Natália Jardim (funcionario_id 4529) tem um chefe. A identificação do gerente dela é 4125, exibida na coluna gerente_id. Se você procurar por esse valor na coluna funcionario_id, verá que este é o ID de Melissa Ramos.

Agora, vamos dar uma olhada na Sra. Melissa Ramos:

Há um valor NULL na coluna gerente_id, o que significa que Melissa Ramos não tem um gerente acima dela - ou seja, ela é a presidente da empresa.

A lógica diz que sempre que há um valor NOT NULL na coluna gerente_id, o funcionário responde a um gerente. É claro que é possível que um funcionário possa ser um gerente e também ter alguém acima dele na hierarquia.

Contar Todos os Funcionários de Cada Gerente

Vamos fazer um exercício de 'aquecimento'. Usando apenas a tabela funcionario, como você contaria todos os funcionários de cada gerente? Aqui vai um exemplo de como você poderia fazer isso, usando o truque de ligar uma tabela a ela mesma (SELF JOIN, ou auto-junção):

SELECT	
sup.funcionario_id,
	sup.nome,
	sup.sobrenome,
	COUNT (sub.funcionario_id) AS numero_de_funcionarios
FROM funcionario sub 
JOIN funcionario sup 
ON sub.gerente_id = sup.funcionario_id
GROUP BY sup.funcionario_id, sup.nome, sup.sobrenome;

Se você não está muito familiarizado com SELF JOINs, veja este artigo com explicações e exemplos sobre auto-junção (Self Join).

Ao aplicar o SELF JOIN na tabela funcionario, utilizei pseudônimos para ficar mais fácil de acompanhar o processo. Uma tabela se chamará sub (funcionários subordinados) e, a outra, sup (funcionários superiores). Já que estou procurando os dados analíticos dos gerentes, escrevi um comando para que a consulta retorne as colunas funcionario_id, nome e sobrenome da tabela sup.

O código então conta o número de funcionários usando a função COUNT() na coluna sub.funcionario_id. Você obteria o mesmo resultado se usasse sup.funcionario_id. Decidi usar a tabela sub para poder explicar esta etapa da seguinte maneira: "Ah! Como a tabela sub é para funcionários subordinados, então, logicamente, é nela que eu conto o número de subordinados".

Como eu disse anteriormente, ao ligar a tabela funcionario a ela mesma utilizando o SELF JOIN, utilizei dois pseudônimos. A condição de ligação é sub.gerente_id = sup.funcionario_id. Parece lógico, pois o valor na coluna gerente_id é o número de identificação do gerente como funcionário, que naturalmente estará na coluna funcionario_id.

Como utilizei a função de agregação COUNT(), tenho que agrupar o resultado pelas colunas funcionario_id, nome e sobrenome.

Ao rodar o código, ele retornará o seguinte resultado:

A tabela mostra quatro gerentes e o número de subordinados de cada um (numero_de_funcionarios).

Encontre Todos os Subordinados Diretos de Cada Gerente

Para encontrar subordinados diretos, o processo é parecido com o que descrevemos acima. Se eu encontrei o número de subordinados, então eu também consigo encontrar os nomes desses subordinados. Na verdade, a solução para este exercício é apenas uma variação do código anterior:

SELECT	
sub.funcionario_id AS subordinado_id,
	sub.nome AS nome_subordinado,
	sub.sobrenome AS sobrenome_subordinado,
	sup.funcionario_id AS superior_id,
	sup.nome AS nome_superior,
	sup.sobrenome AS sobrenome_superior
FROM funcionario sub 
JOIN funcionario sup 
ON sub.gerente_id = sup.funcionario_id
ORDER BY superior_id;

O princípio aqui é o mesmo; a tabela funcionario é ligada a ela mesma através da coluna sub.gerente_id = sup.funcionario_id. Para fazer isso, eu novamente uso os pseudônimos sub e sup.

Primeiro, pego as colunas sub.funcionario_id, sub.nome e sub.sobrenome, e renomeio-as, já que os dados se referem aos funcionários subordinados. Em seguida, eu repito o processo com essas colunas na tabela sup, mas desta vez o nome refletirá o cargo superior do funcionário.

Finalmente, o resultado é ordenado pela coluna superior_id:

Ao que parece, a tabela mostra o que queríamos. Temos os nomes dos funcionários e os nomes de seus gerentes. No entanto, percebam que há apenas nove funcionários, quando sabemos que há dez funcionários na empresa. Por que isso acontece? A razão é Melissa Ramos: ela é a presidente da empresa e, por isso, não tem um superior e não é subordinada a ninguém. Isto é representado como um valor NULL na coluna gerente_id:

Ela não aparece no resultado da consulta por causa da natureza da auto-junção (SELF JOIN); eu usei JOIN (ou seja, INNER JOIN). Este tipo de junção não retorna linhas com valores NULL. Se você também quiser que essas linhas apareçam no resultado da sua consulta, terá que usar LEFT JOIN ao invés de JOIN; todo o resto da consulta permanece igual. Esta parte do código será parecida com esta:

...
FROM funcionario sub LEFT JOIN funcionario sup ON sub.gerente_id = sup.funcionario_id
...

A execução do novo código retornará todos os funcionários, mesmo aqueles que não têm um superior:

No entanto, talvez este resultado ainda não seja satisfatório, pois retorna apenas os subordinados diretos. Em empresas mais complexas, alguns gerentes têm subordinados diretos que gerenciam outros funcionários. Esses funcionários têm um chefe direto, mas também respondem ao chefe de seu chefe. Será que é possível escolher um gerente e obter a lista de todos os seus subordinados diretos e indiretos em SQL? Com certeza, sim! Para isso, recorremos à nossa velha amiga, a consulta recursiva.

Se você não estiver muito familiarizado com o conceito de consultas recursivas, recomendamos que leia este artigo explicando como funcionam as consultas recursivas antes de continuar.

Encontre os Subordinados Diretos e Indiretos de Cada Gerente

Na tabela funcionario, temos uma funcionária chamada Natália Jardim. Ela tem apenas um superior, o presidente da empresa. Natália é uma executiva em um cargo alto; naturalmente, ela tem subordinados. Seus subordinados também têm seus próprios subordinados. O que eu quero mostrar é como você consegue obter todos os subordinados, diretos e indiretos, de um gerente - nesse caso, Natália Jardim. Vou fazer isso usando um CTE recursivo.

O código que vai me dar o que eu quero é o seguinte:

WITH RECURSIVE subordinado AS (
	SELECT	funcionario_id,
			nome,
			sobrenome,
			gerente_id,
			0 AS nivel
	FROM funcionario
	WHERE funcionario_id = 4529

	UNION ALL

	SELECT	e.funcionario_id, 
			e.nome,
			e.sobrenome,
			e.gerente_id,
			nivel + 1
	FROM funcionario e 
JOIN subordinado s 
ON e.gerente_id = s.funcionario_id
)

SELECT	
s.funcionario_id,
	s.nome AS nome_subordinado,
	s.sobrenome AS sobrenome_subordinado,
	m.funcionario_id AS superior_direto_id,
	m.nome AS nome_superior_direto,
	m.sobrenome AS sobrenome_superior_direto,
	s.nivel
FROM subordinado s 
JOIN funcionario m 
ON s.gerente_id = m.funcionario_id
ORDER BY nivel;

Na sintaxe SQL padrão, se sua intenção é ter um CTE recursivo, você começa o código escrevendo WITH RECURSIVE. Aviso importante! Se você estiver usando o SQL Server, isto não vai funcionar. Você precisará escrever a consulta acima sem RECURSIVE. Em outras palavras, a primeira linha do código deverá parecer com esta:

WITH subordinado AS (
...

Agora, vamos voltar para a explicação do que o código acima faz. Ele cria um CTE recursivo chamado subordinado. O primeiro comando SELECT neste CTE retornará as colunas da tabela funcionario. Acrescentei também uma nova coluna, nivel. Natália Jardim será a gerente de nível 0; você entenderá o propósito desta coluna daqui a pouco. Como o ID de funcionário de Natália Jardim é 4529, acrescentei esse ID na cláusula WHERE.

Quero que este resultado do comando SELECT seja "fundido" com o resultado do segundo comando SELECT. Para fazer isso, os dois comandos SELECT têm que ter o mesmo número de colunas no resultado. Para que a UNION ALL faça sentido, colocarei as colunas funcionario_id, nome, sobrenome e gerente_id no segundo SELECT.

A última coluna do comando será o valor de nivel do primeiro comando SELECT (que é igual 0). A cada repetição, adicionaremos 1 a este valor, o que retornará os níveis de hierarquia. Isto nos ajudará a ordenar os dados e a saber facilmente quem é gerente de quem. Juntei a tabela funcionario com o próprio CTE. Estou tratando o CTE como uma tabela (e, de fato, é uma tabela), dando-lhe um pseudônimo e juntando as duas tabelas em e.gerente_id = s.funcionario_id.

Finalmente, chego ao comando SELECT fora do CTE. Nesta parte do código, eu juntei o próprio CTE com a tabela funcionario. Primeiro, selecionamos as colunas funcionario_id, nome e sobrenome do CTE, pois estou usando o CTE como fonte dos dados subordinados. Também renomeei essas colunas assim, para evitar confusão.

O próximo passo é selecionar as mesmas colunas da tabela funcionario. Estas colunas conterão os dados dos gerentes diretos dos funcionários.

Finalmente, o resultado é ordenado pela coluna nivel, que ordenará os funcionários hierarquicamente. Uma consulta tão longa assim com certeza trará um resultado bem legal; confira:

A tabela nos mostra todos os subordinados diretos e indiretos de Natália Jardim junto com seus superiores diretos. Examine a tabela e perceba que Natália Jardim tem dois subordinados diretos: Joaquim Holanda e Sandra Ribeiro. Joaquim não tem subordinados, mas Sandra tem dois: Maria Assis e Jonas Nogueira.

Viu? Uma tarefa aparentemente simples pode não ser tão simples assim. Felizmente, as consultas recursivas são muito úteis nesses momentos.