Dois JOINs na mesma tabela em SQL: apelido, chave certa e o LEFT JOIN que salva linhas

O relatório de vendas precisa mostrar, em cada pedido, o nome do cliente e o nome do vendedor. Os dois estão na mesma tabela pessoas. Você escreve dois JOIN pessoas e o MySQL responde ERROR 1066 (42000): Not unique table/alias: 'pessoas'. Coloca apelidos, e vem o próximo: Column 'nome' in field list is ambiguous. A resposta curta: dê um apelido diferente a cada cópia da tabela, qualifique toda coluna com o apelido e ligue cada cópia pela sua própria chave, uma por cliente_id e a outra por vendedor_id. Se o vendedor pode faltar, a cópia dele entra com LEFT JOIN, senão o pedido some do relatório.

Não é defeito de modelagem. Ter cliente e vendedor na mesma tabela de pessoas é comum e correto. O que falta é dizer ao banco qual papel cada cópia representa.

Por que o banco não sabe qual "nome" você quer

Num JOIN, cada tabela citada vira uma fonte de colunas. Quando a mesma tabela aparece duas vezes, o banco precisa de um nome para cada aparição, e o nome da tabela não serve mais, porque é igual nas duas. Daí o primeiro erro. Depois de você dar apelidos, existem duas colunas nome disponíveis, uma em cada cópia, e um nome solto no SELECT não diz qual delas. Daí o segundo.

Pense em cada apelido como um papel. A tabela física é uma só, mas na consulta ela aparece como "comprador" e como "vendedor". Cada papel tem a sua condição de ligação e as suas colunas. Com essa leitura, a consulta deixa de ser confusa.

O exemplo: pedidos de uma loja de material de construção

CREATE TABLE pessoas (
  id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(120) NOT NULL
);

CREATE TABLE pedidos (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  cliente_id  INT UNSIGNED NOT NULL,
  vendedor_id INT UNSIGNED NULL,
  total       DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (cliente_id)  REFERENCES pessoas(id),
  FOREIGN KEY (vendedor_id) REFERENCES pessoas(id)
);

INSERT INTO pessoas (id, nome) VALUES
  (1, 'Ana Ribeiro'), (2, 'Bruno Lima'), (3, 'Carla Souza'), (4, 'Diego Alves');

INSERT INTO pedidos (id, cliente_id, vendedor_id, total) VALUES
  (101, 1, 3, 350.00),
  (102, 2, 4, 89.90),
  (103, 1, NULL, 1200.00);

O pedido 103 foi feito pelo site, sem vendedor. Ele é o caso que separa uma consulta certa de uma que parece certa.

Como escrever a consulta, passo a passo

1. Dê à tabela principal um apelido curto, como p para pedidos.
2. Junte pessoas uma vez com o apelido cli, ligando cli.id = p.cliente_id.
3. Junte pessoas de novo com o apelido ven, ligando ven.id = p.vendedor_id.
4. Decida para cada papel se ele é obrigatório. Cliente sempre existe: JOIN. Vendedor pode faltar: LEFT JOIN.
5. Qualifique toda coluna do SELECT e dê nomes de saída distintos com AS.
6. Compare a contagem de linhas com a contagem da tabela principal.

SELECT
  p.id,
  cli.nome AS cliente,
  COALESCE(ven.nome, '(site)') AS vendedor,
  p.total
FROM pedidos p
JOIN pessoas cli      ON cli.id = p.cliente_id
LEFT JOIN pessoas ven ON ven.id = p.vendedor_id
ORDER BY p.id;

+-----+-------------+-------------+---------+
| id  | cliente     | vendedor    | total   |
+-----+-------------+-------------+---------+
| 101 | Ana Ribeiro | Carla Souza |  350.00 |
| 102 | Bruno Lima  | Diego Alves |   89.90 |
| 103 | Ana Ribeiro | (site)      | 1200.00 |
+-----+-------------+-------------+---------+

Três pedidos na tabela, três linhas no resultado. O COALESCE troca o nulo do pedido sem vendedor por um texto legível, sem esconder que ele não tem vendedor.

Por que os nomes de saída importam na aplicação

Mesmo com as colunas qualificadas, se o SELECT trouxer cli.nome, ven.nome sem AS, as duas colunas chegam à aplicação com o mesmo nome, nome. Ao buscar o resultado como array associativo ou objeto, uma sobrescreve a outra, e o relatório mostra o vendedor no lugar do cliente sem erro nenhum. Os apelidos de coluna, cliente e vendedor, evitam isso. É também um bom motivo para listar as colunas em vez de usar SELECT *, como no post sobre SELECT sem estrela.

Os dois erros que devolvem dado errado sem reclamar

Ligar as duas cópias pela mesma chave

-- ERRADO: as duas cópias ligadas pelo cliente
JOIN pessoas cli ON cli.id = p.cliente_id
JOIN pessoas ven ON ven.id = p.cliente_id

A consulta roda e devolve três linhas, com o cliente aparecendo também como vendedor. Num teste rápido, onde a mesma pessoa às vezes ocupa os dois papéis, ninguém percebe. O sintoma é sutil: a coluna de vendedor sempre igual à de cliente. Leia cada ON em voz alta: "o vendedor é a pessoa cujo id é o vendedor_id do pedido".

Usar JOIN interno no papel opcional

Com JOIN pessoas ven no lugar do LEFT JOIN, o pedido 103 desaparece, porque não existe pessoa com id nulo. O relatório mostra dois pedidos, o total vendido cai 1.200 reais e ninguém recebe aviso. A forma de pegar isso é contar:

SELECT COUNT(*) FROM pedidos;               -- 3
SELECT COUNT(*) FROM pedidos p
JOIN pessoas cli ON cli.id = p.cliente_id
JOIN pessoas ven ON ven.id = p.vendedor_id; -- 2: perdeu linha

Se a contagem com os joins é menor que a da tabela principal, algum join interno comeu registro. Se é maior, algum join está multiplicando linhas, e a condição precisa ser revista. Num relatório de pedidos ligado a pessoas por chave primária, a contagem deve ser exatamente igual.

Desempenho: índices nas duas chaves

Cada JOIN procura a pessoa pelo id, que é chave primária e já tem índice. No sentido contrário, como "todos os pedidos da vendedora Carla", o banco precisa de índice em vendedor_id. No InnoDB, as chaves estrangeiras declaradas acima já criam índice nas colunas cliente_id e vendedor_id se ele não existir. Sem chave estrangeira declarada, crie os índices você mesmo. Em relatórios grandes, limite o período e pagine, como no guia de consulta SQL com limite.

Erros comuns

• Juntar a mesma tabela duas vezes sem apelido. O banco recusa a consulta com erro de tabela ou apelido duplicado.
• Coluna sem qualificar no SELECT ou no WHERE. O banco recusa com erro de coluna ambígua, e quem corrige às pressas pode qualificar com o apelido do papel errado.
• As duas cópias ligadas pela mesma chave. O cliente aparece como vendedor e a consulta "funciona" com dado errado.
• JOIN interno no papel que pode ser nulo. Registros somem do relatório sem aviso, e totais ficam menores.
• Filtrar o lado do LEFT JOIN no WHERE. Uma condição como WHERE ven.nome LIKE 'C%' descarta as linhas com vendedor nulo e transforma o LEFT JOIN em interno; condições desse lado vão no ON quando a intenção é manter as linhas.
• Duas colunas com o mesmo nome de saída. A aplicação sobrescreve uma com a outra e mostra o nome errado.

Checklist

• Cada cópia da tabela tem um apelido que diz o papel.
• Toda coluna da consulta está qualificada pelo apelido.
• Cada ON liga o papel à sua própria chave estrangeira.
• Papéis que podem faltar usam LEFT JOIN.
• Colunas de mesmo nome saem com AS distintos.
• A contagem do resultado bate com a da tabela principal.
• Existe um registro de teste com o papel opcional vazio.

Pegue o relatório que junta a mesma tabela duas vezes no seu sistema, cadastre um registro com o lado opcional vazio e rode de novo. Ele continua na lista, e qual dos lados é obrigatório de verdade?