Um Guia Ilustrado para a Auto-Adesão SQL

O que é uma auto-adesão SQL e como ela funciona? Quando ele deve ser usado? Daremos respostas a essas perguntas!

Em SQL, podemos combinar dados de várias tabelas usando um operador JOIN. JOIN tem várias variantes; já discutimos CROSS JOIN, INNER JOIN, e OUTER JOIN. Na maioria das vezes, estes operadores juntam dados de duas ou mais tabelas diferentes. Você pode praticar todos os diferentes tipos de JOINs em nosso Cláusulas JOIN em SQL curso. Neste artigo, no entanto, explicaremos como unir registros da mesma tabela.

Um SQL self join junta dados da mesma tabela. Em outras palavras, ele une uma tabela a si mesmo. Os registros retirados da tabela são combinados com outros registros da mesma tabela. Por que você faria isso? Você pode precisar comparar um valor com outro valor da mesma linha. Você não pode fazer isso a menos que una a tabela a si mesmo e compare os valores como se eles estivessem em dois registros separados.

Não há um operador dedicado a este tipo de consulta. Em vez disso, o self join SQL usa o operador INNER JOIN, um dos operadores OUTER JOIN, ou um CROSS JOIN. A diferença é que uma única tabela é listada como a tabela da esquerda e da direita no join.

Vejamos um exemplo de auto-entrada SQL baseado na tabela employee, mostrada abaixo:

A tabela employee armazena os números de identificação dos funcionários, primeiros nomes, sobrenomes, salários e o número de identificação de seu gerente. Com exceção do patrão (cujo ID = 1), todos têm um supervisor. Somente o chefe pode ter um valor NULL na coluna manager_id.

Vamos tentar uma consulta que devolva o superior imediato de cada funcionário:

SELECT e.id, e.first_name, e.last_name,  e.salary, 
  m.first_name AS fname_boss, m.last_name AS lname_boss
FROM employee e
JOIN employee m ON  e.manager_id = m.id ;

Note que usamos a tabela employee como a tabela da esquerda e da direita no join. Para unir dados da mesma tabela, tivemos que atribuir dois apelidos ao nome da tabela.

Você deve usar pseudônimos ao realizar auto-adesões. Como você está unindo colunas da mesma tabela, elas terão os mesmos nomes. O pseudônimo renomeia as colunas para que o mecanismo do banco de dados possa executar sua consulta. Na consulta acima, usamos a letra e como o pseudônimo para a mesa da esquerda. Neste caso, estamos utilizando a tabela employee para seus registros de funcionários. Utilizamos a carta m como o pseudônimo para a mesa certa, que utilizava o employee tabela para seus registros de gerente. Embora estejamos utilizando uma tabela, SQL a trata como duas tabelas diferentes.

Selecionamos os dados dos funcionários selecionando as colunas id, first_name e last_name no e "mesa". Os dados dos gerentes foram selecionados com referência à m "mesa". Observe que os registros dos funcionários foram prefixados com um "e" e os registros do gerente com um "m". Agora poderíamos nos unir a eles porque utilizamos uma condição de união apropriada: a coluna manager_id do e foi comparado com a coluna id da m. Os registros de ambas as tabelas foram combinados usando um operador JOIN (ou INNER JOIN; lembre-se, o "INNER" está implícito).

Você notou que o chefe não é mostrado nos resultados? Eles não têm correspondência na tabela direita "manager", portanto, não foi possível encontrar dados sobre o superior do chefe.

A imagem abaixo mostra como os registros são correspondidos durante a execução desta consulta.

Vamos analisar os dados na tabela de saída:

Em nossa tabela, o JOIN não retornou um registro para John Watson. Ele não tem um identificador que aponte para a identificação de seu superior, então ele é o chefe. Teríamos que usar um LEFT JOIN ao invés de um INNER JOIN se quiséssemos retornar dados para todos os funcionários, inclusive o chefe. Nesse caso, os registros da tabela da esquerda, sem correspondência na tabela da direita, também seriam mostrados:

SELECT e.id, e.first_name, e.last_name,  e.salary, m.first_name 
   AS fname_boss  , m.last_name  AS lname_boss
FROM employee e
LEFT JOIN employee m ON  e.manager_id = m.id ;

Nossa Cláusulas JOIN em SQL curso tem toda a seção dedicada à prática de auto-adesão.

Quando devemos usar o Self Join em SQL?

As auto-adesões são comumente usadas nas seguintes áreas:

• Relações hierárquicas
• Relações seqüenciais
• Dados gráficos

Analisaremos cada um destes individualmente.

Dados Hierárquicos

O processamento de dados hierárquicos é uma das aplicações mais freqüentes da auto-adesão SQL. Isto ocorre quando há uma coluna adicional apontando para um identificador na mesma tabela, tal como em nosso employee mesa. Em nosso caso, a coluna manager_id refere-se (tem o mesmo valor que) à coluna id.

O exemplo dado acima (a relação empregado-gerente) não é a única situação em que os dados hierárquicos são utilizados. A lista de materiais para um carro compartilha uma estrutura semelhante. Cada carro consiste de múltiplos componentes, tais como o motor, o sistema de freio e o sistema elétrico. Cada um desses componentes é composto de peças menores. O sistema elétrico de um carro pode ser quebrado em seus componentes, tais como a bateria e o alternador (que pode ser quebrado em ainda mais peças do carro). Isto significa que as peças do carro constituem um grupo de dados hierárquicos. O car A tabela apresentada abaixo mostra alguns dados sobre peças de automóveis.

Outro exemplo de dados hierárquicos é a relação pai-filho. Ao armazenar este relacionamento, podemos usar uma única mesa para abrigar uma árvore genealógica inteira. Podemos então usar um self join SQL para recuperar facilmente dados sobre os antepassados de uma determinada pessoa.

A tabela abaixo pode nos ajudar a identificar rapidamente o(s) antepassado(s) mais velho(s) de uma família. Estas pessoas não têm dados sobre os antepassados em seus registros, o que significa que eles formam a raiz da árvore genealógica.

O person A tabela apresentada abaixo ilustra isso:

A consulta abaixo recupera o primeiro e último nome de cada pessoa junto com o primeiro e último nome de sua mãe e de seu pai.

SELECT c.first_name, c.last_name, m.first_name AS fname_mother, m.last_name  AS lname_mother
FROM family c
LEFT JOIN person m ON  c.mather_id = m.id 
LEFT JOIN person f ON  c.father_id = f.id ;

Note que tivemos que nos unir três vezes (ou seja, em três "tabelas" ou pseudônimos) para obter os dados tanto sobre o pai quanto sobre a mãe. John Watson, Anne Brown e Jacob Miller não têm dados que apontem para seus ancestrais.

Os exemplos de estruturas hierárquicas discutidos acima armazenam dados usando uma abordagem de registro superior-inferior. Isto nos permite apresentar os dados como uma estrutura em árvore. Você aprenderá mais sobre o processamento de estruturas em árvore em SQL enquanto continua a aprender sobre bancos de dados relacionais.

Dados seqüenciais

Os dados seqüenciais também podem se beneficiar do uso de uma auto-adesão SQL. Por exemplo, suponha que você tenha registros que descrevam as etapas consecutivas necessárias para preparar um prato. Todas as etapas podem ser colocadas em uma única mesa. Sua ordem é determinada com base nas colunas que apontam para os IDs dos registros anteriores e seguintes na mesma tabela.

Vamos ilustrar isto usando o instruction mesa:

Como você vê, na ordem atual, estas instruções não fazem sentido. Mas quando usamos um self join SQL para mostrar a relação seqüencial para cada etapa da receita, obtemos:

1. Pré-aquecer um forno a 220 graus C.
2. Descasque quatro batatas.
3. Corte as batatas em fatias.
4. Atirar batatas cortadas em rodelas com óleo.
5. Cozer no forno pré-aquecido por 20 minutos.
6. Tempere as fatias quentes com sal e pimenta.

Também temos batatas fritas saborosas no forno!

Gráficos

OSQL Self join também pode ser usado para mostrar as relações necessárias para os gráficos. Um gráfico é uma estrutura constituída de nós conectados entre si com bordas (relações). Um exemplo de um gráfico é a rede rodoviária entre várias cidades.

Dê uma olhada no desenho abaixo.

Este gráfico representa cinco cidades que estão conectadas umas com as outras. Cada flecha mostra uma estrada de uma cidade para outra. Neste caso, as cidades são os nós e as estradas entre elas são as arestas. Estamos usando duas tabelas para armazenar estes dados. A city tabela armazena o número de identificação e o nome de cada cidade. O route A tabela contém o número de identificação da rota, a cidade de partida (a coluna from_city_id ) e a cidade de destino (a coluna to_city_id ).

Aqui está a tabela "cidade":

E esta é a tabela de "rota":

Podemos usar uma auto-adesão SQL no city tabela, juntamente com uma INNER JOIN das duas tabelas, para descobrir que rotas existem entre as cidades.

Dê uma olhada na consulta.

SELECT c1.name AS from_city, c2.name AS to_city 
FROM city c1
JOIN route r ON c1.id = r.from_city_id
JOIN city c2  ON c2.id = r.to_city_id ;

O city e route foram unidas utilizando a coluna id de city e a coluna from_city_id de route. Neste ponto, só pudemos recuperar o nome da cidade inicial. A fim de recuperar o nome da cidade alvo, utilizamos uma auto-inscrição no city mesa. Desta vez, comparamos o id do aliado city tabela com a coluna to_city_id na route mesa.

Aqui está o resultado:

Você também pode usar uma auto-adesão SQL em subconsultas recursivas que armazenam gráficos. Mais informações sobre este tópico podem ser encontradas em nosso novo curso, "Consultas Recursivas".

Usando o SQL Self Join para encontrar valores duplicados

As auto-uniões também podem ser usadas para identificar valores duplicados em uma tabela. Vamos introduzir uma tabela de exemplo chamada color:

Cada registro na tabela é diferente por causa da coluna id, que deve ser sempre única. Mas isto não impede que duas fileiras armazenem o mesmo nome de cor. Queremos identificar tais casos e encontrar os IDs dos nomes das cores duplicadas. Vamos tentar isto:

SELECT c1.id AS id1, c1.name  AS color1, c2.id AS id2, c2.name AS color2
FROM color c1
JOIN color c2 ON c1.name = c2.name AND c1.id < c2.id   ; 

Conseguimos encontrar nomes de cores duplicadas porque nos auto-registamos com base no nome da cor. A segunda condição é usada para pular registros idênticos de ambas as tabelas, bem como os mesmos pares de registros em ordem inversa.

Dê uma olhada no resultado da consulta:

Agora é fácil ver que existem valores duplicados para azul e amarelo.

Saiba mais

Se você gostaria de aprender mais sobre JOINs do que nós cobrimos neste guia ilustrado de auto-adesão SQL, confira nosso Cláusulas JOIN em SQL curso. Lembre-se, a melhor maneira de aprenderCláusulas JOIN em SQL - ou qualquer outra coisa - é aprender fazendo!