Self-joins em SQL: juntando uma tabela com ela mesma

Um self-join é um JOIN comum em que os dois lados referenciam a mesma tabela. Não existe sintaxe especial para isso: você simplesmente lista a tabela duas vezes e dá a ela dois aliases diferentes. Soa estranho, mas é a ferramenta de trabalho para hierarquias (funcionário → gerente), comparar linhas entre si e montar pares. Vamos ver tudo isso sobre um esquema concreto.

Ao longo do artigo, usaremos esta tabela de funcionários:

CREATE TABLE employees (
    id          bigint PRIMARY KEY,
    name        text NOT NULL,
    manager_id  bigint REFERENCES employees(id),  -- points back to this table
    department  text,
    salary      numeric(10, 2),
    hired_at    date
);

Por que juntar uma tabela com ela mesma

A ideia central: uma linha de uma tabela precisa «enxergar» outra linha da mesma tabela. Se os gerentes morassem em uma tabela managers separada, você escreveria um JOIN normal. Mas manager_id aponta de volta para employees, então você precisa trazer a tabela duas vezes.

Aqui os aliases não são um enfeite opcional: são obrigatórios. Sem eles, o motor não consegue distinguir a «linha do funcionário» da «linha do gerente», e você vai receber um erro de coluna ambígua.

• e — a linha do funcionário;
• m — a linha do gerente desse funcionário;
• a condição de junção liga e.manager_id a m.id.

A hierarquia funcionário → gerente

O caso clássico é mostrar cada funcionário ao lado do seu gerente:

SELECT e.name        AS employee,
       m.name        AS manager
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id;

Há uma armadilha aqui: o CEO tem manager_id IS NULL, e um JOIN (interno) comum descarta essa linha silenciosamente — NULL não casa com nada. Para manter todo mundo, inclusive quem não tem chefe, use um LEFT JOIN:

SELECT e.name                          AS employee,
       COALESCE(m.name, '- none -')    AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
ORDER BY m.name NULLS FIRST, e.name;

COALESCE substitui NULL por um valor de preenchimento, e NULLS FIRST faz o CEO subir para o topo. Um único self-join te dá exatamente um nível da hierarquia. Para uma profundidade arbitrária (o gerente de um gerente, e assim por diante) você precisa de uma CTE recursiva WITH RECURSIVE — mas esse é outro assunto.

Comparar linhas dentro de uma tabela

Os self-joins brilham quando você precisa comparar linhas entre si. Por exemplo, encontrar os funcionários que ganham mais do que o próprio gerente:

SELECT e.name   AS employee,
       e.salary AS emp_salary,
       m.name   AS manager,
       m.salary AS mgr_salary
FROM employees AS e
JOIN employees AS m ON m.id = e.manager_id
WHERE e.salary > m.salary;

Ou comparar cada pessoa com um colega do mesmo departamento para ver quem foi contratado primeiro:

SELECT a.name AS earlier, b.name AS later, a.department
FROM employees AS a
JOIN employees AS b
  ON a.department = b.department
 AND a.hired_at < b.hired_at;       -- a was hired strictly before b

Repare que a.hired_at < b.hired_at faz dupla função: ordena cada par por tempo e, ao mesmo tempo, impede que uma linha se junte com ela mesma.

Pares sem duplicatas nem autocorrespondências

Se você quer cada par de funcionários dentro de um departamento (digamos, para emparelhar revisões de código), um JOIN ingênuo sobre a.department = b.department produz lixo: cada linha casa com ela mesma (Alice–Alice) e cada par aparece duas vezes (Alice–Bob e Bob–Alice).

A solução é um único predicado — comparar por id:

SELECT a.name AS person_a,
       b.name AS person_b,
       a.department
FROM employees AS a
JOIN employees AS b
  ON a.department = b.department
 AND a.id < b.id          -- strict < kills both self-pairs and duplicates
ORDER BY a.department, person_a;

• a.id <> b.id removeria apenas a autocorrespondência, mas as duplicatas continuariam;
• a.id < b.id remove as duas: cada par não ordenado sobrevive exatamente uma vez.

Esse truque da desigualdade estrita é o padrão mais comum na hora de gerar pares.

Pegadinha. Atenção ao desempenho: um self-join sobre um manager_id ou department sem índice em uma tabela grande pode degenerar em um Nested Loop caro. Indexe suas colunas de junção (CREATE INDEX ON employees(manager_id)) e confira o plano com EXPLAIN ANALYZE.

Diferenças entre bancos de dados

A sintaxe do self-join é SQL padrão e se comporta da mesma forma em PostgreSQL, MySQL e ClickHouse, mas fique atento a alguns detalhes:

• PostgreSQL / MySQL — tudo acima funciona como está escrito; o LEFT JOIN para preservar as linhas com pai NULL é obrigatório.
• MySQL anterior ao 8.0 não tinha WITH RECURSIVE, então hierarquias profundas precisavam ser construídas de forma procedural; o 8.0+ dá suporte a recursão.
• ClickHouse — JOIN existe, mas é mais custoso e menos flexível; hierarquias costumam ser resolvidas com funções de array ou desnormalização, e a igualdade com NULL em um JOIN se comporta de outra maneira. Com grandes volumes de dados no ClickHouse, evite um self-join de desigualdade (a.id < b.id) — ele paraleliza mal.

Resumo: um self-join é apenas um JOIN de uma tabela com ela mesma por meio de aliases. Lembre-se de três movimentos: LEFT JOIN para hierarquias com pai NULL, comparação de colunas para análise de linhas e uma desigualdade estrita de id para pares sem duplicatas.