Como se juntar à mesma mesa duas vezes

JOIN é uma das afirmações mais comuns em SQL. Como você deve saber, ele é usado para unir e combinar dados de duas ou mais tabelas em um conjunto de dados comum. Neste artigo, vou discutir tipos especiais de uniões? nas quais você combina a mesma tabela duas vezes - inclusive unindo uma tabela a si mesma, também conhecida como self join. Quando e por que você precisa fazer isso? Como você a escreve em SQL? Vamos descobrir.

Junte-se: Uma rápida revisão

Você provavelmente está familiarizado com as junções em SQL. Você tem duas tabelas, A e B, e você as combina usando uma coluna comum a ambas. Aqui está um exemplo:

Nós temos duas tabelas: customer e city, com uma coluna comum chamada city_id.

Agora, se você quiser juntá-las para obter os nomes das respectivas cidades dos clientes, você pode fazer isso com uma junção como esta:

select customer.customer_id,
     customer.firstname,
     customer.lastname,
     customer.birthdate,
     customer.spouse_id,
     customer.city_id,
     city.name as city_name
from customer 
join city 
on customer.city_id = city.city_id;

Nesta declaração de JOIN, correspondemos aos registros de customer e city por uma chave (city_id). Recuperamos todas as 6 colunas do customer tabela e uma coluna, name, da city mesa. Há vários tipos de junções em SQL; este exemplo faz um INNER JOIN.

Eu não vou mergulhar fundo na sintaxe JOIN aqui. Para saber mais, consulte nosso curso interativo sobre joins SQL, que você pode encontrar em LearnSQL.com.br.

O resultado desta união será uma tabela com todos os 6 campos do customer tabela, mais um campo adicional da city mesa:

Juntando a mesma tabela várias vezes

Agora que fizemos uma rápida revisão, vamos olhar para as uniões mais complexas.

Às vezes é preciso juntar várias vezes a mesma mesa. Geralmente, isto envolve adicionar uma ou mais colunas a um conjunto de resultados da mesma tabela, mas a registros diferentes ou por colunas diferentes. Examinaremos dois desses cenários: unir uma tabela a si mesma e unir tabelas com múltiplos relacionamentos.

Auto-Adesão: Juntando uma tabela a si mesma

A auto-adesão é um caso especial da adesão. Em vez de unir duas mesas diferentes, você une uma mesa a si mesmo. Por que quereríamos fazer isso?

Em nosso exemplo acima, queríamos acrescentar uma coluna do city tabela, o nome da cidade, para a customer mesa. Assim, unimos duas mesas diferentes uma à outra. Fazer uma auto-adesão significaria, por exemplo, unir a customer mesa para si mesma.

Aqui está a customer tabela como um lembrete:

A coluna spouse_id armazena o customer_id do cônjuge do cliente. Por exemplo, os Clientes 1 e 2 (John e Mary) são cônjuges um do outro, os Clientes 3 e 5 (Lisa e Tim) são cônjuges um do outro, e assim por diante. Podemos adicionar o primeiro nome e o sobrenome do cônjuge a cada registro no customer mesa. Para fazer isso, precisamos realizar uma auto-adesão, ou seja, unir-se à customer mesa para si mesma:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.spouse_id,
      spouse.firstname as spouse_firstname,
      spouse.lastname as spouse_lastname
from customer cust 
join customer spouse
on cust.spouse_id = spouse.customer_id;

Quando você executa este código, o resultado é o seguinte:

Agora que você já viu um exemplo de caso de uso de auto-adesão, vamos rever sua sintaxe SQL.

Sintaxe de auto-adesão

A sintaxe para a auto-adesão é muito semelhante a qualquer outro tipo de adesão. Aqui está o código de nosso exemplo de auto-adesão:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.spouse_id,
      spouse.firstname as spouse_firstname,
      spouse.lastname as spouse_lastname
from customer cust
join customer spouse
on cust.spouse_id = spouse.customer_id;

É uma declaração JOIN na qual o customer tabela é usada duas vezes.

As informações sobre o cônjuge, tais como seu nome, são armazenadas na mesma tabela que um cliente separado com seu próprio customer_id. Uma vez que spouse_id contém o customer_id do cônjuge, precisamos unir a tabela consigo mesmo para obter o nome do cônjuge.

Pode-se pensar em uma união como uma união entre duas cópias da mesma mesa. Para cada registro com um valor não-nulo em spouse_id, procuramos o valor de customer_id que corresponda a ele. Quando encontramos uma correspondência, as colunas firstname e lastname são adicionadas à tabela resultante.

Os pseudônimos de mesa são necessários em uma auto-adesão. O código não funciona sem eles, pois não saberia a qual cópia da tabela você está se referindo. Aqui, eu estou usando os pseudônimos cust e spouse.

Logo antes da palavra-chave FROM, escolhemos colunas que queremos manter na tabela resultante. Precisamos usar os pseudônimos da tabela para a recuperação de colunas (cust.firstname, cust.lastname, spouse.firstname, etc.). Mantemos cinco colunas da tabela de customer tabela e anexar da mesma tabela duas colunas que contenham o nome do cônjuge.

Esta é uma união interna, mas você pode usar qualquer tipo de união: LEFT JOIN, RIGHT JOIN, CROSS JOIN, etc. Em nosso exemplo, temos um "inner join" que retorna apenas os registros correspondentes; ou seja, apenas os clientes com cônjuges são devolvidos. Donna não tem um cônjuge na tabela, portanto Donna não está incluída no conjunto de dados resultante.

Se você quiser saber mais sobre as adesões, recomendo nosso artigo "Como Aprender Cláusulas JOIN em SQL."Há também um curso interativo de adesão ao SQL disponível em nosso LearnSQL.com.br plataforma.

Exemplos de auto-adesão

O acima é apenas um exemplo. Uma vez que você começa a usar SQL diariamente, você se depara com a necessidade de auto-adesão com bastante freqüência.

Um caso de uso comum para a auto-adesão é quando há uma hierarquia entre os registros em uma tabela. Este tipo de estrutura de dados é chamado de estrutura em árvore, e muitas vezes você precisa unir-se à tabela consigo mesmo em SQL. Aqui estão alguns exemplos.

Exemplo 1: Hierarquia de funcionários

Cada funcionário tem um gerente, e um gerente por sua vez tem seu gerente, todos na mesma mesa. Se você quiser adicionar as informações correspondentes do gerente a cada registro, você precisa fazer uma auto-adesão. Cobrimos este exemplo no artigo "Um Guia Ilustrado para o Auto-adesão SQL", então dê uma olhada nele para ver como isto se parece.

Exemplo 2: Hierarquia do Departamento

Cada departamento dentro de uma organização tem um pai: por exemplo, o departamento de Ciência de Dados está sob o departamento de TI, o departamento de TI está sob o Suporte Empresarial, e o Suporte Empresarial está sob a diretoria.

Considere a tabela a seguir, departmento que se parece com isto:

Agora, se você quiser anexar o nome dos pais a cada departamento, você precisa escrever um nome próprio:

select c.*,
   p.name as parent_name
from department c 
left join department p 
on c.parent_department_id=p.department_id;

Dentro deste SELECT, nós nos unimos ao department tabela com ela mesma para obter o nome do departamento pai como um campo adicional. Note que o registro cujo department_id é 1 não tem um pai (parent_department_id é NULL; não é preenchido). Isto porque o conselho de administração está no topo da estrutura em árvore. Queremos exibir este registro no resultado, por isso usamos um LEFT JOIN e não um INNER JOIN.

Quando você executa este código, a tabela resultante é parecida com esta:

Você pode ver facilmente o nome pai ao qual cada departamento pertence: TI está sob Administração, Administração está sob o Conselho, etc.

Exemplo 3: Hierarquia de Categorias

Leve anúncios classificados - essas páginas populares da web onde você pode alugar, comprar ou vender qualquer coisa, desde imóveis a produtos e serviços diversos. Para colocar um anúncio, você escolhe uma categoria e uma subcategoria para seu anúncio. Por exemplo, se você vende imóveis, você escolheria entre subcategorias como casa, apartamento, ou terreno.

Temos uma tabela chamada category que contém informações sobre essas categorias e subcategorias, bem como sobre suas relações. As relações entre as categorias e subcategorias desta tabela são armazenadas em uma estrutura pai-filho como esta:

Todas as categorias e subcategorias estão nesta única tabela. Agora, se você quiser adicionar informações sobre o pai a cada registro, você precisará fazer uma auto-adesão - junte a esta tabela a si mesmo:

select subcategory.*,
    main.category_name as parent_name
from category subcategory 
left join category main 
on subcategory.parent_category_id = main.category_id;

Aqui está o resultado da execução desta instrução SQL:

Relações Múltiplas entre duas Tabelas

Há situações ao lado do self join em que você precisa se juntar à mesma mesa mais de uma vez. Uma é quando você tem múltiplas relações entre duas mesas diferentes. É aqui que você se une à mesma mesa duas vezes, mas geralmente a alguma outra mesa e não necessariamente a si mesmo.

Suponha que o customer A tabela tem dois campos que contêm identificações de cidades. Isto é comum se você tiver duas cidades diferentes para cada cliente?-?por exemplo, a cidade de residência (residence_city_id) e a cidade do endereço postal para onde os avisos devem ser enviados (notice_city_id):

Também temos city que tem a identificação da cidade (city_id) e o nome da cidade (name), como visto anteriormente e mostrado abaixo como um lembrete:

Agora, se você quiser exibir os nomes das cidades, você terá que se juntar ao city tabela duas vezes:

select cust.customer_id,
      cust.firstname,
      cust.lastname,
      cust.birthdate,
      cust.residence_city_id,
      cust.notice_city_id,
      residence_city.name as residence_city_name,
      notice_city.name as notice_city_name
from customer cust 
join city residence_city 
on cust.residence_city_id=residence_city.city_id
join city notice_city 
on cust.notice_city_id=notice_city.city_id;

Vamos quebrar o que está acontecendo neste código. Primeiro, nós nos juntamos a customer e city com residence_city_id como a chave. Conseguimos residence_city_name fazendo a correspondência para city_id no city mesa. Uma segunda junção é realizada entre customer e city para obter notice_city_name. A chave utilizada aqui é notice_city_id que também corresponde a city_id no city mesa.

Usamos pseudônimos de mesa cust para customer, residence_city para a primeira cópia de city para obter o nome da cidade residência, e notice_city para a segunda cópia da cidade para obter o nome da cidade notice. Usamos os pseudônimos para definir as colunas na tabela resultante. Os pseudônimos também são usados durante o join para definir as colunas chave. Novamente, os pseudônimos são necessários para distinguir as duas cópias de city.

Quando você executa este código, você obtém o seguinte resultado:

Temos agora duas colunas adicionais com os nomes das cidades correspondentes.

Usamos aqui as uniões simples (isto é, internas), mas você pode usar qualquer tipo de união, conforme a necessidade. Se você é novo em SQL Joins e quer ler mais sobre seus diferentes tipos, recomendo os artigos "Como aprender joins" e "Como praticar joins" que cobrem estes tópicos. Se você preferir aprender assistindo a vídeos. Eu recomendo vivamente o episódio que discute as adesões.

Juntando-se à mesma mesa duas vezes

Neste artigo, discutimos quando você precisa se juntar à mesma tabela duas vezes em SQL e vimos alguns casos comuns de uso comercial. Explicamos como fazer isso e como é a sintaxe SQL. Auto-ajustamento com dados hierárquicos e relações múltiplas entre duas tabelas são apenas duas das situações para as quais você precisa unir a mesma tabela duas vezes. Existem outras; geralmente, elas envolvem a adição de uma ou mais colunas a um conjunto de resultados da mesma tabela na mesma coluna.

Se você quiser saber mais sobre junções, incluindo auto junções, recomendo nosso curso interativo SQL Joins disponível em nosso LearnSQL.com.br plataforma. Quando se trata de SQL, é importante praticar; nosso curso é projetado exatamente para isso!