SQL FULL OUTER JOIN: Manter Todas as Linhas das Duas Tabelas

Lição 5.5 · Tempo de leitura: ~6 min

O SQL FULL OUTER JOIN devolve todas as linhas das duas tabelas: as linhas correspondentes são combinadas, e as linhas sem correspondência de qualquer um dos lados são mantidas com NULL nas colunas da outra tabela. Nesta lição, você vai aprender a sintaxe, quais bancos de dados a suportam e como obter o mesmo resultado no MySQL e no MariaDB.

O LEFT JOIN mantém as linhas sem correspondência de uma tabela, o RIGHT JOIN as da outra. O FULL OUTER JOIN mantém as duas. É a ferramenta para comparar duas listas: o que está nas duas, o que está só na primeira e o que está só na segunda.

O Que o FULL OUTER JOIN Devolve

O resultado tem três partes:

1. Pares de linhas correspondentes, como no INNER JOIN.
2. Linhas da tabela da esquerda sem correspondência, com NULL nas colunas da direita.
3. Linhas da tabela da direita sem correspondência, com NULL nas colunas da esquerda.

Sintaxe Básica

SELECT left_table.column1, right_table.column2
FROM left_table
FULL OUTER JOIN right_table ON left_table.key_column = right_table.key_column;

FULL JOIN e FULL OUTER JOIN são a mesma coisa.

Exemplo: Atores e Clientes

Quem no banco Sakila é ator, quem é cliente e quem é as duas coisas? No PostgreSQL ou no SQL Server, uma junção completa pelo nome responde diretamente:

-- PostgreSQL, SQL Server
SELECT a.first_name AS actor_first, a.last_name AS actor_last,
       c.first_name AS customer_first, c.last_name AS customer_last
FROM actor AS a
FULL OUTER JOIN customer AS c
     ON c.first_name = a.first_name AND c.last_name = a.last_name;

Cada ator aparece uma vez, cada cliente aparece uma vez, e uma pessoa que é as duas coisas aparece numa só linha com os dois lados preenchidos.

FULL OUTER JOIN no MySQL e no MariaDB

MySQL e MariaDB não têm FULL OUTER JOIN, mas o mesmo resultado pode ser montado com duas consultas unidas por UNION ALL:

1. Um LEFT JOIN devolve as correspondências e as linhas que existem só na tabela da esquerda.
2. Um RIGHT JOIN com WHERE left_key IS NULL acrescenta apenas as linhas que existem só na tabela da direita.

SELECT a.first_name AS actor_first, a.last_name AS actor_last,
       c.first_name AS customer_first, c.last_name AS customer_last
FROM actor AS a
LEFT JOIN customer AS c
       ON c.first_name = a.first_name AND c.last_name = a.last_name
UNION ALL
SELECT a.first_name, a.last_name, c.first_name, c.last_name
FROM actor AS a
RIGHT JOIN customer AS c
       ON c.first_name = a.first_name AND c.last_name = a.last_name
WHERE a.actor_id IS NULL;

Resultado: 798 linhas: 200 atores mais 599 clientes, menos uma pessoa que é as duas coisas e aparece numa única linha combinada (JENNIFER DAVIS).

Às vezes essa alternativa aparece escrita com UNION em vez de UNION ALL e sem a condição WHERE. Só funciona quando o resultado não tem linhas legitimamente repetidas, porque o UNION remove todos os duplicados, e é mais lenta. A versão com UNION ALL é exata. Você vai aprender UNION e UNION ALL na Lição 5.10.

Usar uma Junção Completa para Comparar Listas

Uma junção completa é útil sempre que duas fontes devem conter os mesmos itens:

• conciliar os pagamentos do banco de dados com um extrato bancário;
• comparar um catálogo de produtos com a lista de estoque;
• encontrar registros que existem em apenas um de dois sistemas depois de uma migração.

Para manter apenas as diferenças, filtre as linhas em que um dos lados é NULL:

-- PostgreSQL, SQL Server: pessoas que são só atores ou só clientes
SELECT a.actor_id, c.customer_id
FROM actor AS a
FULL OUTER JOIN customer AS c
     ON c.first_name = a.first_name AND c.last_name = a.last_name
WHERE a.actor_id IS NULL OR c.customer_id IS NULL;

Principais conclusões:

• O FULL OUTER JOIN devolve as linhas correspondentes mais as linhas sem correspondência das duas tabelas.
• As colunas do lado em falta são NULL.
• PostgreSQL, SQL Server, Oracle e SQLite 3.39+ o suportam; MySQL e MariaDB não.
• No MySQL e no MariaDB: LEFT JOIN UNION ALL RIGHT JOIN ... WHERE left_key IS NULL.
• Uma junção completa é a ferramenta natural para comparar duas listas.

Perguntas Frequentes

O MySQL suporta FULL OUTER JOIN?

Não. MySQL e MariaDB não o suportam. Combine um LEFT JOIN e um RIGHT JOIN com UNION ALL, mantendo apenas as linhas sem correspondência na segunda consulta (WHERE left_key IS NULL).

Qual é a diferença entre FULL OUTER JOIN e CROSS JOIN?

O FULL OUTER JOIN emparelha linhas por uma condição e mantém as linhas sem correspondência das duas tabelas. O CROSS JOIN não tem condição e devolve todas as combinações de linhas, por isso o seu resultado costuma ser muito maior.

Por que usar UNION ALL em vez de UNION para emular FULL OUTER JOIN?

O UNION remove linhas duplicadas, incluindo as que se repetem legitimamente nos dados, e precisa de trabalho extra para encontrar duplicados. O UNION ALL com o filtro IS NULL na segunda consulta devolve exatamente as linhas de uma junção completa.