Como criar consultas recursivas no SQL e quando utilizá-las

Como criar consultas recursivas no SQL e quando utilizá-las?

Consultas recursivas são um recurso poderoso no SQL que permite que você consulte dados de maneira hierárquica ou inter-relacionada, como em grafos ou árvores. Elas são ideais para resolver problemas complexos, como percorrer hierarquias de categorias, obter registros com relações de pai e filho, ou até mesmo analisar estruturas de árvore. As consultas recursivas no SQL são geralmente feitas com CTEs recursivas (Common Table Expressions), uma ferramenta que permite executar uma consulta dentro de outra.

1. O que são consultas recursivas?

Uma consulta recursiva é uma consulta que se chama repetidamente até que uma condição de término seja atingida. Normalmente, as consultas recursivas são usadas para lidar com estruturas de dados hierárquicas ou em árvore, como árvores genealógicas, categorias de produtos, ou relacionamentos entre funcionários e gerentes.

A consulta recursiva no SQL é feita usando a palavra-chave WITH RECURSIVE. Ela divide a consulta em duas partes: uma consulta base (ou âncora) e uma consulta recursiva que se refere à primeira.

Exemplo básico de consulta recursiva

WITH RECURSIVE hierarquia AS (
    SELECT id, nome, id_pai
    FROM categorias
    WHERE id_pai IS NULL
    UNION ALL
    SELECT c.id, c.nome, c.id_pai
    FROM categorias c
    JOIN hierarquia h ON c.id_pai = h.id
)
SELECT * FROM hierarquia;

Neste exemplo, estamos utilizando uma consulta recursiva para listar todas as categorias de um sistema de e-commerce, onde cada categoria pode ter uma categoria pai. A parte da consulta com UNION ALL é chamada recursivamente para buscar todas as subcategorias.

2. Como funciona a consulta recursiva?

A consulta recursiva é dividida em duas partes:

Base (âncora): A primeira parte da CTE retorna os dados iniciais que não têm pais (ou seja, o ponto de partida da árvore, como o nível superior de uma hierarquia).
Recursão: A segunda parte da CTE se refere a si mesma e faz a junção de dados com base nas condições da consulta recursiva, até atingir um limite ou condição de término.

Exemplo detalhado com uma hierarquia de categorias

Suponha que temos uma tabela de categorias de produtos com os seguintes dados:

id	nome	id_pai
1	Eletrônicos	NULL
2	Celulares	1
3	Smartphones	2
4	Acessórios	1
5	Carregadores	4

A consulta recursiva mostrada acima retorna todos os registros, incluindo os filhos das categorias, criando uma hierarquia completa.

3. Quando usar consultas recursivas?

Consultas recursivas são particularmente úteis para dados que têm uma estrutura hierárquica ou que possuem relacionamentos entre registros com uma dependência direta. Alguns exemplos incluem:

Estruturas de árvore: como as hierarquias de categorias ou funções de uma empresa (onde um gerente tem subordinados e cada subordinado pode ser um gerente de outros).
Cálculos de grafos: quando você precisa resolver problemas de trilhas ou caminhos mais curtos em grafos ou redes.
Hierarquias familiares: como árvores genealógicas ou redes de parentesco.

Exemplo de hierarquia de funcionários

Em um sistema de gestão de recursos humanos, você pode ter uma tabela de funcionários com uma coluna id_pai que indica o gerente do funcionário. A consulta recursiva pode ser usada para listar todos os subordinados de um gerente:

WITH RECURSIVE hierarquia_funcionarios AS (
    SELECT id, nome, id_pai
    FROM funcionarios
    WHERE id_pai IS NULL
    UNION ALL
    SELECT f.id, f.nome, f.id_pai
    FROM funcionarios f
    JOIN hierarquia_funcionarios h ON f.id_pai = h.id
)
SELECT * FROM hierarquia_funcionarios;

Aqui, a consulta recursiva começa com o gerente e vai retornando os subordinados, criando uma lista de todos os funcionários no sistema.

4. Quando não usar consultas recursivas?

Embora as consultas recursivas sejam muito poderosas, elas não são ideais para todas as situações. Elas podem ser menos eficientes do que outras abordagens quando se trata de:

Grandes volumes de dados: Se a hierarquia for muito profunda, a consulta recursiva pode se tornar ineficiente e afetar o desempenho. A performance pode ser melhorada em alguns casos com a criação de índices.
Sistemas que não precisam de hierarquia: Se você não está lidando com dados hierárquicos ou não precisa fazer auto-junções, uma consulta simples pode ser mais rápida e eficaz.

5. Limitando a profundidade da recursão

Em alguns casos, você pode querer limitar a profundidade da recursão para evitar que a consulta percorra uma árvore muito grande. Você pode fazer isso usando a cláusula LIMIT ou adicionando uma condição de parada dentro da própria CTE.

Exemplo de limitar a profundidade

WITH RECURSIVE hierarquia AS (
    SELECT id, nome, id_pai, 1 AS profundidade
    FROM categorias
    WHERE id_pai IS NULL
    UNION ALL
    SELECT c.id, c.nome, c.id_pai, h.profundidade + 1
    FROM categorias c
    JOIN hierarquia h ON c.id_pai = h.id
    WHERE h.profundidade < 3
)
SELECT * FROM hierarquia;

Neste exemplo, a profundidade da recursão é limitada a 3, ou seja, apenas as três primeiras camadas da hierarquia serão retornadas.

6. Conclusão

Consultas recursivas no SQL são essenciais para trabalhar com dados hierárquicos ou grafos. Elas oferecem uma maneira poderosa de obter dados relacionados, como departamentos, categorias ou relações familiares, com uma única consulta. Ao usar consultas recursivas, você pode simplificar o código, melhorar a legibilidade e resolver problemas complexos de maneira mais eficiente. No entanto, é importante entender quando usá-las para evitar problemas de desempenho em grandes volumes de dados.

Como e quando usar consultas recursivas no SQL para otimizar a consulta de dados hierárquicos

Consultas recursivas no SQL são essenciais para lidar com dados hierárquicos e relações complexas. Elas permitem que você escreva consultas que percorrem árvores ou grafos de dados, tornando o código mais simples e eficiente. Porém, é importante saber quando usá-las para não comprometer a performance, especialmente em hierarquias profundas. Ao combinar CTEs recursivas com boas práticas de otimização, você pode resolver uma ampla gama de problemas de forma eficiente e clara.

Algumas aplicações:

Consultas que lidam com hierarquias, como árvores genealógicas ou relações entre categorias de produtos
Sistemas de gestão de recursos humanos, onde você precisa listar os subordinados de um gerente ou diretor
Sistemas de gestão de documentos, onde você precisa percorrer uma estrutura de pastas e subpastas
Plataformas de redes sociais, onde você precisa buscar relações de amizade ou seguidores em estruturas em árvore

Dicas para quem está começando

Use CTEs recursivas para facilitar consultas complexas que envolvem relacionamentos de pai e filho ou dados em árvores hierárquicas
Evite usar consultas recursivas em grandes volumes de dados sem otimização adequada
Lembre-se de que a profundidade da recursão pode ser limitada para evitar loops infinitos ou excessivos
Fique atento ao desempenho da consulta e utilize EXPLAIN para verificar o plano de execução das consultas recursivas

Contribuições de João Gutierrez

Como criar consultas recursivas no SQL e quando utilizá-las?