SQL Self Join

Na Loja Aurora, cada funcionário tem um chefe, e o chefe também é um funcionário: está na mesma tabela funcionarios. A coluna chefe_id guarda só o número do chefe. Para ver o nome do chefe ao lado do nome de cada funcionário, você precisa juntar a tabela com ela mesma. Isso tem nome: self join (em inglês, "junção consigo mesma"). Não é um comando novo; é um JOIN comum, com um truque nos apelidos.

Você vai aprender

Quando uma tabela "aponta para ela mesma" e por que isso pede um self join.
Como abrir a mesma tabela duas vezes com apelidos diferentes (f e c).
Por que a gerente some com INNER JOIN e volta com LEFT JOIN.
Como contar a equipe de cada chefe e subir dois níveis (o chefe do chefe).
Como formar pares de clientes da mesma cidade sem repetir e sem parear a pessoa com ela mesma.
Os erros reais: ambiguous column name e a ligação ON invertida, que não dá erro nenhum.

Todos os exemplos rodam de verdade no seu navegador, no banco da Loja Aurora (veja O Banco da Loja Aurora). Se o JOIN ainda é novidade, comece por INNER JOIN e LEFT JOIN; esta aula usa os dois.

A ideia: a mesma lista impressa duas vezes

Imagine que a Ana imprimiu a lista de funcionários da loja. Ao lado de cada nome há um número: o número do chefe. Para descobrir quem é o chefe do Igor, ela olha o número dele (5), corre o dedo pela mesma lista até achar a linha de número 5 e lê o nome: Luana Castelo.

Agora imagine que ela imprimiu a lista duas vezes e pôs as folhas lado a lado. Na da esquerda, ela lê o funcionário; na da direita, procura o chefe. São a mesma lista, só que cada folha tem um papel diferente. É exatamente isso que o self join faz:

Na mesa da Ana	No SQL
A folha da esquerda: "os funcionários"	`funcionarios AS f`
A folha da direita: "os chefes"	`funcionarios AS c`
"O número do chefe do Igor é o número da linha do chefe"	`ON f.chefe_id = c.id`

Os apelidos f e c (aula Apelidos (AS)) são as etiquetas nas duas folhas. Sem elas, o banco não sabe de qual cópia você está falando.

1. A tabela que aponta para ela mesma

Veja a tabela inteira. Repare na última coluna:

SELECT id, nome, cargo, chefe_id
FROM funcionarios;

A coluna chefe_id é uma chave estrangeira (aula FOREIGN KEY) como as outras, com uma diferença: ela não aponta para outra tabela, aponta para a coluna id da própria funcionarios. Lendo os números, dá para montar o organograma da loja:

Marta Lustosa (1, Gerente)           chefe_id NULL: não tem chefe
├── Caio Teles (2)                   chefe_id 1
├── Renata Borges (3)                chefe_id 1
├── Pedro Araújo (4)                 chefe_id 1
└── Luana Castelo (5, Supervisora)   chefe_id 1
    ├── Igor Mendes (6)              chefe_id 5
    ├── Sofia Rocha (7)              chefe_id 5
    └── Davi Nunes (8, Estoquista)   chefe_id 5

A Marta é a gerente: ninguém manda nela, e por isso o chefe_id dela é NULL (aula O Valor NULL). Guarde esse detalhe, ele vai fazer diferença na seção 3.

2. O primeiro self join: quem é o chefe de quem

Agora a consulta que põe o nome do chefe ao lado de cada funcionário:

SELECT f.nome AS funcionario, c.nome AS chefe
FROM funcionarios AS f
INNER JOIN funcionarios AS c ON f.chefe_id = c.id;

FROM funcionarios AS f: A primeira "cópia" da tabela, com o apelido f (de funcionário). É a folha da esquerda.
INNER JOIN funcionarios AS c: A mesma tabela de novo, agora com o apelido c (de chefe). O banco não copia nada de verdade: ele só lê a tabela duas vezes, como se fossem duas.
ON f.chefe_id = c.id: A regra que liga as duas: "o chefe_id do funcionário é o id do chefe". É aqui que o self join acontece.
f.nome AS funcionario: O nome lido da cópia f, com o nome de coluna funcionario no resultado.
c.nome AS chefe: O nome lido da cópia c. As duas colunas se chamam nome na tabela; os apelidos de coluna (AS funcionario, AS chefe) é que deixam o resultado legível.

Para entender como o banco chegou a cada linha, siga o Igor:

Na cópia f, o banco encontra a linha do Igor: id 6, chefe_id 5.
Ele procura, na cópia c, as linhas em que c.id = 5. Só tem uma: a da Luana.
Junta as duas numa linha do resultado: f.nome = Igor Mendes, c.nome = Luana Castelo.
Repete isso para cada funcionário da cópia f.

Conte as linhas do resultado: são 7, e a loja tem 8 funcionários. Quem ficou de fora? A Marta. Isso não é defeito do self join; é o comportamento normal do INNER JOIN, e a seção 3 explica.

Missão: a Luana quer a lista só da equipe dela. Acrescente um WHERE que filtre pelo nome do chefe (c.nome) e deixe só as 3 pessoas que respondem a ela.

SELECT f.nome AS funcionario, c.nome AS chefe
FROM funcionarios AS f
INNER JOIN funcionarios AS c ON f.chefe_id = c.id;

SELECT f.nome AS funcionario, c.nome AS chefe
FROM funcionarios AS f
INNER JOIN funcionarios AS c ON f.chefe_id = c.id
WHERE c.nome = 'Luana Castelo';

3. Não perca a gerente: LEFT JOIN

A Marta sumiu porque o chefe_id dela é NULL. A condição f.chefe_id = c.id vira NULL = c.id, e uma comparação com NULL nunca é verdadeira (aula O Valor NULL). O INNER JOIN só mostra linhas que encontraram par; a Marta não encontrou nenhum chefe, então ficou fora.

Para mostrar todos os funcionários, até quem não tem chefe, troque por LEFT JOIN (aula LEFT JOIN): todas as linhas da cópia da esquerda (f) aparecem, e onde não houver par, as colunas da direita (c) vêm NULL.

SELECT f.nome AS funcionario, c.nome AS chefe
FROM funcionarios AS f
LEFT JOIN funcionarios AS c ON f.chefe_id = c.id;

Agora são 8 linhas, e a Marta aparece com o chefe NULL. Se você quiser escrever "(ninguém)" no lugar do NULL, existe uma função para isso: IFNULL(c.nome, '(ninguém)'). Ela tem aula própria, mais adiante: IFNULL e COALESCE.

Qual dos dois usar?

Pergunte-se: "quem não tem par deve aparecer?". Para um organograma completo, sim: LEFT JOIN. Para "a lista de quem tem chefe", não: INNER JOIN.

4. Do outro lado: a equipe de cada chefe

A mesma ligação serve para olhar a hierarquia de cima para baixo. Agora a cópia c vem primeiro, e você agrupa por chefe para contar quantas pessoas cada um comanda (aulas GROUP BY e COUNT()):

SELECT c.nome AS chefe, COUNT(*) AS equipe
FROM funcionarios AS c
INNER JOIN funcionarios AS f ON f.chefe_id = c.id
GROUP BY c.id, c.nome;

A Marta comanda 4 pessoas (Caio, Renata, Pedro e Luana) e a Luana comanda 3. Repare que a regra do ON continua igual: f.chefe_id = c.id. O que mudou foi só a ordem das tabelas e o agrupamento. Quem não chefia ninguém (o Caio, por exemplo) não aparece, pelo mesmo motivo da seção 3: não achou par.

5. Dois degraus: o chefe do chefe

Dá para abrir a tabela uma terceira vez. A cópia cc é "o chefe do chefe":

SELECT f.nome  AS funcionario,
       c.nome  AS chefe,
       cc.nome AS chefe_do_chefe
FROM funcionarios AS f
INNER JOIN funcionarios AS c  ON f.chefe_id = c.id
LEFT  JOIN funcionarios AS cc ON c.chefe_id = cc.id;

INNER JOIN … AS c ON f.chefe_id = c.id: O primeiro degrau, igual à seção 2.
LEFT JOIN … AS cc ON c.chefe_id = cc.id: O segundo degrau: agora quem procura o chefe é a cópia c. O LEFT garante que quem responde direto à Marta continue no resultado, com chefe_do_chefe NULL (a Marta não tem chefe).

O Igor, a Sofia e o Davi respondem à Luana, que responde à Marta. Cada degrau a mais é mais um JOIN. Para uma hierarquia de profundidade desconhecida (dez, vinte níveis), escrever um JOIN por degrau não funciona; para isso existe a CTE Recursiva, no Nível 3.

6. Pares na mesma tabela: clientes da mesma cidade

O self join não serve só para chefes. Ele serve sempre que você quer comparar uma linha com outras linhas da mesma tabela. A Ana quer saber quais clientes moram na mesma cidade, para combinar entregas juntas. Comece por Recife, que tem só dois clientes:

SELECT a.nome, b.nome
FROM clientes AS a
INNER JOIN clientes AS b ON a.cidade = b.cidade
WHERE a.cidade = 'Recife';

Quatro linhas para duas pessoas? O banco combinou cada cliente de Recife com cada cliente de Recife, inclusive com ele mesmo: Juliana com Juliana, Bruno com Bruno. E o par Juliana–Bruno aparece duas vezes, uma em cada ordem. Vamos limpar em dois passos.

Passo 1: tire a pessoa pareada com ela mesma. Exija que os id sejam diferentes:

SELECT a.nome, b.nome
FROM clientes AS a
INNER JOIN clientes AS b ON a.cidade = b.cidade AND a.id <> b.id
WHERE a.cidade = 'Recife';

Passo 2: tire o par repetido. Troque <> por <. Assim só vale a ordem em que o id da esquerda é o menor (Juliana é a 10, Bruno é o 19), e cada par aparece uma vez só:

SELECT a.nome, b.nome
FROM clientes AS a
INNER JOIN clientes AS b ON a.cidade = b.cidade AND a.id < b.id
WHERE a.cidade = 'Recife';

Condição extra	Recife (2 clientes)	O que sobra
nenhuma	4 linhas	todas as combinações, até a pessoa com ela mesma
`a.id <> b.id`	2 linhas	cada par duas vezes (A–B e B–A)
`a.id < b.id`	1 linha	cada par uma vez só

Sem o WHERE de Recife, a mesma consulta com a.id < b.id devolve os pares de todas as cidades: são 88.

Missão: Fortaleza tem 7 clientes. A consulta abaixo devolve 49 linhas, porque combina todo mundo com todo mundo. Mude a condição do ON para que cada par de clientes de Fortaleza apareça uma vez só, sem ninguém pareado consigo mesmo. (Dica: o certo dá 21 linhas.)

SELECT a.nome, b.nome
FROM clientes AS a
INNER JOIN clientes AS b ON a.cidade = b.cidade
WHERE a.cidade = 'Fortaleza';

SELECT a.nome, b.nome
FROM clientes AS a
INNER JOIN clientes AS b ON a.cidade = b.cidade AND a.id < b.id
WHERE a.cidade = 'Fortaleza';

7. Formas diferentes de escrever o mesmo self join

As três consultas abaixo respondem "quem é o chefe de quem". As duas primeiras dão as mesmas 7 linhas da seção 2; a terceira dá 8, porque mantém a Marta, como o LEFT JOIN.

Forma antiga, com vírgula e WHERE:

SELECT f.nome AS funcionario, c.nome AS chefe
FROM funcionarios f, funcionarios c
WHERE f.chefe_id = c.id;

Aqui as tabelas vêm separadas por vírgula e a ligação vai no WHERE. Também repare que o AS foi omitido: funcionarios f é o mesmo que funcionarios AS f. Funciona, mas o JOIN … ON separa melhor "como ligar" (no ON) de "o que filtrar" (no WHERE).

Com uma subconsulta no SELECT:

SELECT f.nome AS funcionario,
       (SELECT c.nome FROM funcionarios AS c WHERE c.id = f.chefe_id) AS chefe
FROM funcionarios AS f;

Para cada funcionário, o banco roda a consulta entre parênteses e busca o nome do chefe. Quando não acha (a Marta), ela devolve NULL, por isso as 8 linhas. Isso é uma subconsulta, assunto da aula Subconsultas.

Forma	Quem não tem chefe	Quando usar
`INNER JOIN … ON`	some	a forma recomendada, quando só interessa quem tem par
`LEFT JOIN … ON`	aparece com `NULL`	listas completas (organograma, relatórios)
vírgula + `WHERE`	some	só para ler código antigo; prefira o `JOIN`
subconsulta no `SELECT`	aparece com `NULL`	quando você quer só uma coluna da outra cópia

Quando dá errado

Esqueceu os apelidos

A tabela aparece duas vezes, mas sem etiqueta:

SELECT nome
FROM funcionarios
INNER JOIN funcionarios ON chefe_id = id;

A mensagem: ambiguous column name: nome, "nome de coluna ambíguo: nome". Agora existem duas colunas nome (uma em cada cópia) e o banco não sabe qual você quer. Tentar resolver com o nome da tabela não adianta, porque as duas cópias têm o mesmo nome: funcionarios.nome dá ambiguous column name: funcionarios.nome.

Como consertar: dê um apelido diferente a cada cópia (AS f, AS c) e use o apelido antes de toda coluna: f.nome, c.nome, f.chefe_id, c.id.

Deu o mesmo apelido às duas cópias

SELECT f.nome
FROM funcionarios AS f
INNER JOIN funcionarios AS f ON f.chefe_id = f.id;

A mensagem: ambiguous column name: f.nome. É o mesmo problema de cima: dois f são tão ambíguos quanto nenhum.

Como consertar: um apelido por cópia, todos diferentes.

O ON invertido: sem erro, mas errado

A Ana trocou os lados da ligação: escreveu f.id = c.chefe_id em vez de f.chefe_id = c.id.

SELECT f.nome AS funcionario, c.nome AS chefe
FROM funcionarios AS f
INNER JOIN funcionarios AS c ON f.id = c.chefe_id;

O que acontece: nenhuma mensagem, 7 linhas, tudo com cara de certo. Mas leia: segundo esse resultado, o "chefe" da Marta é o Caio! A ligação invertida faz c ser o subordinado, e os nomes das colunas (funcionario, chefe) passaram a mentir. Esse é o erro mais perigoso do self join, justamente porque o banco não reclama.

Como consertar: leia o ON em voz alta: "o chefe_id do funcionário é o id do chefe": f.chefe_id = c.id. E confira uma linha que você conhece (o Igor tem de aparecer com a Luana).

Duas colunas com o mesmo nome no resultado

SELECT f.nome, c.nome
FROM funcionarios AS f
INNER JOIN funcionarios AS c ON f.chefe_id = c.id;

O que acontece: funciona, mas o cabeçalho tem nome e nome. Quem lê o resultado (ou um programa que pega as colunas pelo nome) não sabe qual é o funcionário e qual é o chefe.

Como consertar: num self join, dê sempre um apelido de coluna: f.nome AS funcionario, c.nome AS chefe.

Nos outros bancos

O self join não é um comando especial: é um JOIN comum em que as duas tabelas são a mesma. Por isso ele se escreve igual no MySQL, no PostgreSQL e no SQL Server. O que muda de banco para banco é só o texto das mensagens de erro quando falta o apelido.

Referência rápida

Escrita	O que faz
`FROM t AS a INNER JOIN t AS b ON …`	Junta a tabela `t` com ela mesma; `a` e `b` são as duas cópias
`ON f.chefe_id = c.id`	Liga cada linha à linha "de cima" (o chefe)
`LEFT JOIN`	Mantém quem não tem par (a gerente, com chefe `NULL`)
`AND a.id <> b.id`	Não pareia uma linha com ela mesma
`AND a.id < b.id`	Cada par uma vez só
Uma cópia a mais (`AS cc`)	Sobe mais um degrau da hierarquia

Mensagem do SQLite	Quer dizer
`ambiguous column name: nome`	Há duas colunas `nome` e você não disse de qual cópia: faltou o apelido
`ambiguous column name: f.nome`	Duas cópias com o mesmo apelido

Exercícios

1. A ligação

Complete a condição que liga cada funcionário (f) ao seu chefe (c):

FROM funcionarios AS f INNER JOIN funcionarios AS c ON f.chefe_id =

2. Sem perder ninguém

Que tipo de JOIN mostra também a gerente, que não tem chefe?

FROM funcionarios AS f funcionarios AS c ON f.chefe_id = c.id

3. Cada par uma vez

Complete o operador para que cada par de clientes da mesma cidade apareça uma vez só:

ON a.cidade = b.cidade AND a.id b.id

4. Por que um self join precisa de apelidos?

Porque o SQLite não aceita a mesma tabela duas vezes Porque as duas cópias têm as mesmas colunas, e sem apelido o banco não sabe de qual você está falando Porque os apelidos deixam a consulta mais rápida Não precisa: os apelidos são opcionais

5. Com INNER JOIN funcionarios AS c ON f.chefe_id = c.id, quantas linhas saem, e por quê?

8, uma por funcionário 2, uma por chefe 7, porque a Marta tem chefe_id NULL e não acha par 0, porque a tabela não pode se juntar com ela mesma

6. A Ana escreveu ON f.id = c.chefe_id no lugar de ON f.chefe_id = c.id. O que acontece?

A consulta roda, mas a coluna "chefe" passa a mostrar os subordinados Dá ambiguous column name Dá o mesmo resultado, porque a ordem do = não importa Não sai nenhuma linha

Missão final: o organograma completo

Missão: a Ana quer imprimir o organograma da loja com todos os 8 funcionários, cada um com o nome do chefe ao lado. A consulta abaixo esquece a gerente. Conserte-a, mantendo as colunas funcionario e chefe.

SELECT f.nome AS funcionario, c.nome AS chefe
FROM funcionarios AS f
INNER JOIN funcionarios AS c ON f.chefe_id = c.id;

SELECT f.nome AS funcionario, c.nome AS chefe
FROM funcionarios AS f
LEFT JOIN funcionarios AS c ON f.chefe_id = c.id;

Quer ir além? Acrescente a coluna c.cargo AS cargo_do_chefe, ou uma terceira cópia para mostrar o chefe do chefe, como na seção 5.

Resumo

Self join é juntar uma tabela com ela mesma. Não é um comando novo: é um JOIN comum em que as duas tabelas são a mesma.
Ele aparece quando uma coluna aponta para a própria tabela, como funcionarios.chefe_id → funcionarios.id, e quando você quer comparar linhas da mesma tabela entre si.
Os apelidos são obrigatórios e têm de ser diferentes (f, c); sem eles sai ambiguous column name. Dê também apelidos às colunas do resultado.
INNER JOIN some com quem não tem par (a gerente); LEFT JOIN mantém, com NULL.
Para pares: a.id <> b.id tira a linha com ela mesma; a.id < b.id mostra cada par uma vez.
Cuidado com o ON invertido: ele não dá erro, mas troca chefe por subordinado. Confira sempre uma linha conhecida.