O que é um JOIN

Na aula Tabelas que se Relacionam, a Ana descobriu quem fez o pedido 7 em duas etapas: olhou o cliente_id na tabela de pedidos e depois procurou esse número na tabela de clientes. Para um pedido, tudo bem. Para os 90 pedidos da Loja Aurora, seriam 90 buscas. O JOIN (em inglês, "juntar", "unir") faz essa ponte sozinho: numa consulta só, ele junta cada linha de uma tabela com a linha certa da outra. É o comando que mais se usa em SQL depois do próprio SELECT.

Você vai aprender

Como escrever o primeiro JOIN: cada pedido ao lado do nome do cliente.
Como o banco pensa um JOIN: forma todos os pares de linhas e guarda só os que a condição ON aprova.
Por que se escreve tabela.coluna, e como os apelidos de tabela (p, c) encurtam a consulta.
Os quatro tipos de JOIN (INNER, LEFT, RIGHT, FULL) em diagramas, com o que cada um faz com os clientes que nunca compraram.
A forma antiga, com vírgula e WHERE, que você ainda vai encontrar por aí.
Os erros mais comuns, com as mensagens reais: ambiguous column name, o JOIN sem ON e o ON nas colunas erradas.

Os exemplos rodam de verdade no seu navegador, no banco da Loja Aurora; o quadro "Resultado" é o SQLite respondendo.

A ideia: grampear as fichas

Pense em duas pilhas de papel: as fichas dos pedidos e as fichas dos clientes. A Ana pega o pedido 1, lê "cliente 1", procura a ficha do cliente 1 e grampeia as duas juntas. Depois o pedido 2, "cliente 2", grampeia. E assim por diante, até o último pedido. No fim, ela tem 90 conjuntos grampeados: cada um com os dados do pedido e os dados do cliente, lado a lado.

O JOIN é esse grampeador. Você diz quais pilhas juntar (as tabelas) e qual regra decide quais fichas combinam (a condição ON, "quando"). O resultado é uma tabela nova, só para essa consulta, com colunas das duas tabelas.

1. O primeiro JOIN

Os 8 primeiros pedidos, cada um com o nome de quem comprou:

SELECT pedidos.id, pedidos.data, clientes.nome
FROM pedidos
JOIN clientes ON pedidos.cliente_id = clientes.id
ORDER BY pedidos.id
LIMIT 8;

SELECT pedidos.id, pedidos.data, clientes.nome: As colunas do resultado, vindas das duas tabelas. O pedidos. e o clientes. na frente dizem de qual tabela é cada coluna.
FROM pedidos: A primeira tabela, a da esquerda.
JOIN clientes: A segunda tabela, a da direita, que vai ser "grampeada" na primeira.
ON pedidos.cliente_id = clientes.id: A regra do grampo: uma linha de pedido combina com uma linha de cliente quando o cliente_id do pedido é igual ao id do cliente. É a ligação da chave estrangeira com a chave primária, escrita como condição.
ORDER BY pedidos.id LIMIT 8: Só para o exemplo ficar curto: os 8 primeiros pedidos (aulas ORDER BY e LIMIT).

Repare na linha do pedido 7: lá está o Luís Otávio Brito, a mesma resposta que deu trabalho na aula passada, agora sem copiar número nenhum. Sem o LIMIT, saem as 90 linhas, uma para cada pedido:

SELECT COUNT(*)
FROM pedidos
JOIN clientes ON pedidos.cliente_id = clientes.id;

Experimente: o gerente quer saber também de que cidade é cada cliente. Acrescente a coluna clientes.cidade no fim do SELECT. O ON não muda: a ligação continua sendo a mesma.

SELECT pedidos.id, pedidos.data, clientes.nome
FROM pedidos
JOIN clientes ON pedidos.cliente_id = clientes.id
ORDER BY pedidos.id
LIMIT 8;

SELECT pedidos.id, pedidos.data, clientes.nome, clientes.cidade
FROM pedidos
JOIN clientes ON pedidos.cliente_id = clientes.id
ORDER BY pedidos.id
LIMIT 8;

2. Como o banco pensa: todos os pares, depois o filtro

Por dentro, um JOIN funciona (pelo menos na teoria) em dois passos. Para ver os dois, a Ana pegou só os clientes 1 e 2 e só os pedidos 1, 2 e 27. Primeiro passo: todos os pares possíveis, cada cliente com cada pedido. Esse "junta tudo com tudo" tem nome, CROSS JOIN ("junção cruzada"):

SELECT c.id, c.nome, p.id AS pedido, p.cliente_id
FROM clientes AS c
CROSS JOIN pedidos AS p
WHERE c.id IN (1, 2)
  AND p.id IN (1, 2, 27);

(As letras c e p são apelidos das tabelas; a seção 3 explica.) Dois clientes vezes três pedidos dá 6 pares. Alguns fazem sentido, outros não: o par "Maria Clara Mendes + pedido 1" junta a Maria com um pedido que é do José. Segundo passo: o ON passa por cada par e só deixa ficar os que obedecem à regra:

SELECT c.id, c.nome, p.id AS pedido, p.cliente_id
FROM clientes AS c
JOIN pedidos AS p ON p.cliente_id = c.id
WHERE c.id IN (1, 2)
  AND p.id IN (1, 2, 27);

Dos 6 pares, sobraram 3: os que têm cliente_id igual ao id do cliente. O José ficou com os pedidos 1 e 27, a Maria com o pedido 2. Na Loja Aurora inteira, os pares possíveis entre 30 clientes e 90 pedidos são 30 × 90 = 2.700; o ON deixa só 90.

O banco não monta mesmo os 2.700 pares

"Todos os pares, depois o filtro" é a definição do resultado, o jeito certo de pensar. Na prática, o SQLite é esperto: para cada pedido, ele vai direto ao cliente certo pela chave primária, como você iria direto à ficha 7 no arquivo. O resultado é o mesmo; só chega muito mais rápido. Como ele faz isso é assunto da aula EXPLAIN QUERY PLAN.

3. tabela.coluna e os apelidos de tabela

As duas tabelas têm uma coluna id. Se a consulta disser só id, o banco não sabe se é o id do pedido ou o do cliente. Por isso, num JOIN, escreve-se tabela.coluna: pedidos.id é o número do pedido, clientes.id é o número do cliente.

Escrever o nome da tabela toda vez cansa. Assim como uma coluna pode ganhar apelido com AS (aula Apelidos (AS)), uma tabela também pode:

SELECT p.id, p.data, c.nome
FROM pedidos AS p
JOIN clientes AS c ON p.cliente_id = c.id
WHERE p.id <= 3;

FROM pedidos AS p: Dentro desta consulta, a tabela pedidos passa a se chamar p.
JOIN clientes AS c: E clientes passa a se chamar c.
p.id, p.data, c.nome, p.cliente_id = c.id: Em todo o resto da consulta, usa-se o apelido. O apelido vale só nesta consulta; a tabela continua se chamando pedidos no banco.

O AS é opcional: FROM pedidos p JOIN clientes c funciona igual, e é assim que você vai ver na maioria dos códigos. O costume é usar a primeira letra (ou as primeiras) do nome da tabela. Este guia, daqui em diante, usa apelidos em quase todo JOIN.

E se eu pedir `SELECT *`?

O asterisco traz todas as colunas das duas tabelas, na ordem: primeiro as de pedidos, depois as de clientes.

SELECT *
FROM pedidos
JOIN clientes ON pedidos.cliente_id = clientes.id
WHERE pedidos.id = 1;

São 13 colunas, e duas delas se chamam id: a primeira é o id do pedido, a sétima é o do cliente. Para olhar rapidamente, serve; numa consulta de verdade, escolha as colunas que você quer, cada uma com a tabela na frente.

4. Os tipos de JOIN

O JOIN que você usou até aqui guarda só os pares que combinam. Mas e os clientes 27, 28, 29 e 30, que nunca fizeram pedido? Eles não combinam com nenhum pedido, então simplesmente somem do resultado. Às vezes é isso que você quer; às vezes você quer justamente vê-los. Por isso existem quatro tipos de JOIN. Nos diagramas, o círculo da esquerda é a tabela do FROM, o da direita é a do JOIN, e a parte pintada é o que entra no resultado:

INNER JOIN
só os pares que combinam

LEFT JOIN
tudo de A, mesmo sem par em B

RIGHT JOIN
tudo de B, mesmo sem par em A

FULL JOIN
tudo de A e tudo de B

O que acontece com quem fica "sem par"? A linha entra no resultado mesmo assim, e as colunas da outra tabela vêm vazias: NULL. Veja a diferença entre os dois tipos mais usados, com os clientes 25 a 30. Primeiro o JOIN comum (que é o INNER JOIN):

SELECT c.id, c.nome, p.id AS pedido
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
WHERE c.id >= 25
ORDER BY c.id, p.id;

Seis linhas: os 3 pedidos do Otávio e os 3 da Mirela. Os clientes 27 a 30 sumiram, porque não têm par. Agora trocando só JOIN por LEFT JOIN:

SELECT c.id, c.nome, p.id AS pedido
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id
WHERE c.id >= 25
ORDER BY c.id, p.id;

Dez linhas: as mesmas 6, mais uma para cada cliente sem pedido, com pedido em NULL. O LEFT JOIN garante que todo cliente (a tabela da esquerda) aparece pelo menos uma vez. Na loja inteira:

Tipo	Com `clientes c … pedidos p`	Linhas	Aula
`INNER JOIN` (ou só `JOIN`)	só os pedidos com o seu cliente	90	INNER JOIN
`LEFT JOIN`	os 90 pares + os 4 clientes sem pedido	94	LEFT JOIN
`RIGHT JOIN`	todos os pedidos; como todo pedido tem cliente, ninguém sobra	90	RIGHT JOIN
`FULL JOIN`	todo mundo dos dois lados	94	FULL JOIN
`CROSS JOIN`	todos os pares, sem condição	2.700	(seção 2)

O diagrama é um resumo, não a regra

Os círculos ajudam a lembrar quem entra no resultado, mas não contam a história toda. Um JOIN não "sobrepõe" duas listas; ele forma pares. Por isso o José, com 4 pedidos, aparece em 4 linhas, e nenhum círculo mostra isso. Quando estiver em dúvida, volte à seção 2: todos os pares, depois o filtro do ON, e, nos JOINs com LEFT, RIGHT ou FULL, as linhas sem par voltando com NULL do outro lado.

Formas diferentes de escrever o mesmo JOIN

As três consultas abaixo dão exatamente o mesmo resultado:

SELECT p.id, c.nome
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
WHERE p.id <= 3;

SELECT p.id, c.nome
FROM pedidos p
INNER JOIN clientes c ON p.cliente_id = c.id
WHERE p.id <= 3;

SELECT p.id, c.nome
FROM pedidos p, clientes c
WHERE p.cliente_id = c.id
  AND p.id <= 3;

A terceira é a forma antiga: as tabelas separadas por vírgula no FROM (o que dá todos os pares, como o CROSS JOIN) e a condição da ligação misturada com os outros filtros no WHERE. Ela vem dos primeiros tempos do SQL, e você vai encontrá-la em sistemas antigos e em muitas respostas na internet. Funciona, mas tem dois problemas: a ligação fica perdida no meio dos filtros, e, se você esquecer a condição, não há erro nenhum, só milhares de pares a mais.

Forma	Quando usar
`JOIN … ON`	Use esta. Curta, e a ligação fica no `ON`, separada dos filtros do `WHERE`.
`INNER JOIN … ON`	Igual à de cima; o `INNER` só deixa explícito o tipo. Muita gente prefere escrever, para não confundir com o `LEFT JOIN`.
`FROM a, b WHERE …`	Saiba ler, mas não escreva código novo assim.
`CROSS JOIN`	Só quando você quer mesmo todas as combinações (por exemplo, cada tamanho de camiseta com cada cor).

Quando dá errado

ambiguous column name: id

A Ana escreveu as colunas sem dizer de qual tabela eram:

SELECT id, nome, data
FROM pedidos
JOIN clientes ON pedidos.cliente_id = clientes.id;

A mensagem: ambiguous column name: id, "nome de coluna ambíguo: id". As duas tabelas têm uma coluna id, e o SQLite não adivinha qual você quer. Repare que ele não reclamou de nome nem de data: só clientes tem nome, só pedidos tem data, então não há dúvida. Mas não conte com isso: se um dia alguém criar uma coluna data em clientes, a consulta que funcionava passa a dar erro.

Como consertar: ponha a tabela (ou o apelido) na frente de toda coluna num JOIN: SELECT pedidos.id, clientes.nome, pedidos.data.

Missão: conserte a consulta para ela parar de dar ambiguous column name. A coluna id deve ser o número do pedido.

SELECT id, nome, data
FROM pedidos
JOIN clientes ON pedidos.cliente_id = clientes.id;

SELECT pedidos.id, clientes.nome, pedidos.data
FROM pedidos
JOIN clientes ON pedidos.cliente_id = clientes.id;

Esqueceu o ON: 2.700 linhas

SELECT COUNT(*)
FROM pedidos
JOIN clientes;

O que acontece: nenhum erro. No SQLite, um JOIN sem ON vira um CROSS JOIN: todos os pares, 90 × 30 = 2.700 linhas, cada pedido grudado em cada cliente. Sem o COUNT(*), você veria o pedido 1 aparecer 30 vezes, uma com cada cliente.

Como perceber: desconfie quando um JOIN devolve muito mais linhas do que a maior das tabelas. Um JOIN de pedidos com o seu cliente tem de dar no máximo uma linha por pedido.

Como consertar: acrescente a ligação: JOIN clientes ON pedidos.cliente_id = clientes.id.

O ON ligando as colunas erradas

Esse é o erro mais traiçoeiro, porque o resultado parece certo. A Ana ligou o id do pedido com o id do cliente:

SELECT p.id, c.nome
FROM pedidos p
JOIN clientes c ON p.id = c.id
WHERE p.id IN (27, 28, 29, 30);

O que acontece: o resultado diz que o pedido 27 é do Paulo Sérgio Andrade. Mas o Paulo é um dos clientes que nunca fizeram pedido! A consulta juntou o pedido 27 com o cliente 27 só porque os números são iguais, e isso não quer dizer nada. Veja os donos de verdade, ligando pela chave estrangeira:

SELECT p.id, p.cliente_id, c.nome
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
WHERE p.id IN (27, 28, 29, 30);

Como evitar: o ON liga a chave estrangeira de uma tabela com a chave primária da outra: pedidos.cliente_id = clientes.id. Se o ON tem id = id dos dois lados, quase sempre está errado.

Usou o nome da tabela depois de dar o apelido

SELECT p.id, clientes.nome
FROM pedidos AS p
JOIN clientes AS c ON p.cliente_id = c.id;

A mensagem: no such column: clientes.nome, "não existe a coluna clientes.nome". A coluna existe, sim; o problema é que, depois de clientes AS c, a tabela só atende pelo apelido dentro desta consulta.

Como consertar: use o apelido em todo lugar: c.nome.

Errou o nome da tabela no ON

SELECT pedidos.id, clientes.nome
FROM pedidos
JOIN clientes ON pedidos.cliente_id = cliente.id;

A mensagem: no such column: cliente.id. A tabela se chama clientes, no plural; cliente é só o começo do nome da coluna cliente_id. É uma confusão comum.

Como consertar: ON pedidos.cliente_id = clientes.id, ou use apelidos curtos e não erre mais o nome.

O ON depois do WHERE

SELECT pedidos.id, clientes.nome
FROM pedidos
JOIN clientes
WHERE pedidos.id = 1
ON pedidos.cliente_id = clientes.id;

A mensagem: near "ON": syntax error. O ON faz parte do JOIN e vem colado nele, antes do WHERE.

Como consertar: JOIN clientes ON pedidos.cliente_id = clientes.id e só depois WHERE pedidos.id = 1.

Nos outros bancos

JOIN sem ON. O MySQL faz como o SQLite: na gramática dele, a condição é opcional no JOIN comum, que vira um CROSS JOIN. Já na gramática do PostgreSQL e do SQL Server, o JOIN comum sempre leva uma condição (ON, e no PostgreSQL também USING ou NATURAL): lá, o esquecimento dá erro de sintaxe em vez de 2.700 linhas.
Apelidos. FROM pedidos AS p e FROM pedidos p funcionam no MySQL, no PostgreSQL e no SQL Server.
Os quatro tipos. PostgreSQL e SQL Server têm INNER, LEFT, RIGHT e FULL. O MySQL não tem FULL JOIN; a aula FULL JOIN mostra como imitá-lo. O SQLite tem os quatro desde a versão 3.39.0, de 2022.

Referência rápida

Escrita	O que faz
`FROM a JOIN b ON a.x = b.y`	Junta cada linha de `a` com as linhas de `b` que obedecem à condição
`INNER JOIN`	O mesmo que `JOIN`: só os pares que combinam
`LEFT JOIN` / `RIGHT JOIN` / `FULL JOIN`	Também guardam as linhas sem par (de um lado, do outro ou dos dois), com `NULL` no lado que faltou
`CROSS JOIN`	Todos os pares, sem condição (linhas de `a` × linhas de `b`)
`tabela.coluna`	Diz de qual tabela é a coluna; obrigatório quando o nome existe nas duas
`FROM pedidos AS p` ou `FROM pedidos p`	Apelido de tabela, válido só nesta consulta
`FROM a, b WHERE a.x = b.y`	Forma antiga do `INNER JOIN`

Mensagem do SQLite	Quer dizer
`ambiguous column name: id`	A coluna existe nas duas tabelas; ponha a tabela ou o apelido na frente
`no such column: clientes.nome`	Você deu apelido à tabela e depois usou o nome dela (ou errou o nome)
`near "ON": syntax error`	O `ON` está fora do lugar: ele vem logo depois do `JOIN tabela`
(nenhuma, mas linhas demais)	Faltou o `ON`: virou `CROSS JOIN`
(nenhuma, mas pares absurdos)	O `ON` liga as colunas erradas (por exemplo, `p.id = c.id`)

Exercícios

1. Complete o JOIN

Complete para juntar cada pedido com o seu cliente:

SELECT p.id, c.nome FROM pedidos p clientes c p.cliente_id = c.id;

2. Quantos pares?

A tabela fornecedores tem 6 linhas e categorias tem 8. Quantas linhas devolve SELECT * FROM fornecedores CROSS JOIN categorias;?

Linhas:

3. A consulta SELECT id FROM pedidos JOIN clientes ON pedidos.cliente_id = clientes.id; dá ambiguous column name: id. Por quê?

Porque falta o AS nas tabelas Porque as duas tabelas têm uma coluna chamada id, e o SQLite não sabe qual mostrar Porque a coluna id não existe Porque o ON está escrito ao contrário

4. Num JOIN comum entre clientes e pedidos, o que acontece com os clientes que nunca fizeram pedido?

Aparecem com o pedido 0 Aparecem uma vez, com NULL nas colunas do pedido A consulta dá erro Não aparecem: só entram os pares que combinam

5. A Ana escreveu FROM pedidos p JOIN clientes c ON p.id = c.id. O que há de errado?

Nada: o resultado está certo Dá erro de sintaxe Junta cada pedido com o cliente de mesmo número, que não tem nada a ver com o dono do pedido; o certo é p.cliente_id = c.id Os apelidos p e c não são permitidos

Missão final: cada produto com a sua categoria

Missão: a lista de produtos mostra só o número da categoria. Faça um JOIN com a tabela categorias para mostrar, de cada um dos 45 produtos, o nome do produto (coluna produto) e o nome da categoria (coluna categoria). As duas tabelas têm uma coluna nome, então use apelidos e AS para dar os nomes certos às colunas.

SELECT nome, categoria_id
FROM produtos;

SELECT p.nome AS produto, c.nome AS categoria
FROM produtos p
JOIN categorias c ON p.categoria_id = c.id;

Esse é um INNER JOIN, o tipo mais usado de todos. Na próxima aula, INNER JOIN, você vai juntar três tabelas de uma vez, somar o que cada cliente gastou e conhecer os atalhos USING e NATURAL JOIN (e por que o segundo é perigoso).

Resumo

O JOIN junta as linhas de duas tabelas numa consulta só: FROM pedidos JOIN clientes ON pedidos.cliente_id = clientes.id.
Pense em dois passos: todos os pares de linhas (o CROSS JOIN) e depois o filtro do ON, que deixa só os pares que combinam.
O ON liga a chave estrangeira de uma tabela com a chave primária da outra. id = id dos dois lados quase sempre é erro.
Num JOIN, escreva tabela.coluna, ou use apelidos de tabela (pedidos p, clientes c). Coluna sem tabela que existe nos dois lados dá ambiguous column name.
Há quatro tipos: INNER (só os pares), LEFT (tudo da esquerda), RIGHT (tudo da direita) e FULL (tudo dos dois). Quem fica sem par entra com NULL do outro lado.
No SQLite, JOIN sem ON não dá erro: vira CROSS JOIN, com linhas demais.