Docs executáveis de SQLite: Self Join

Q: O que é um SELF JOIN no SQLite?

É um JOIN comum em que uma tabela é unida com ela mesma. Você atribui dois aliases diferentes à mesma tabela para que o SQLite a trate como duas fontes de linhas distintas, e então relaciona as linhas por uma coluna que conecta um registro a outro — geralmente um vínculo do tipo pai/filho, como funcionário e gerente.

Q: Por que preciso de aliases em um SELF JOIN?

Sem aliases, o SQLite não tem como saber a qual cópia da tabela você está se referindo ao usar o nome de uma coluna. Dando um alias para cada instância (por exemplo, e para o funcionário e m para o gerente), você consegue escrever e.manager_id = m.id sem ambiguidade. Os aliases não são opcionais aqui — sem eles, a query nem sequer é interpretada.

Q: Quando usar SELF JOIN em vez de subquery?

Use SELF JOIN quando você quiser exibir colunas das duas linhas no mesmo resultado — por exemplo, mostrar o nome do funcionário e o nome do gerente lado a lado. A subquery é melhor quando você só precisa filtrar ou buscar um valor isolado. Já para hierarquias com muitos níveis, nenhuma das duas se sai bem: nesse caso, o ideal é uma CTE recursiva.

Self join no SQLite é só um JOIN com alias

Não tem mistério nenhum em um self join. É um JOIN comum, só que os dois lados acabam sendo a mesma tabela. O detalhe é que o SQLite precisa de algum jeito de diferenciar as duas cópias, e é aí que entram os aliases.

Você usa self join sempre que uma linha da tabela faz referência a outra linha da mesma tabela. O exemplo clássico é uma tabela employees em que cada linha tem um manager_id apontando para outro funcionário:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT * FROM employees;

Ada não tem chefe. Boris e Cleo se reportam a Ada. Diego e Esme se reportam a Boris. Toda essa relação mora dentro de uma única tabela — e é justamente aí que o self join no SQLite mostra seu valor.

A estrutura básica do self join

Para listar cada funcionário junto com o nome do gerente, basta fazer um join da tabela employees com ela mesma. Uma cópia entra no papel de "funcionário" e a outra no papel de "gerente":

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

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

Pense nisso como duas tabelas que, por acaso, compartilham o mesmo armazenamento. O e é a linha do funcionário; o m é a linha do gerente. A condição de junção e.manager_id = m.id faz o alinhamento: para cada funcionário, busca a linha em m cujo id bate com o manager_id daquele funcionário.

Repare que a Ada ficou de fora. O manager_id dela é NULL, e o INNER JOIN descarta as linhas que não casam.

Mantendo as linhas sem correspondência: LEFT JOIN

Se você quer todo mundo no resultado, incluindo quem não tem gerente, troque para LEFT JOIN:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

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

Agora a Ada aparece com NULL na coluna de gerente. A mecânica do self join é a mesma — só muda o tipo de junção, que faz o que o LEFT JOIN sempre faz: mantém todas as linhas do lado esquerdo e preenche com vazio quando não há correspondência.

Esse é o formato que você normalmente quer quando vai exibir uma lista de pessoas. "Sem gerente" também é informação; descartar a linha não faz sentido.

Os aliases não são opcionais

Tente fazer o self join sem aliases e o SQLite não vai entender o que você quer dizer:

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- Erro: nome de coluna ambíguo: name

Cada coluna aparece duas vezes — uma de cada cópia da tabela — e o SQLite não tem como saber qual delas você quer. Os aliases resolvem isso, dando um nome próprio para cada instância. Escolha aliases que descrevam o papel que aquela linha está exercendo, não o nome da tabela:

e e m para employee/manager (funcionário/gerente).
parent e child para hierarquias.
a e b quando você está comparando pares quaisquer.

O alias é justamente o que faz um self join ficar legível.

Encontrando pares dentro da mesma tabela

Self join não serve só para hierarquias. Sempre que você precisa comparar linhas da mesma tabela, esse padrão se encaixa. Veja esta lista de produtos, onde queremos achar todos os pares com o mesmo preço:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Caneca',     9.99),
    ('Caderno',    9.99),
    ('Caneta',     2.50),
    ('Lápis',      2.50),
    ('Luminária', 29.00);

SELECT a.name AS produto_a, b.name AS produto_b, a.price
FROM products AS a
INNER JOIN products AS b
    ON a.price = b.price
   AND a.id < b.id;

Tem dois detalhes pra prestar atenção. Primeiro, a.price = b.price é a condição de fato que faz o casamento. Segundo, a.id < b.id é o que impede a query de devolver cada par duas vezes (uma como (Caneca, Caderno) e outra como (Caderno, Caneca)) e também evita que cada linha seja pareada com ela mesma. Esse truque do < vale guardar — ele aparece sempre que você precisa listar pares.

Subindo dois níveis na hierarquia

Um self join resolve um nível da hierarquia. Quer descobrir o "chefe do chefe" de cada funcionário? É só fazer o join três vezes:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name      AS employee,
    m.name      AS manager,
    gm.name     AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m  ON e.manager_id = m.id
LEFT JOIN employees AS gm ON m.manager_id = gm.id
ORDER BY e.id;

Cada novo alias representa mais um nível subindo na árvore. Funciona bem para dois ou três níveis, mas desanda rápido — você precisaria saber a profundidade da hierarquia na hora de escrever a query e adicionar um join para cada nível. É justamente esse muro que as CTEs recursivas vieram derrubar.

Quando não usar self join

O self join é a ferramenta certa quando você precisa das colunas dos dois lados da relação no resultado. Se a ideia é só filtrar — por exemplo, listar todos os funcionários cujo gerente é a Ada — uma subquery costuma ficar mais legível:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT name
FROM employees
WHERE manager_id = (SELECT id FROM employees WHERE name = 'Ada');

Sem ginástica de alias, e a intenção fica clara. A regra prática: você precisa de dados das duas linhas no resultado? Self join. Só precisa de um valor para comparar? Subquery.

Para hierarquias de profundidade arbitrária (organogramas, árvores de arquivos, comentários aninhados), nenhum dos dois padrões escala bem. Aí entra o território das CTEs recursivas.

A seguir: Subqueries

Self joins e subqueries resolvem problemas parecidos, e saber qual usar em cada situação evita muita dor de cabeça depois ao revisar o SQL. A próxima página mergulha fundo em subqueries — escalares, correlacionadas e com IN — e onde cada forma se destaca.

Perguntas frequentes

O que é um SELF JOIN no SQLite?

É um JOIN comum em que uma tabela é unida com ela mesma. Você atribui dois aliases diferentes à mesma tabela para que o SQLite a trate como duas fontes de linhas distintas, e então relaciona as linhas por uma coluna que conecta um registro a outro — geralmente um vínculo do tipo pai/filho, como funcionário e gerente.

Por que preciso de aliases em um SELF JOIN?

Sem aliases, o SQLite não tem como saber a qual cópia da tabela você está se referindo ao usar o nome de uma coluna. Dando um alias para cada instância (por exemplo, e para o funcionário e m para o gerente), você consegue escrever e.manager_id = m.id sem ambiguidade. Os aliases não são opcionais aqui — sem eles, a query nem sequer é interpretada.

Quando usar SELF JOIN em vez de subquery?