SQL INNER JOIN

Na aula O que é um JOIN, a Ana juntou cada pedido com o seu cliente. Aquele JOIN tem nome completo: INNER JOIN, a "junção interna", que guarda só os pares que combinam. É o tipo de JOIN mais usado no dia a dia. Nesta aula a Ana vai mais longe: junta três e quatro tabelas para saber quem comprou o quê e de quem, soma quanto cada cliente gastou e conhece dois atalhos, o USING e o NATURAL JOIN. Um deles é útil; o outro parece uma mão na roda e esconde uma armadilha.

Os exemplos rodam de verdade no seu navegador, no banco da Loja Aurora.

A ideia: o encontro dos dois lados

Pense numa festa em que cada convidado chega com uma pulseira numerada, e cada mesa tem um número. Para o fotógrafo, "INNER JOIN" é fotografar cada pessoa na mesa do seu número. Quem não tem mesa não sai em foto nenhuma. Mesa vazia também não. Só entram na foto os encontros que aconteceram.

Na Loja Aurora: um INNER JOIN entre clientes e pedidos mostra cada pedido com o seu cliente. Os 4 clientes que nunca compraram (27 a 30) ficam de fora, porque não têm par.

1. INNER JOIN com filtro

Os pedidos que ainda não foram entregues, com o nome do cliente para a Ana poder ligar para ele:

SELECT p.id, p.data, c.nome
FROM pedidos p
INNER JOIN clientes c ON p.cliente_id = c.id
WHERE p.entregue_em IS NULL;

FROM pedidos p

A tabela da esquerda, com o apelido p.

INNER JOIN clientes c

Inner join = "junção interna": junta clientes (apelido c) guardando só os pares que combinam. Escrever só JOIN dá no mesmo.

ON p.cliente_id = c.id

A regra do par: a chave estrangeira do pedido igual à chave primária do cliente.

WHERE p.entregue_em IS NULL

Depois de formar os pares, fica só quem ainda não tem data de entrega (aula IS NULL).

Cinco pedidos pendentes, do 86 ao 90. A ordem das partes é a de sempre: SELECT, FROM com os seus JOINs, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Os JOINs fazem parte do FROM: eles dizem de onde vêm as linhas.

2. Três tabelas: quem comprou e quem vendeu

O pedido tem duas chaves estrangeiras: cliente_id e funcionario_id. Para mostrar o nome do cliente e o nome do vendedor, a Ana encadeia um segundo JOIN:

SELECT p.id, c.nome AS cliente, f.nome AS vendedor
FROM pedidos p
JOIN clientes c     ON p.cliente_id = c.id
JOIN funcionarios f ON p.funcionario_id = f.id
WHERE p.entregue_em IS NULL;

FROM pedidos p

O ponto de partida: os pedidos.

JOIN clientes c ON p.cliente_id = c.id

Primeira junção: cada pedido ganha as colunas do seu cliente.

JOIN funcionarios f ON p.funcionario_id = f.id

Segunda junção: o resultado da primeira ganha as colunas do funcionário. Cada JOIN leva o seu próprio ON.

c.nome AS cliente, f.nome AS vendedor

As duas tabelas têm uma coluna nome. Os apelidos de coluna deixam claro qual é qual no resultado.

O SQLite processa os JOINs da esquerda para a direita: primeiro junta pedidos com clientes, depois junta esse resultado com funcionarios. Com INNER JOIN, a ordem em que você escreve as tabelas não muda quais linhas saem (a ordem delas no resultado, só o ORDER BY garante); escolha a que for mais fácil de ler. Um bom costume é começar pela tabela "do meio", a que tem as chaves estrangeiras (aqui, pedidos), e ir pendurando as outras nela.

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

SELECT p.id AS pedido, c.nome AS cliente, f.nome AS vendedor
FROM pedidos p
JOIN clientes c     ON p.cliente_id = c.id
JOIN funcionarios f ON p.funcionario_id = f.id;

3. Quatro tabelas: o que cada um comprou

Os produtos de um pedido ficam em itens_pedido, a tabela do meio da relação muitos-para-muitos (aula Tabelas que se Relacionam). Para ler o pedido 1 "em português", com o nome do cliente e o nome de cada produto, são quatro tabelas:

SELECT p.id AS pedido, c.nome AS cliente,
       pr.nome AS produto, i.quantidade
FROM itens_pedido i
JOIN pedidos p   ON i.pedido_id = p.id
JOIN clientes c  ON p.cliente_id = c.id
JOIN produtos pr ON i.produto_id = pr.id
WHERE p.id = 1;

Siga o caminho no mapa do banco: o item aponta para o pedido, o pedido aponta para o cliente, e o item também aponta para o produto. Cada seta do mapa vira um JOIN … ON. Repare no apelido pr para produtos: o p já estava em uso por pedidos, e dois apelidos iguais na mesma consulta não podem.

4. JOIN com GROUP BY: somando por nome

Na aula Agrupando por Mais de uma Coluna, os resultados vinham com números (categoria_id 1, 2…). Com um JOIN, o agrupamento pode mostrar nomes. Quanto cada cliente já gastou em produtos (sem contar o frete)? Os 5 maiores:

SELECT c.nome AS cliente,
       SUM(i.quantidade * i.preco_unitario) AS gasto
FROM clientes c
JOIN pedidos p      ON p.cliente_id = c.id
JOIN itens_pedido i ON i.pedido_id = p.id
GROUP BY c.id, c.nome
ORDER BY gasto DESC
LIMIT 5;

FROM clientes c JOIN pedidos p … JOIN itens_pedido i …

Cada linha do JOIN é um item comprado, com o cliente que o comprou ao lado.

SUM(i.quantidade * i.preco_unitario)

Em cada item, quantidade vezes preço; a soma de todos os itens do grupo (aula SUM()).

GROUP BY c.id, c.nome

Um grupo por cliente. O c.id garante que dois clientes com o mesmo nome não se misturem; o c.nome está lá porque aparece no SELECT (a regra da coluna esquecida, da aula anterior).

A Conceição Prado lidera, com 1.345,73 em produtos. E o faturamento por categoria? Três tabelas de novo, outro caminho no mapa:

SELECT c.nome AS categoria, SUM(i.quantidade) AS unidades
FROM itens_pedido i
JOIN produtos p   ON i.produto_id = p.id
JOIN categorias c ON p.categoria_id = c.id
GROUP BY c.id, c.nome
ORDER BY unidades DESC;

Os eletrônicos venderam 72 unidades; a jardinagem, só 13. (Repare que aqui o apelido p é de produtos: apelidos valem só dentro de cada consulta, e cada consulta escolhe os seus.)

5. O filtro no ON ou no WHERE?

Num INNER JOIN, uma condição extra pode ir no ON ou no WHERE, e o resultado é o mesmo. Os pedidos de clientes de Pernambuco, das duas formas:

SELECT p.id, c.nome
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id AND c.estado = 'PE';

SELECT p.id, c.nome
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
WHERE c.estado = 'PE';

Os mesmos 7 pedidos, da Juliana e do Bruno. Mesmo assim, o costume é: no ON, só a ligação entre as tabelas; no WHERE, os filtros. A consulta fica mais fácil de ler, e você evita uma surpresa: no LEFT JOIN, mudar um filtro do WHERE para o ON muda o resultado. A aula dele mostra por quê.

6. O atalho USING

Quando a coluna da ligação tem o mesmo nome nas duas tabelas, dá para trocar o ON a.x = b.x por USING (x) ("usando x"). Na Loja Aurora, as chaves estrangeiras têm nomes diferentes das primárias (cliente_id × id), então o USING não serve para elas. Mas clientes e fornecedores têm, os dois, a coluna cidade. Quais fornecedores ficam na mesma cidade de cada cliente VIP?

SELECT c.nome AS cliente, cidade, f.nome AS fornecedor
FROM clientes c
JOIN fornecedores f USING (cidade)
WHERE c.vip = 1;

JOIN fornecedores f USING (cidade)

O mesmo que ON c.cidade = f.cidade. Os parênteses são obrigatórios, e dentro deles pode ir mais de uma coluna, separadas por vírgula.

cidade (sem apelido)

Com USING, a coluna da ligação vira uma só, e pode ser citada sem a tabela na frente: não há ambiguidade, porque os dois lados têm o mesmo valor.

O José Ribamar aparece duas vezes porque Teresina tem dois fornecedores. Com SELECT *, o USING também mostra a coluna cidade uma vez só, em vez de duas:

SELECT *
FROM clientes c
JOIN fornecedores f USING (cidade)
WHERE c.id = 1;

Conte as colunas: são 12, e só há uma cidade. (Já id, nome e estado aparecem duas vezes, porque não estão no USING.)

7. NATURAL JOIN: o atalho que dá errado

O NATURAL JOIN ("junção natural") vai além do USING: ele mesmo procura as colunas que têm o mesmo nome nas duas tabelas e liga por todas elas, sem você escrever condição nenhuma. Parece cômodo. Veja o que acontece com pedidos e clientes:

SELECT *
FROM pedidos
NATURAL JOIN clientes
WHERE id IN (27, 28);

O único nome em comum entre pedidos e clientes é id. Então o NATURAL JOIN ligou pedidos.id = clientes.id: o pedido 27 com o cliente 27, o Paulo Sérgio Andrade, que nunca fez pedido! É o mesmo erro do "ON nas colunas erradas" da aula O que é um JOIN, só que agora sem você ter escrito o ON errado. A coluna cliente_id do resultado mostra a verdade: o pedido 27 é do cliente 1. E com outras tabelas é pior:

SELECT COUNT(*) FROM produtos NATURAL JOIN categorias;
SELECT COUNT(*) FROM clientes NATURAL JOIN fornecedores;

Zero e zero. produtos e categorias têm em comum id e nome; o NATURAL JOIN exigiu que um produto tivesse o mesmo número e o mesmo nome de uma categoria, e nenhum tem. Clientes e fornecedores têm em comum id, nome, cidade e estado.

O maior perigo é o futuro: uma consulta com NATURAL JOIN que funciona hoje pode passar a dar outro resultado amanhã, se alguém acrescentar a uma das tabelas uma coluna com nome igual a uma da outra (uma data, um status, uma observacao). Ninguém mexeu na consulta, e ela mudou. A documentação do PostgreSQL faz o mesmo alerta: o USING é razoavelmente seguro, porque só junta as colunas que você listou; o NATURAL é bem mais arriscado, porque qualquer coluna nova com nome repetido entra na ligação. Saiba reconhecer o NATURAL JOIN, mas não use.

Formas diferentes de escrever a ligação

Quando dá errado

SELECT p.id, nome
FROM pedidos p
JOIN clientes c     ON p.cliente_id = c.id
JOIN funcionarios f ON p.funcionario_id = f.id;

SELECT *
FROM pedidos
JOIN clientes USING (cliente_id);

SELECT *
FROM clientes
JOIN fornecedores USING cidade;

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

SELECT COUNT(*)
FROM pedidos p
JOIN clientes c ON p.cliente_id = c.id
JOIN funcionarios f;

Referência rápida

Exercícios

Missão final: os pedidos de cada vendedor

SELECT funcionario_id, COUNT(*) AS pedidos
FROM pedidos
GROUP BY funcionario_id;

SELECT f.nome AS vendedor, COUNT(*) AS pedidos
FROM pedidos p
JOIN funcionarios f ON p.funcionario_id = f.id
GROUP BY f.id, f.nome;

Saíram 6 vendedores, com 15 pedidos cada. A loja tem 8 funcionários: a gerente Marta e o estoquista Davi não atendem pedidos, e por isso o INNER JOIN os deixou de fora. Para que eles aparecessem com 0, seria preciso um LEFT JOIN, o assunto da próxima aula.

Resumo

• INNER JOIN (ou só JOIN) guarda só os pares que combinam; quem não tem par fica de fora.
• Para três ou mais tabelas, encadeie JOIN … ON, um por ligação. Com N tabelas, são N − 1 condições ON.
• JOIN com GROUP BY permite somar e contar mostrando nomes. Agrupe pelo id e pelo nome.
• No INNER JOIN, um filtro no ON ou no WHERE dá o mesmo resultado; por costume, a ligação vai no ON e os filtros no WHERE.
• USING (x) é um atalho para ON a.x = b.x quando os nomes são iguais. NATURAL JOIN liga por todos os nomes repetidos: evite.
• Num JOIN, ponha a tabela ou o apelido na frente de toda coluna, para não cair no ambiguous column name.