Como consultar uma árvore pai-filho no SQL

O que são estruturas de árvore pai-filho no SQL? Neste artigo, responderemos a essa pergunta, falaremos sobre hierarquia de consultas e demonstraremos as cinco consultas SQL mais comuns que você precisará para essas estruturas de dados.

Sim, você pode usar o SQL em uma estrutura de árvore pai-filho. Vou lhe mostrar como neste artigo. Ao longo do caminho, eu o orientarei em cinco exemplos de consultas, começando com a mais fácil e terminando com a mais complexa. Esses exemplos usarão expressões de tabela comuns (CTEs) recursivas.

Se você não estiver familiarizado com CTEs, recomendo nosso Consultas Recursivas curso interativo. Ele contém mais de 100 exercícios que ensinam como usar CTEs em SQL, começando com o básico e progredindo para tópicos avançados, como CTEs recursivos.

Antes de nos aprofundarmos no código, vamos dar uma olhada em uma visão geral da estrutura de árvore pai-filho e como ela é armazenada em um banco de dados relacional.

O que é uma árvore pai-filho?

Se você entende de dados hierárquicos, provavelmente sabe que é um sinônimo de estrutura pai-filho. Os dois nomes são muito lógicos; uma estrutura de árvore pai-filho é um conjunto de dados estruturados hierarquicamente. Em outras palavras, há relações hierárquicas entre os itens de dados. Isso significa que um item de dados pode ser o pai de outro item de dados, que passa a ser chamado de filho. Os itens também são chamados de níveis ou nós da árvore e podem assumir três formas principais:

• Nó raiz - O primeiro nó, onde a árvore pai-filho começa.
• Nó pai - É qualquer nó que tenha um ou mais nós descendentes (ou filhos).
• Nó filho - Qualquer nó que tenha um predecessor ou nó pai.

Exemplos reais de estruturas pai-filho incluem estruturas organizacionais de empresas (uma empresa é formada por departamentos, os departamentos são formados por equipes e as equipes são formadas por funcionários), árvores genealógicas (há pais, filhos, netos, bisnetos etc.) e taxonomias naturais (os seres vivos pertencem a um domínio, reino, filo, classe, ordem, família, gênero e espécie). Até mesmo pastas de computador (disco C, Arquivos de Programas, Microsoft SQL Server...), cardápios (bebidas, bebidas não alcoólicas, chá...), gêneros de arte e música (por exemplo, havia o blues, que desenvolveu o rhythm and blues, que levou ao soul, funk etc.) e projetos (um projeto tem subprojetos, que têm tarefas, subtarefas etc.) podem ser considerados estruturas de dados hierárquicas.

Estrutura de árvore pai-filho em bancos de dados relacionais

Para que o SQL possa fazer algo com ela, uma estrutura de árvore pai-filho precisa ser armazenada em um banco de dados relacional.

Essas estruturas geralmente são armazenadas em uma tabela com duas colunas de ID, das quais uma faz referência a um ID de objeto pai. Isso nos permite determinar a hierarquia entre os dados. Em outras palavras, sabemos qual nó é um nó pai para qual nó filho e vice-versa.

Isso pode parecer um pouco abstrato, portanto, mostrarei como funciona com um exemplo simples. E vou ser literal com ele! Minha estrutura de árvore pai-filho mostrará dados sobre os pais e seus filhos. Dê uma olhada:

Aqui, a coluna id mostra o ID do filho. Para descobrir quem é o pai ou a mãe dessa criança, você precisa olhar para a coluna parent_id, encontrar o mesmo número de ID na coluna id e procurar nessa linha o nome do pai ou da mãe.

Em outras palavras, Jim Cliffy não tem pais nessa tabela; o valor em sua coluna parent_id é NULL. Isso significa que ele é o nó raiz dessa estrutura em árvore.

Mark Cliffy é filho de Jim Cliffy. Como sei disso? Porque Mark tem parent_id = 1, que é o ID de Jim Cliffy. Mark Cliffy é um nó filho, mas também é um nó pai. Por quê? Porque Veronica Cliffy é filha de Mark Cliffy. Sei disso porque o pai dela tem parent_id = 2, e a tabela me diz que é Mark Cliffy. Veronica Cliffy é estritamente um nó filho; ela tem um nó pai, mas nenhum nó filho está se ramificando a partir dela.

Consultas típicas executadas em uma estrutura de árvore pai-filho

Usarei a mesma tabela para cada uma dessas consultas. Ela tem as mesmas colunas mostradas acima, mas com mais linhas e valores diferentes.

Introdução aos dados de exemplo

A tabela é denominada parent_child e tem as seguintes colunas:

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

Aqui está a tabela completa:

Você pode usar essa tabela para verificar se as consultas que estou prestes a mostrar retornam o resultado correto.

Exemplo 1: Listar todos os filhos de 1 pai

Essa é a consulta mais simples, portanto, vou usá-la para que você se sinta mais à vontade com a estrutura em árvore. Aqui, quero encontrar todos os filhos de um pai especificado. Nesse caso, estou interessado em encontrar todos os filhos de uma pessoa chamada Marge Wellington, cujo ID é 4.

Aqui está a pequena consulta:

SELECT first_name,
	 last_name
FROM parent_child
WHERE parent_id = 4;

Simplesmente selecionei o primeiro e o último nome da tabela e usei a cláusula WHERE para mostrar somente as linhas em que há um 4 na coluna parent_id.

O resultado mostra três linhas:

Isso me diz que Bobby, Sammy e Sarah Wellington são todos filhos de Marge Wellington. Dê uma olhada na tabela original e verá que isso é verdade.

Isso foi apenas um aquecimento! Vamos passar para a próxima.

Exemplo 2: Listar um nó pai para um nó filho

Agora, o resultado do exemplo anterior foi um pouco, bem, básico. Listei apenas os nomes dos filhos. Poderia ser muito útil mostrar também o nome do pai. E é exatamente isso que vou fazer. Mostrarei o nome e o sobrenome da criança e do pai.

Em vez de procurar os filhos de um pai, agora estarei procurando os pais do filho. Quero descobrir quem é o pai de Sam Francis Dijkstra. Além dos nomes, também quero ver os IDs.

A consulta para isso é:

SELECT child.id AS child_id,
	 child.first_name AS child_first_name,
	 child.last_name AS child_last_name,
	 parent.first_name AS parent_first_name,
	 parent.last_name AS parent_last_name,
	 parent.id AS parent_id
FROM parent_child child 
JOIN parent_child parent
  ON child.parent_id = parent.id
WHERE child.id = 11;

O principal conceito que estou introduzindo aqui é a autojunção. Dei o alias child para a tabela parent_child e a uni a ela mesma usando o parent alias. Ao fazer isso, estou agindo como se estivesse trabalhando com duas tabelas diferentes. Uma contém os dados sobre as crianças; é por isso que a chamei de child. A outra tem dados sobre os pais, por isso a chamei de parent.

As colunas selecionadas refletem isso. Os nomes e IDs das crianças são selecionados na tabela "first". Os nomes e IDs dos pais são selecionados na "segunda" tabela. As "tabelas" são unidas onde parent_id é igual a id.

A tabela original me diz que o ID de Sam Francis Dijkstra é 11. Usei a cláusula WHERE para filtrar os dados e mostrar somente o pai de Sam Francis. Você também pode usar a cláusula WHERE nas colunas child.first_name e child.last_name. Optei por filtrar os dados usando o ID porque a consulta é um pouco mais curta dessa forma.

Aqui está o resultado:

Ele mostra que a mãe de Sam Francis é Mary Dijkstra, o que é verdade.

Tudo ficou claro até agora? Tudo bem. Vamos em frente!

Exemplo 3: Obter um número de geração (ou nível de árvore) para cada nó

Neste exemplo, quero listar todas as pessoas da tabela e mostrar a qual geração elas pertencem. Qual é o objetivo disso? Quando obtiver esses dados, poderei ver facilmente quem pertence a qual geração: pais, filhos, netos etc.

Para isso, usarei um CTE - não o CTE comum, mas um CTE recursivo. Se você precisar atualizar seus conhecimentos sobre CTE, aqui está um artigo que explica o que é um CTE.

Esta é a minha consulta:

WITH RECURSIVE generation AS (
	SELECT id,
		first_name,
		last_name,
		parent_id,
		0 AS generation_number
	FROM parent_child
	WHERE parent_id IS NULL

UNION ALL 

	SELECT child.id,
		child.first_name,
		child.last_name,
		child.parent_id,
		generation_number+1 AS generation_number
	FROM parent_child child
	JOIN generation g
	  ON g.id = child.parent_id
)

SELECT first_name,
	 last_name,
	 generation_number
FROM generation;

Como todo CTE recursivo, o meu começa com duas palavras-chave: WITH RECURSIVE. Nomeei a geração do CTE. Na primeira instrução SELECT, estou selecionando IDs e nomes. Além disso, há uma nova coluna chamada generation_number com um 0 para todas as linhas em que parent_id = NULL. Por que NULL? Porque sei que a pessoa que é a predecessora de todas as outras pessoas não tem pai na tabela. Portanto, o valor deve ser NULL.

Estou usando UNION ALL para mesclar o resultado dessa instrução SELECT com a segunda, que será responsável pela recursão. Para que o UNION ALL funcione, o número de colunas e os tipos de dados devem ser os mesmos nos dois comandos SELECT.

O membro recursivo seleciona novamente IDs e nomes. Há também a coluna generation_number com o valor generation_number+1. A cada recursão, 1 será adicionado ao valor anterior dessa coluna. Como a consulta começa com 0, a primeira recursão resultará em um 1 na coluna generation_number, a segunda em um 2 e assim por diante.

Para que tudo isso funcione, juntei a tabela parent_child com o próprio CTE onde id = parent_id. O mesmo princípio se aplica às tabelas de autoagrupamento: a tabela serve como dados sobre os filhos, o CTE serve como dados sobre os pais.

Depois de escrever o CTE, preciso usar seus dados. Fiz isso escrevendo uma instrução SELECT simples que retorna nomes e números de geração do CTE. Muito bom, não é?

Aqui está o resultado:

Com esse resultado, vejo que Rosa Wellington é o nó raiz porque seu número de geração é 0. Todas as pessoas com valor 1 são seus filhos, valor 2 são netos e valor 3 são bisnetos. Se você verificar isso na tabela de origem, verá que tudo o que eu disse é verdade.

Exemplo 4: Listar todos os descendentes

Este exemplo é uma extensão do anterior. Quero mostrar a você como listar todos os descendentes de um pai e mostrar os nomes dos pais e dos filhos.

Esta é a consulta:

WITH RECURSIVE generation AS (
	SELECT id,
		 first_name,
		 last_name,
		 parent_id,
		 0 AS generation_number
	FROM parent_child
	WHERE parent_id IS NULL

UNION ALL 

	SELECT child.id,
		 child.first_name,
		 child.last_name,
		 child.parent_id,
		 generation_number+1 AS generation_number
	FROM parent_child child
	JOIN generation g
	  ON g.id = child.parent_id

)

SELECT	g.first_name AS child_first_name,
		g.last_name AS child_last_name,
		g.generation_number,
		parent.first_name AS parent_first_name,
		parent.last_name AS parent_last_name
FROM generation g
JOIN parent_child parent
ON g.parent_id = parent.id
ORDER BY generation_number; 

Se você comparar essa consulta com a anterior, verá que a parte do CTE é idêntica. Não há necessidade de eu passar por ela novamente.

O que é diferente é a instrução SELECT que faz referência ao CTE. Mas também não há novos conceitos de SQL aqui. A consulta seleciona os nomes do filho e do pai e seu número de geração. Fiz isso unindo novamente o CTE à tabela parent_child. O CTE contém os dados dos filhos, enquanto a tabela contém os dados dos pais. A última linha de código ordena o resultado pelo número da geração.

A consulta retorna exatamente o que eu queria:

Ou não? Claro, ela mostra todos os filhos e o nome de seus pais. Mas Rosa Wellington, o nó raiz e a matriarca dessa família, está faltando. E eu não apliquei nenhum filtro para excluí-la.

O que aconteceu? Na verdade, apliquei um filtro usando JOIN na última instrução SELECT. Lembre-se de que JOIN retorna somente as linhas correspondentes das tabelas unidas. Rosa Wellington está ausente porque não tem dados sobre seu pai; no caso dela, não há dados em que id possa corresponder a parent_id. Se você quiser incluí-la também, use o LEFT JOIN no último SELECT:

…
FROM generation g LEFT JOIN parent_child parent ON g.parent_id = parent.id
…

E o resultado completo está aqui:

Se você quiser saber mais sobre essa consulta complexa, aqui está um artigo dedicado a esse exemplo.

Exemplo 5: Gerar uma visualização em árvore de dados hierárquicos

O exemplo final é o mais complexo, mas também é o mais divertido. Ou, pelo menos, seu resultado é. Seria uma pena consultar estruturas de árvore sem poder mostrar os dados em algum tipo de formato de árvore.

A tarefa aqui é mostrar todas as pessoas da tabela. Além disso, cada descendente deve ser mostrado de forma que seja graficamente óbvio de quem é filho e a qual geração pertence. Essa é uma visualização em árvore. Acho melhor você esperar até que eu chegue à saída da consulta para ver o que quero dizer com isso.

Vamos ao trabalho! Novamente, o CTE recursivo salva o dia:

WITH RECURSIVE tree_view AS (
	SELECT id,
		 parent_id,
		 first_name,
		 last_name,
		 0 AS level,
		 CAST(id AS varchar(50)) AS order_sequence
	FROM parent_child
	WHERE parent_id IS NULL
	
UNION ALL

	SELECT parent.id,
		 parent.parent_id,
		 parent.first_name,
		 parent.last_name,
		 level + 1 AS level,
		 CAST(order_sequence || '_' || CAST(parent.id AS VARCHAR (50)) AS VARCHAR(50)) AS order_sequence
	FROM parent_child parent
	JOIN tree_view tv
	  ON parent.parent_id = tv.id
)

SELECT 
   RIGHT('------------',level*3) || first_name || ' ' || last_name 
     AS parent_child_tree
FROM tree_view
ORDER BY order_sequence;

Você já sabe como funcionam as consultas recursivas. Desta vez, o CTE tem o nome de tree_view. A primeira instrução SELECT seleciona alguns dados da tabela em que parent_id é NULL. Há a coluna level com o valor 0. E usei a função CAST() para alterar o tipo de dados id para VARCHAR; você verá por que preciso disso.

Usamos novamente o UNION ALL para mesclar os resultados de duas consultas. A segunda instrução SELECT novamente seleciona alguns dados, com a tabela parent_child unida ao próprio CTE. O importante é que, a cada recursão, 1 será adicionado ao nível anterior. Além disso, o sublinhado e o valor da coluna id serão adicionados a cada recursão. Preciso desse pequeno truque porque usarei essa coluna mais tarde para classificar a saída. Dessa forma, mostrarei a visualização em árvore corretamente. Para ter certeza de que você entendeu, aqui está uma linha da tabela:

O valor da coluna para Frank Wellington será 1_2_6. Por quê? Porque Rosa, como primeiro nível, recebe o valor 1. Jon Wellington é seu filho; seu ID vai para order_sequence, que agora se torna 1_2. Em seguida, o ID de Frank é adicionado e se torna 1_2_6. Ao fazer isso em toda a estrutura hierárquica, obtenho a coluna que posso usar para mostrar o resultado da maneira desejada.

De volta à consulta. Para obter o resultado, você precisa de um SELECT que use os dados do CTE. Estou usando a função RIGHT() aqui. Ela extrai um número específico de caracteres da direita. No meu caso, ela remove o número de traços do nível*3 para cada nível. Também concatenei esses traços com o primeiro e o último nome. O resultado é classificado pelo endereço order_sequence.

Você está pronto para ver a exibição em árvore? Aqui está ela:

Essa representação gráfica simples mostra, obviamente, os níveis geracionais e quem é quem nessa árvore genealógica. Pelo número de traços, você pode ver facilmente que Jon, Joni e Marge Wellington são filhos de Rosa. Mary Dijkstra, Frank, Jason, Sarah, Bobby e Sammy Wellington são os netos de Rosa. Também é fácil ver quem são seus pais. Você também pode ver quem são os bisnetos, mas vou deixar isso para você.

Antes de terminar, também gostaria de recomendar este artigo sobre como as estruturas de árvore de consulta funcionam no Oracle.

Consultar árvores pai-filho só se torna mais interessante

As estruturas de árvore pai-filho são bastante interessantes. Elas são um conjunto de dados completamente diferente do que você costuma consultar em bancos de dados relacionais. Mostrei a você o que são essas estruturas hierárquicas e como elas são representadas em uma tabela.

Mais importante ainda, mostrei cinco consultas que podem ser usadas para resolver alguns dos problemas mais comuns relacionados a dados hierárquicos. Como você viu, os CTEs e os CTEs recursivos são vitais para a consulta de árvores pai-filho.

Tenho certeza de que você já se deparou com dados hierárquicos em seu trabalho. Você provavelmente percebeu que precisa se equipar com conhecimento detalhado de consultas recursivas para lidar com esses dados. Temos o cursoConsultas Recursivas que o guiará sistematicamente pelos CTEs em geral, pelas consultas recursivas e por como a consulta de dados hierárquicos e gráficos funciona no SQL.

Boa sorte com o aprendizado! E fique à vontade para usar todas as consultas que mostrei e adaptá-las às suas necessidades comerciais.