Tipos de JOIN em SQL Explicados com Exemplos

Um JOIN mal escrito não gera um erro. Ele executa, devolve um resultado, e as linhas estão erradas: clientes faltando, totais inflados, registros órfãos escondidos. Na maioria das vezes a consulta recebeu exatamente o que pediu, e quem a escreveu escolheu o tipo de join errado, ou filtrou no lugar errado.

Um join combina linhas de duas tabelas com base numa condição que você escreve. Quais linhas sobrevivem depende do tipo de join, e os seis tipos abaixo dão seis respostas diferentes a partir das mesmas duas tabelas.

customers tem cinco pessoas. Três delas foram indicadas por outro cliente, registrado em referred_by:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        TEXT NOT NULL,
  referred_by INT REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_id, name, referred_by) VALUES
  (1, 'Alice', NULL),
  (2, 'Bob',   NULL),
  (3, 'Carol', 1),
  (4, 'Dave',  1),
  (5, 'Erin',  2);

orders tem seis pedidos. Cinco apontam para um cliente real. O sexto, 106, aponta para o cliente 99, que foi excluído depois de fazer o pedido, então o pedido não corresponde mais a ninguém:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount      NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (order_id, customer_id, amount) VALUES
  (101, 1, 50.00),
  (102, 1, 20.00),
  (103, 2, 75.00),
  (104, 4, 30.00),
  (105, 4, 15.00),
  (106, 99, 10.00);

A forma mais rápida de acompanhar os exemplos é um container Postgres, configurado como em O Que É Docker Compose e Como Usar. Os joins são SQL padrão. Os dois pontos em que o MySQL se comporta de forma diferente estão indicados mais abaixo.

INNER JOIN: só as linhas que coincidem dos dois lados

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

Cinco linhas. Carol e Erin não têm pedidos, então desaparecem. O pedido 106 não tem cliente, então também desaparece. JOIN e INNER JOIN significam a mesma coisa. INNER é opcional e a maioria das pessoas o omite.

Com cinco linhas você não nota nenhuma lentidão. Numa tabela orders real, orders.customer_id precisa de um índice, ou cada join contra ela percorre a tabela inteira. Como Funcionam os Índices de Banco de Dados explica quando esse índice compensa.

LEFT JOIN: todas as linhas da esquerda, com ou sem correspondência

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

Todo cliente aparece. Carol e Erin recebem NULL nas colunas do pedido. Esse é o join que você vai escrever com mais frequência: “todo X, com o Y que ele tiver”.

RIGHT JOIN: todas as linhas da direita, com ou sem correspondência

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

O pedido 106 volta, com NULL no lugar do nome. Essa é a linha órfã que o inner join escondia, e um right join (ou o left join equivalente) é como você a encontra de propósito.

Você raramente vai ver um RIGHT JOIN em código real. a RIGHT JOIN b devolve as mesmas linhas que b LEFT JOIN a, então a maioria das equipes usa LEFT JOIN em todo lugar e troca a ordem das tabelas. Reescrever um RIGHT JOIN assim é uma questão de consistência. O original não está errado.

FULL OUTER JOIN: tudo dos dois lados

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name NULLS LAST, o.order_id;

Oito linhas: cada correspondência, mais os clientes sem correspondência, mais o pedido sem correspondência. Isso faz dele o join da reconciliação, quando você quer tudo que, de um lado ou do outro, não se encaixa. Fora disso, você raramente vai precisar dele.

O MySQL não tem FULL OUTER JOIN, e também não aceita NULLS LAST. Para obter as mesmas linhas lá, pegue o LEFT JOIN, depois faça um UNION ALL com um RIGHT JOIN filtrado com WHERE c.customer_id IS NULL, para que as linhas já correspondidas não se repitam. Um UNION simples dos dois joins também funciona, mas tem o custo de uma deduplicação e ainda mescla linhas que já eram idênticas.

CROSS JOIN: cada linha combinada com cada linha

1
2
3

SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o;

Cinco clientes vezes seis pedidos: trinta linhas, sem nenhuma condição de join. A maioria dos cross joins em produção são acidentes. Alguém escreveu FROM customers, orders e esqueceu o WHERE que deveria conectá-las. Você ia querer um de propósito para gerar combinações, como cada tamanho com cada cor, ou cada dia de um intervalo de datas com cada loja, antes de preencher os números reais.

Como funciona um self-join?

customers.referred_by aponta para outra linha da mesma tabela. Para mostrar quem indicou quem pelo nome, junte a tabela a ela mesma com um segundo alias:

1
2
3
4

SELECT c.name AS customer, r.name AS referred_by
FROM customers c
LEFT JOIN customers r ON c.referred_by = r.customer_id
ORDER BY c.name;

O join funciona exatamente como antes. customers c e customers r são dois aliases, e o planejador os trata como duas tabelas separadas que por acaso têm as mesmas linhas. É um LEFT JOIN de propósito, para que Alice e Bob, que não foram indicados por ninguém, fiquem no resultado. Qualquer tabela em que uma linha aponta para outra linha da mesma tabela funciona assim: funcionários e gerentes, categorias e categorias pai, comentários e respostas.

Por que uma cláusula WHERE transforma um LEFT JOIN num INNER JOIN?

Você quer todo cliente, com os pedidos acima de 25 $ quando ele tiver algum:

1
2
3
4

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25;

Carol e Erin desapareceram. WHERE executa depois do join, e para elas o.amount é NULL. NULL > 25 não é verdadeiro nem falso. É desconhecido, e WHERE só mantém as linhas que são verdadeiras. O LEFT JOIN as tinha mantido, e o filtro as descartou.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- Mantém todo cliente; só filtra quais dos pedidos contam
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 25;

-- Mantém clientes sem nenhum pedido, mas não clientes cujos pedidos são todos <= 25
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 25 OR o.amount IS NULL;

Use a primeira. Colocar a condição no ON decide quais pedidos têm permissão para corresponder, e todo cliente ainda recebe pelo menos uma linha. A segunda versão só deixa passar as linhas em que o join não encontrou nada. Um cliente cujo único pedido foi de 20 $ recebe uma linha com amount = 20.00, que não atende nenhuma das duas condições, e esse cliente desaparece de novo. Com esses dados, as duas consultas mantêm por acaso os cinco clientes, e é assim que a segunda passa pelos testes sem ser notada.

Por que SUM devolve o total errado depois de juntar duas tabelas?

Adicione uma terceira tabela, payments, em que um pedido pode ser pago em várias parcelas:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE payments (
  payment_id INT PRIMARY KEY,
  order_id   INT NOT NULL,
  amount     NUMERIC(10,2) NOT NULL
);

INSERT INTO payments (payment_id, order_id, amount) VALUES
  (1, 101, 25.00),
  (2, 101, 25.00),
  (3, 103, 75.00);

O pedido 101 é de 50 $, pago em duas parcelas de 25 $. Agora junte as três tabelas e totalize o que cada cliente pediu:

1
2
3
4
5

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY c.name;

O total real de Alice é 70 $ (50.00 + 20.00). Duas coisas deram errado. O pedido 101 coincide com dois pagamentos, então o join devolve essa linha de pedido duas vezes e SUM(o.amount) soma os 50 $ dela duas vezes. O pedido 102 não tem pagamento, então o inner join contra payments o elimina. Para Alice isso dá 50 + 50 + 0 = 100. Dave não tem nenhum pagamento, então não aparece no resultado.

A regra: quando você agrega uma coluna, não junte tabelas de que a agregação não precisa. Qualquer join um-para-muitos pode multiplicar linhas ou removê-las. Se você só quer os totais dos pedidos, deixe payments de fora:

1
2
3
4

SELECT c.name, SUM(o.amount) AS total_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;

Se você precisar também dos totais de pagamento no mesmo relatório, faça primeiro um GROUP BY order_id em payments numa subconsulta ou CTE. Isso dá uma linha por pedido, que você pode juntar aos pedidos com segurança.

NOT IN contra NOT EXISTS: como você encontra linhas sem correspondência?

“Clientes sem pedidos” parece um trabalho para NOT IN:

1
2

SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Isso funciona aqui só porque orders.customer_id é NOT NULL. Remova essa restrição e insira um único pedido com customer_id em NULL, e a consulta devolve zero linhas, sem nenhum erro. NOT IN se expande numa cadeia de comparações <> unidas por AND. x <> NULL é desconhecido, então um único NULL torna a condição inteira desconhecida para todo cliente.

Dois padrões não têm esse problema:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- LEFT JOIN + IS NULL
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- NOT EXISTS
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

As duas devolvem Carol e Erin, e um NULL em orders.customer_id não afeta nenhuma das duas. Nenhuma delas constrói uma lista para comparar. As duas verificam, cliente por cliente, se existe um pedido correspondente. Use NOT EXISTS por padrão. Use LEFT JOIN ... IS NULL quando a consulta já junta as duas tabelas para buscar outras colunas.

Quando um join devolve mais linhas do que você espera, procure uma tabela um-para-muitos entre o join e o GROUP BY. Quando devolve menos, procure uma condição WHERE na tabela que pode ser NULL. Nos dois casos, corrija a consulta em torno do join antes de mudar o tipo de join.

Artigos relacionados

• Como Funcionam os Índices de Banco de Dados
• O Que É Docker Compose e Como Usar
• Como Funciona o Cache Redis e Como Usá-lo