Como obter descendentes de um pai em SQL

Quer aprender como lidar com árvores genealógicas e encontrar descendentes de um dos pais? Ao ler este artigo, você aprenderá como lidar com dados hierárquicos.

Encontrar descendentes de um dos pais é um problema comum em SQL. Se você imaginar uma árvore genealógica, o bloco básico que forma as relações dentro dela é a relação pai-filho. A relação pai-filho é precisamente o que define todos os dados hierárquicos.

Outro exemplo de dados hierárquicos é o relacionamento gerente-empregado. É possível encontrar dados hierárquicos em inúmeras outras situações, tais como dados detalhados sobre projeto(s), seus subprojetos e cada tarefa que pode ser dividida em subtarefas. Outro exemplo de dados hierárquicos é quando se está construindo uma classificação de seres vivos onde se classificam as criaturas de acordo com o reino, filo, classes, ordem, famílias, gênero e espécies. Ou talvez você esteja olhando para um gênero musical e seus subgêneros. Por exemplo, a música blues pode ser separada em subgêneros como o Delta blues, country blues, jump blues e o Chicago blues. Ela influenciou um novo estilo, a música soul. A música soul tem seus subgêneros, tais como Southern soul, Motown soul, Philly soul, progressive soul, etc., que por sua vez influenciaram a criação de outros gêneros, tais como funk ou discoteca.

Este artigo cobre os tópicos que você pode aprender em maiores detalhes no curso Consultas Recursivas . Há um guia que descreve como este conhecimento pode ser benéfico para você; lê-lo pode ajudá-lo a decidir se deseja aprender consultas recursivas.

Desenho de uma Tabela Hierárquica

Você notará uma característica específica das tabelas que contêm dados com relações pai-filho: duas colunas contendo valores de identificação. Uma coluna é o ID usado para a identificação única dos dados dentro da tabela. A outra coluna contém o ID usado para fazer referência a outra linha.

Deixe-me mostrar-lhe o que quero dizer com isso. A tabela que você usará é chamada family_treecom as seguintes colunas:

• id: O ID da pessoa e a chave primária (PK) para a tabela.
• first_name: O primeiro nome da pessoa.
• last_name: O sobrenome da pessoa.
• parent_id: O ID do pai da pessoa.

Aqui estão alguns dados da tabela:

Você vê que o ID de John McArthur é 2. O ID de Sam McArthur é 5, enquanto seu parent_id = 2. Isto significa que seu pai tem o ID 2; neste caso, é John McArthur.

Esta estrutura de dados é característica dos dados hierárquicos, o que é bastante comum em bancos de dados. Para obter dados de uma tabela como esta, você terá que usar consultas hierárquicas ou recursivas. Se você quiser saber o que as consultas recursivas podem fazer, salte para o artigo revelando seu poder. Serei mais específico e lhe mostrarei como estruturar uma consulta recursiva para obter todos os descendentes de um pai a partir da tabela acima.

Consulta recursiva: Recuperando os descendentes

Primeiro, deixe-me mostrar-lhe como é a consulta. Em seguida, vou decompô-la para você, analisar todas as partes da consulta e explicar seu propósito.

Vamos lá! A pergunta que vai lhe dar a todos os descendentes de um dos pais é esta:

WITH RECURSIVE descendant AS (
	SELECT	id,
			first_name,
			last_name,
			parent_id,
			0 AS level
	FROM family_tree
	WHERE id = 1

	UNION ALL

	SELECT	ft.id,
			ft.first_name,
			ft.last_name,
			ft.parent_id,
			level + 1
	FROM family_tree ft
JOIN descendant d
ON ft.parent_id = d.id
)

SELECT	d.id AS descendant_id,
		d.first_name AS descendant_first_name,
		d.last_name AS descendant_last_name,
		a.id AS ancestor_id,
		a.first_name AS ancestor_first_name,
		a.last_name AS ancestor_last_name,
		d.level
FROM descendant d
JOIN family_tree a
ON d.parent_id = a.id
ORDER BY level, ancestor_id;

A consulta começa com a definição do CTE. As três letras significam Common Table Expression, e você reconhecerá um CTE quando vir a palavra WITH. Mais teoria por trás do CTE e sua sintaxe são explicadas em outro artigo.

Os CTE podem ser recursivos ou não recursivos. Quer saber como diferenciar entre esses dois? É mais simples do que você pensa; as que começam com WITH RECURSIVE são recursivas.

Quando se escreve um CTE, é preciso especificar seu nome. No meu caso, o CTE é nomeado descendente pela seguinte linha de código: WITH RECURSIVE descendente AS. O que se segue é uma declaração SELECT entre parênteses, que é armazenada como um CTE. Nesta declaração SELECT, eu seleciono todas as colunas da tabela family_tree. Acrescento também um novo nível de coluna nomeado que conterá o valor 0. Tenha paciência comigo; você verá o objetivo desta etapa em um instante. Finalmente, utilizo uma cláusula WHERE para filtrar os resultados. Estou interessado em encontrar todos os descendentes de um Peter McArthur, cujo id = 1.

Depois "fundirei" esta declaração SELECT com a próxima, usando UNION ALL. É um comando que ajuda a combinar os resultados de duas ou mais declarações SELECT. Para que o UNION ALL funcione, todas as declarações SELECT têm que ter o mesmo número de colunas. A seguinte declaração SELECT seleciona novamente todas as colunas da tabela family_tree. Além disso, ele pega o valor da coluna level (da declaração anterior SELECT, 0) e adiciona 1 a cada repetição. Os dados para esta declaração SELECT são obtidos juntando-se à tabela family_tree com o CTE, tratado aqui como qualquer outra mesa. Como os dados do CTE são os mesmos que os dados da tabela family_tree, exceto para a nova coluna level, este JOIN essencialmente junta-se à tabela family_tree com ele mesmo nas colunas parent_id e id.

Fechando os parênteses, você está pronto para definir o CTE. Agora vem a declaração SELECT, que usa o CTE e devolve todos os descendentes para cada um dos pais. Eu primeiro seleciono as colunas id, first_name e last_name do descendente do CTE nesta declaração SELECT. O CTE é tratado como uma tabela regular, com d como o pseudônimo. Eu o junto com a tabela family_tree com o pseudônimo a. Eu escolhi este pseudônimo, porque estou tratando o CTE como a tabela para os dados dos descendentes e family_tree como a tabela que contém os dados dos antepassados. É apenas um simples truque que me permite não fazer asneira ao escrever uma pergunta. As colunas selecionadas da tabela family_tree são novamente id, first_name, e last_name. A última coluna selecionada é level do CTE.

No final, os dados são ordenados pelas colunas level e ancestor_id. Aqui está o resultado:

Todos estes são descendentes de Peter McArthur cujo ID é 1. As pessoas no nível 1 são filhos de Peter McArthur. Seus netos estão no Nível 2, com seus pais indicados na tabela. O nível 3 mostra os bisnetos de Peter McArthur, com seus pais também mostrados por seus nomes.

Sabendo como escrever a consulta acima, você pode aplicá-la a qualquer outro cenário em que haja uma estrutura hierárquica de dados.

Dicas adicionais

Escrevendo CTEs Recursivos no Microsoft SQL Server

Se você tentar executar a consulta acima no SQL Server, ela mostrará um erro. Não há necessidade de pânico; o SQL Server suporta CTEs e consultas recursivas. A única diferença é que você não precisa escrever RECURSIVE no SQL Server para obter uma consulta recursiva. Basta omiti-la, assim:

WITH descendant AS...

Tudo o resto é o mesmo; não há necessidade de alterar qualquer outra parte da consulta. Se você estiver usando Oracle, aqui está o artigo que explica o uso de consultas hierárquicas.

Obtendo os valores NULL na tabela

Se você olhar mais de perto a tabela que mostra os descendentes de Peter McArthur, você notará que não há nenhum Peter McArthur em si. Isso não é um erro; é simplesmente porque ele não é seu próprio descendente. Entretanto, talvez você queira mostrá-lo também, para que você tenha a árvore genealógica completa desde o primeiro antepassado até o último descendente.

Para fazer isso, primeiro você precisará saber por que Peter não apareceu na tabela resultante. A razão é que eu usei JOIN para conectar as mesas. Como Peter McArthur é o ponto de partida da árvore genealógica, há um valor NULL na coluna parent_id. O JOIN não retorna os valores NULL.

Há apenas uma pequena coisa que você precisa mudar na consulta para obter os valores NULL. Em vez de usar JOIN, basta usar o LEFT JOIN. Aqui está a parte do código que muda; todo o resto permanece o mesmo:

...FROM descendant d LEFT JOIN family_tree a ON d.parent_id = a.id...

O resultado difere em uma linha, ou seja, a primeira linha:

Obter o Hang of Finding the Descendants of a Parent?

Neste artigo, você aprendeu sobre a estrutura hierárquica dos dados e sobre a relação pai-filho. Dei-lhe vários exemplos da vida real onde você pode encontrar tais estruturas de dados. Eles não são os únicos, é claro.

Você aprendeu a estrutura característica de tais dados, em preparação para escrever uma consulta que lhe permite obter todos os descendentes de um antepassado. A partir desse exemplo, você deve ser capaz de aplicar o conhecimento em cenários similares. Para ser ainda mais proficiente em escrever consultas recursivas e reconhecer quando utilizá-las, a prática com o curso Consultas Recursivas é altamente recomendada. O artigo que explica quando usar os CTEs também pode ajudá-lo a melhorar com os CTEs.