SQL FULL JOIN

O LEFT JOIN guarda tudo da tabela da esquerda; o RIGHT JOIN, tudo da direita. O FULL JOIN (ou FULL OUTER JOIN, a "junção externa completa") guarda tudo dos dois lados: os pares que combinam, as linhas da esquerda que ficaram sozinhas e as da direita que ficaram sozinhas. A Ana vai usá-lo para responder a uma pergunta do gerente da Loja Aurora: em quais cidades a loja tem clientes, em quais tem fornecedores e em quais tem os dois? E vai aprender a fazer o mesmo sem FULL JOIN, porque o MySQL não tem esse JOIN.

Você vai aprender

O que o FULL JOIN devolve: INNER JOIN + quem sobrou da esquerda + quem sobrou da direita.
Um caso real com sobras dos dois lados: as cidades dos clientes e dos fornecedores.
Como ficar só com as linhas sem par, de qualquer um dos lados.
Por que um FULL JOIN entre clientes e pedidos dá o mesmo que um LEFT JOIN.
O FULL JOIN com USING, que junta as duas colunas numa só.
A forma antiga, com LEFT JOIN + UNION ALL, para bancos que não têm FULL JOIN.

Os exemplos rodam de verdade no seu navegador, no banco da Loja Aurora. O FULL JOIN existe no SQLite desde a versão 3.39.0 (de 2022, a mesma que trouxe o RIGHT JOIN); o editor usa a 3.50.4.

A ideia: duas listas de convidados

A Ana e o irmão vão fazer uma festa juntos, e cada um tem a sua lista de convidados. Para montar a lista final, eles põem as duas lado a lado: quem está nas duas listas aparece uma vez, com o nome dos dois lados; quem só está na lista da Ana aparece com o lado do irmão em branco; quem só está na lista do irmão aparece com o lado da Ana em branco. Ninguém de nenhuma das listas fica de fora. Esse é o FULL JOIN.

Parte do resultado	De onde vem
Os pares que combinam	o mesmo que o `INNER JOIN`
+ as linhas da esquerda sem par	o que o `LEFT JOIN` acrescenta (direita em `NULL`)
+ as linhas da direita sem par	o que o `RIGHT JOIN` acrescenta (esquerda em `NULL`)

1. Um caso com sobras dos dois lados: as cidades

Os clientes da Loja Aurora moram em 8 cidades; os fornecedores ficam em 5. Algumas cidades aparecem nas duas listas, outras não. Para ver o mapa completo, a Ana junta as duas tabelas pela cidade, e usa DISTINCT (aula SELECT DISTINCT) para mostrar cada combinação de cidades uma vez só:

SELECT DISTINCT c.cidade AS cidade_cliente,
                f.cidade AS cidade_fornecedor
FROM clientes c
FULL JOIN fornecedores f ON c.cidade = f.cidade;

FROM clientes c: A tabela da esquerda.
FULL JOIN fornecedores f: Full join = "junção completa". As duas tabelas ficam inteiras. Também se escreve FULL OUTER JOIN.
ON c.cidade = f.cidade: A regra do par: cliente e fornecedor da mesma cidade. (Não é uma chave estrangeira; um JOIN pode ligar por qualquer condição que faça sentido.)
SELECT DISTINCT …: Sem o DISTINCT, Teresina apareceria 20 vezes (10 clientes × 2 fornecedores). Com ele, cada combinação aparece uma vez.

As três partes do FULL JOIN estão todas aí:

Teresina, São Paulo e Fortaleza têm clientes e fornecedores: são os pares.
Parnaíba, Recife, Picos, Oeiras e Floriano só têm clientes: a coluna do fornecedor veio em NULL.
Porto e Buenos Aires só têm fornecedores (os dois estrangeiros): a coluna do cliente veio em NULL.

Experimente: esta versão usa LEFT JOIN e devolve só 8 linhas: Porto e Buenos Aires ficaram de fora, porque estão do lado direito sem par. Troque o tipo de JOIN para que as cidades dos dois lados apareçam.

SELECT DISTINCT c.cidade AS cidade_cliente,
                f.cidade AS cidade_fornecedor
FROM clientes c
LEFT JOIN fornecedores f ON c.cidade = f.cidade;

SELECT DISTINCT c.cidade AS cidade_cliente,
                f.cidade AS cidade_fornecedor
FROM clientes c
FULL JOIN fornecedores f ON c.cidade = f.cidade;

2. Só quem ficou sem par

O gerente quer os nomes: quais clientes moram numa cidade sem fornecedor, e quais fornecedores ficam numa cidade sem cliente? São as linhas em que um dos lados veio vazio:

SELECT c.nome AS cliente, c.cidade AS cidade_cliente,
       f.nome AS fornecedor, f.cidade AS cidade_fornecedor
FROM clientes c
FULL JOIN fornecedores f ON c.cidade = f.cidade
WHERE c.id IS NULL OR f.id IS NULL;

f.id IS NULL: Linha da esquerda sem par: um cliente cuja cidade não tem fornecedor.
c.id IS NULL: Linha da direita sem par: um fornecedor cuja cidade não tem cliente.
OR: Qualquer um dos dois casos (aula OR). Os pares completos têm os dois id preenchidos e ficam de fora.

Seis clientes do interior (e de Recife) e dois fornecedores estrangeiros. É a parte "de fora" dos dois círculos, sem o meio:

Quantas linhas tem o FULL JOIN inteiro, sem filtro nem DISTINCT? Os pares do INNER JOIN são 34 (Teresina: 10 clientes × 2 fornecedores = 20; São Paulo: 7; Fortaleza: 7). Somando as 6 sobras da esquerda e as 2 da direita: 34 + 6 + 2 = 42.

SELECT COUNT(*) AS full_join
FROM clientes c
FULL JOIN fornecedores f ON c.cidade = f.cidade;
SELECT COUNT(*) AS inner_join
FROM clientes c
JOIN fornecedores f ON c.cidade = f.cidade;

3. Quando um lado nunca sobra

E um FULL JOIN entre clientes e pedidos?

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

94 linhas, exatamente o mesmo que o LEFT JOIN da aula passada. Sobram os 4 clientes sem pedido (do lado esquerdo), mas nenhum pedido sem cliente: a chave estrangeira pedidos.cliente_id não deixa existir pedido de um cliente que não está no cadastro (aula Tabelas que se Relacionam). Quando as tabelas são ligadas por uma chave estrangeira bem cuidada, o lado da chave nunca sobra, e o FULL JOIN vira um LEFT JOIN (ou um RIGHT JOIN, conforme a ordem).

Por isso o FULL JOIN é o menos usado dos quatro. Ele aparece quando as duas tabelas são independentes e você quer comparar uma com a outra: as cidades de clientes e de fornecedores, o estoque do sistema com o estoque contado na prateleira, a lista de alunos com a lista de presença, os dados de dois sistemas que deveriam bater.

4. FULL JOIN com USING: as duas colunas viram uma

Com USING (aula INNER JOIN), a coluna da ligação aparece uma vez só. No FULL JOIN isso tem um efeito especial: a documentação do SQLite diz que, num FULL JOIN com USING, a coluna do resultado é o primeiro valor não vazio entre os dois lados (o mesmo que coalesce(esquerda.x, direita.x)). Na prática, a lista de todas as cidades, sem buracos:

SELECT DISTINCT cidade
FROM clientes
FULL JOIN fornecedores USING (cidade);

As 10 cidades, de clientes ou de fornecedores. Para Porto, o lado dos clientes estava vazio, então veio a cidade do fornecedor; para Picos, o contrário. A função coalesce ("unir", "juntar") tem aula própria: IFNULL e COALESCE.

5. A forma antiga: LEFT JOIN + UNION ALL

Até a versão 3.39.0, o SQLite não tinha FULL JOIN, e o MySQL não tem até hoje. A solução clássica é montar o FULL JOIN com as peças que todo banco tem: um LEFT JOIN para os pares e as sobras da esquerda, mais um segundo LEFT JOIN, com as tabelas trocadas, para as sobras da direita. As duas partes são empilhadas com UNION ALL ("união de tudo"), que põe o resultado de uma consulta embaixo do da outra (aula UNION ALL).

As linhas sem par da seção 2, sem FULL JOIN:

SELECT c.nome AS cliente, c.cidade AS cidade_cliente,
       f.nome AS fornecedor, f.cidade AS cidade_fornecedor
FROM clientes c
LEFT JOIN fornecedores f ON c.cidade = f.cidade
WHERE f.id IS NULL
UNION ALL
SELECT c.nome, c.cidade, f.nome, f.cidade
FROM fornecedores f
LEFT JOIN clientes c ON c.cidade = f.cidade
WHERE c.id IS NULL;

Primeira parte: clientes LEFT JOIN fornecedores … WHERE f.id IS NULL: Os clientes sem fornecedor na cidade (as sobras da esquerda).
UNION ALL: Empilha a segunda parte embaixo da primeira. As duas partes precisam ter o mesmo número de colunas, na mesma ordem; os nomes das colunas vêm da primeira.
Segunda parte: fornecedores LEFT JOIN clientes … WHERE c.id IS NULL: As tabelas trocadas de lugar: os fornecedores sem cliente na cidade (as sobras da direita). O SELECT lista as colunas na mesma ordem da primeira parte: cliente primeiro, fornecedor depois.

As mesmas 8 linhas da seção 2. Para o FULL JOIN completo (pares + as duas sobras), tire o WHERE da primeira parte e deixe o da segunda:

-- o FULL JOIN inteiro, sem FULL JOIN (42 linhas)
SELECT c.nome AS cliente, f.nome AS fornecedor
FROM clientes c
LEFT JOIN fornecedores f ON c.cidade = f.cidade   -- pares + sobras da esquerda
UNION ALL
SELECT c.nome, f.nome
FROM fornecedores f
LEFT JOIN clientes c ON c.cidade = f.cidade
WHERE c.id IS NULL;                               -- só as sobras da direita

O WHERE c.id IS NULL da segunda parte é o que impede os pares de aparecerem duas vezes: eles já vieram na primeira parte. É por isso que se usa UNION ALL, que empilha tudo sem mexer, e não UNION, que apaga linhas repetidas (aula UNION): duas linhas iguais de verdade (por exemplo, dois clientes com o mesmo nome) seriam fundidas numa só, e o resultado ficaria diferente do de um FULL JOIN.

Experimente: o mapa de cidades da seção 1, sem FULL JOIN. A primeira parte já está pronta. Acrescente UNION e uma segunda parte com as tabelas trocadas (FROM fornecedores f LEFT JOIN clientes c …), que traga as cidades só de fornecedores. Aqui o UNION sem ALL serve, porque com DISTINCT as repetições já não interessam.

SELECT DISTINCT c.cidade AS cidade_cliente, f.cidade AS cidade_fornecedor
FROM clientes c
LEFT JOIN fornecedores f ON c.cidade = f.cidade;

SELECT DISTINCT c.cidade AS cidade_cliente, f.cidade AS cidade_fornecedor
FROM clientes c
LEFT JOIN fornecedores f ON c.cidade = f.cidade
UNION
SELECT DISTINCT c.cidade, f.cidade
FROM fornecedores f
LEFT JOIN clientes c ON c.cidade = f.cidade;

Repare que, com UNION, as linhas podem sair em outra ordem: para achar as repetidas, o banco reorganiza o resultado. Se a ordem importa, termine com um ORDER BY.

Formas diferentes de fazer a mesma coisa

Forma	Quando usar
`FULL JOIN … ON`	No SQLite 3.39.0 ou mais novo, no PostgreSQL e no SQL Server. Curto e claro.
`FULL JOIN … USING (x)`	Quando a ligação é por colunas de mesmo nome e você quer uma coluna só, sem buracos. Não existe no SQL Server.
`LEFT JOIN` + `UNION ALL` + `LEFT JOIN` trocado com `WHERE … IS NULL`	No MySQL, em SQLite antigo, ou quando a consulta precisa rodar em qualquer banco.
`LEFT JOIN` só	Quando um dos lados nunca sobra (chave estrangeira): dá o mesmo resultado, e é mais simples.

Nos outros bancos

PostgreSQL e SQL Server têm FULL JOIN e FULL OUTER JOIN, como o SQLite. O SQL Server não tem USING, então lá só vale a forma com ON.
MySQL não tem FULL JOIN. Na gramática do JOIN do MySQL 8.4, as junções externas são só LEFT e RIGHT. Use a forma da seção 5. Ilustrativo, a mesma consulta no MySQL:

SELECT c.nome AS cliente, f.nome AS fornecedor
FROM clientes c
LEFT JOIN fornecedores f ON c.cidade = f.cidade
UNION ALL
SELECT c.nome, f.nome
FROM fornecedores f
LEFT JOIN clientes c ON c.cidade = f.cidade
WHERE c.id IS NULL;

(Essa consulta também roda no SQLite; ela é "ilustrativa" aqui só porque a ideia é mostrar o que se escreve no MySQL.)

Quando dá errado

Filtro no WHERE: o FULL virou LEFT (ou INNER)

A Ana quis o FULL JOIN das cidades, mas só com os clientes VIP:

SELECT DISTINCT c.cidade AS cidade_cliente, f.cidade AS cidade_fornecedor
FROM clientes c
FULL JOIN fornecedores f ON c.cidade = f.cidade
WHERE c.vip = 1;

O que acontece: sobraram só 3 linhas, as cidades com par. Porto e Buenos Aires sumiram: nas sobras da direita, c.vip é NULL, e o WHERE as jogou fora. E as cidades só de clientes também sumiram, porque nenhum cliente VIP mora nelas. Na prática, o FULL JOIN virou um INNER JOIN.

E no ON? Com FULL JOIN fornecedores f ON c.cidade = f.cidade AND c.vip = 1, Porto e Buenos Aires voltam, mas saem 13 linhas confusas: os clientes que não são VIP continuam no resultado, como sobras sem par (aparece até "Teresina | NULL", como se Teresina não tivesse fornecedor), porque num FULL JOIN a tabela da esquerda também fica inteira.

Como consertar: o que a Ana quer é "as cidades dos clientes VIP" de um lado e "as cidades dos fornecedores" do outro. Para isso, o filtro tem de agir nos clientes antes do JOIN, com uma consulta dentro do FROM, assunto da aula Subconsultas. A lição: num FULL JOIN, pense duas vezes antes de filtrar qualquer um dos lados.

Os NULL que nunca formam par

A Ana tentou o mesmo mapa pelo estado:

SELECT DISTINCT c.estado AS estado_cliente, f.estado AS estado_fornecedor
FROM clientes c
FULL JOIN fornecedores f ON c.estado = f.estado;

O que acontece: aparece uma linha NULL | NULL, que parece não fazer sentido. Ela vem dos dois fornecedores estrangeiros, que têm estado vazio. Um NULL nunca é igual a nada, nem a outro NULL, então eles não formam par com ninguém e entram como sobras da direita: com o lado do cliente vazio e o próprio estado vazio. Os dois viraram uma linha só por causa do DISTINCT.

Como evitar a confusão: mostre uma coluna que nunca é vazia (o nome ou o país do fornecedor) ou o id de cada lado, para saber de onde vem cada linha.

UNION ALL com as colunas em ordem diferente

Na forma antiga, se a segunda parte listar as colunas em outra ordem (SELECT f.nome, c.nome em vez de SELECT c.nome, f.nome), o SQLite não reclama: ele empilha por posição, e os nomes dos fornecedores vão parar na coluna cliente. Como evitar: copie a lista de colunas da primeira parte para a segunda, sem mudar a ordem.

UNION ALL com número diferente de colunas

SELECT c.nome, f.nome
FROM clientes c
LEFT JOIN fornecedores f ON c.cidade = f.cidade
UNION ALL
SELECT f.nome
FROM fornecedores f
LEFT JOIN clientes c ON c.cidade = f.cidade
WHERE c.id IS NULL;

A mensagem: SELECTs to the left and right of UNION ALL do not have the same number of result columns, "os SELECTs à esquerda e à direita do UNION ALL não têm o mesmo número de colunas". Como consertar: as duas partes precisam ter as mesmas colunas: SELECT c.nome, f.nome nas duas.

Referência rápida

Escrita	O que faz
`a FULL JOIN b ON …`	Os pares + as linhas de `a` sem par + as linhas de `b` sem par
`FULL OUTER JOIN`	O mesmo que `FULL JOIN`
`… WHERE a.chave IS NULL OR b.chave IS NULL`	Só as linhas sem par, dos dois lados
`a FULL JOIN b USING (x)`	A coluna `x` vem uma vez só, com o valor do lado que não estiver vazio
`a LEFT JOIN b … UNION ALL b LEFT JOIN a … WHERE a.chave IS NULL`	O `FULL JOIN` sem `FULL JOIN` (MySQL, SQLite antes da 3.39.0)

Os quatro JOINs na Loja Aurora	`clientes c … pedidos p`	`clientes c … fornecedores f` (por cidade)
`INNER JOIN`	90	34
`LEFT JOIN`	94	40
`RIGHT JOIN`	90	36
`FULL JOIN`	94	42

Exercícios

1. Complete o FULL JOIN

SELECT c.cidade, f.cidade FROM clientes c fornecedores f ON c.cidade = f.cidade;

2. Quantas linhas?

Um INNER JOIN devolve 34 linhas. Do lado esquerdo sobram 6 linhas sem par, e do direito, 2. Quantas linhas devolve o FULL JOIN?

Linhas:

3. Por que clientes FULL JOIN pedidos dá o mesmo resultado que clientes LEFT JOIN pedidos na Loja Aurora?

Porque o SQLite troca FULL por LEFT sozinho Porque não há clientes sem pedido Porque a chave estrangeira garante que não existe pedido sem cliente: o lado dos pedidos nunca sobra Porque o FULL JOIN só funciona com USING

4. Num banco sem FULL JOIN (como o MySQL), qual é a forma clássica de obtê-lo?

Dois INNER JOIN seguidos Um LEFT JOIN, mais UNION ALL, mais um LEFT JOIN com as tabelas trocadas e WHERE … IS NULL Um CROSS JOIN com DISTINCT Não há como

5. Na forma antiga, para que serve o WHERE c.id IS NULL da segunda parte?

Para não repetir os pares, que já vieram na primeira parte Para tirar os fornecedores estrangeiros Para o UNION ALL não dar erro Para ordenar o resultado

Missão final: os sem par, sem FULL JOIN

Missão: imagine que este banco fosse um MySQL, sem FULL JOIN. A consulta abaixo já encontra os clientes que moram numa cidade sem fornecedor (6 linhas). Complete-a com UNION ALL e uma segunda parte que traga também os fornecedores numa cidade sem cliente, para chegar às 8 linhas da seção 2.

SELECT c.nome AS cliente, f.nome AS fornecedor
FROM clientes c
LEFT JOIN fornecedores f ON c.cidade = f.cidade
WHERE f.id IS NULL;

SELECT c.nome AS cliente, f.nome AS fornecedor
FROM clientes c
LEFT JOIN fornecedores f ON c.cidade = f.cidade
WHERE f.id IS NULL
UNION ALL
SELECT c.nome, f.nome
FROM fornecedores f
LEFT JOIN clientes c ON c.cidade = f.cidade
WHERE c.id IS NULL;

Com isso você conhece os quatro tipos de JOIN. Falta um caso especial: juntar uma tabela com ela mesma, para mostrar cada funcionário ao lado do nome do chefe. É a próxima aula, Self Join.

Resumo

FULL JOIN (ou FULL OUTER JOIN) devolve os pares que combinam e todas as linhas sem par, dos dois lados, com NULL no lado que faltou.
Ele brilha quando as duas tabelas são independentes e podem ter sobras dos dois lados, como as cidades de clientes e de fornecedores. Ligadas por chave estrangeira, um dos lados nunca sobra, e o LEFT JOIN basta.
WHERE a.chave IS NULL OR b.chave IS NULL deixa só as sobras.
Com USING (x), a coluna x vem uma vez só, preenchida com o lado que tiver valor.
O SQLite tem FULL JOIN desde a 3.39.0; o MySQL não tem. A forma antiga é LEFT JOIN + UNION ALL + LEFT JOIN trocado com WHERE … IS NULL.
Um filtro no WHERE sobre uma das tabelas apaga as sobras do outro lado: o FULL JOIN deixa de ser completo.