O que é FULL JOIN no SQL? Uma explicação com 4 exemplos

O que é FULL JOIN - um dos tipos de junção SQL frequentemente negligenciados? Neste artigo, mostraremos como o FULL JOIN pode ser útil e ensinaremos como aplicá-lo a diferentes cenários. Os quatro exemplos abrangem alguns dos usos típicos.

Uma junção SQL é uma construção para combinar dados de duas ou mais tabelas. FULL JOIN é um dos tipos de junções. Você aproveitará ao máximo este artigo se já estiver familiarizado com as junções SQL e como elas funcionam. Caso contrário, ou se seu conhecimento precisar ser atualizado, recomendamos que faça nosso curso interativo Cláusulas JOIN em SQL curso interativo para preencher as lacunas. Noventa e três exercícios esperam por você lá, nos quais você recapitulará os tipos de junção e praticará a filtragem de dados usando junções simples e múltiplas, junções automáticas e junções não equitativas.

O que é um FULL JOIN?

FULL JOIN FULL JOIN ou FULL OUTER JOIN (o SQL aceita ambos) é uma junção externa. Em SQL, uma junção externa é um tipo de junção que inclui linhas não correspondentes de uma ou de ambas as tabelas unidas; LEFT JOIN e RIGHT JOIN também são junções externas. FULL JOIN é uma união de LEFT JOIN e RIGHT JOIN: mostra as linhas correspondentes e não correspondentes de ambas as tabelas. Quando os valores de uma tabela não são correspondentes na outra tabela, FULL JOIN retorna NULLs. Visualmente, isso pode ser mostrado da seguinte forma:

Sintaxe de FULL JOIN

A sintaxe de FULL JOIN é:

SELECT …
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

Como de costume nas uniões SQL, as partes principais da sintaxe FULL JOIN são as cláusulas JOIN e ON. A primeira tabela na cláusula FROM é seguida pela palavra-chave FULL JOIN, que une a segunda tabela. A condição na qual as tabelas são unidas é especificada na cláusula ON. Normalmente, as tabelas são unidas em uma coluna que é compartilhada entre elas - por exemplo, a coluna id na tabela writer e a coluna writer_ID na tabela book da tabela. Essas duas colunas teriam os mesmos valores devido à relação chave primária - chave estrangeira.

Em um exemplo simples, poderíamos escrever esse código e obter o seguinte resultado:

Outros tipos de junções no SQL

Já mencionamos que FULL JOIN é apenas um dos vários tipos de junções no SQL. Aqui está a lista completa:

• (INNER) JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN
• CROSS JOIN

JOIN (ou INNER JOIN) é um tipo de união interna que retorna somente as linhas correspondentes das tabelas unidas. Você pode saber mais sobre INNER JOIN aqui.

LEFT JOIN (ou LEFT OUTER JOIN) lista todas as linhas da primeira tabela (esquerda) e somente as linhas correspondentes da segunda tabela (direita). Se não houver linhas correspondentes na tabela da direita, os valores serão mostrados como NULL. Você pode encontrar mais informações em nosso artigo sobre LEFT JOIN.

RIGHT JOIN (ou RIGHT OUTER JOIN) é a imagem espelhada do LEFT JOIN. Ele retorna todas as linhas da segunda tabela (direita) e somente as linhas correspondentes da primeira tabela (esquerda). Quando há linhas não correspondentes na tabela da esquerda, os valores são NULL.

Agora você pode entender melhor por que dissemos que FULL JOIN é a união de LEFT JOIN e RIGHT JOIN. Como ele mostra todos os dados das tabelas da esquerda e da direita e também as linhas não correspondentes, é como se você usasse LEFT JOIN e RIGHT JOIN ao mesmo tempo.

CROSS JOIN retorna um produto cartesiano. Em outras palavras, ele retorna todas as combinações de todas as linhas de ambas as tabelas unidas.

Exemplos de FULL JOIN

Vamos agora aos exemplos que mostram o uso típico do FULL JOIN.

Exemplo 1: Mostrar todos os funcionários e departamentos da empresa

Tarefa: Você precisa mostrar todos os funcionários de uma empresa, mesmo que eles não tenham um departamento atribuído. Você também precisa mostrar todos os departamentos, mesmo que nenhum funcionário esteja alocado a eles.

Conjunto de dados: Usaremos duas tabelas para resolver o problema. A primeira tabela é department (o script está aqui), que é uma lista dos departamentos da empresa:

id	department_name
1	IT
2	Accounting
3	Sales

A segunda tabela é employees, com os dados mostrados abaixo. O script está aqui:

id	first_name	last_name	department_id
1	Sarah	Zimmerman	2
2	Thomas	Tyson	1
3	Daniel	Richardson	1
4	Sofia	Tardelli	2
5	Mark	Fitzpatrick	4

Solução:

SELECT 
  first_name,
  last_name,
  department_name
FROM employees e
FULL JOIN department d
  ON e.department_id = d.id;

Explicação: O código seleciona o nome e o sobrenome dos funcionários e o nome do departamento.

Podemos fazer isso porque acessamos FULL JOIN as duas tabelas. Fazemos referência à tabela employees em FROM e lhe damos um alias. Em seguida, fazemos referência à segunda tabela, departmentem FULL JOIN. As tabelas são unidas com base nos IDs de departamento encontrados em ambas as tabelas. Essa condição de união é escrita na cláusula ON.

Saída: O resultado retorna todos os dados de ambas as tabelas. Há algumas linhas interessantes que precisam ser elaboradas.

Mark Fitzpatrick não tem departamento - seu valor department_id seu valor é 5, mas não há nenhum departamento com ID 5 na tabela department. Portanto, há um valor NULL sob o nome do departamento. Isso pode ocorrer porque ele é um novo funcionário e a tabela employees tabela não foi atualizada com os dados do departamento.

Além disso, a última linha mostra o departamento Sales, mas nenhum nome de funcionário. Isso indica que Sales não tem funcionários. Não há nada de incomum aqui, pois soubemos que todos os três funcionários de Sales saíram da empresa recentemente.

first_name	last_name	department_name
Sarah	Zimmerman	Accounting
Thomas	Tyson	IT
Daniel	Richardson	IT
Sofia	Tardelli	Accounting
Mark	Fitzpatrick	NULL
NULL	NULL	Sales

Exemplo 2: Mostrar todos os clientes com o número de produtos comprados e o número de produtos sem vendas

Tarefa: Há uma loja de discos na Web que vende vinis (ou seja, discos de música). Sua tarefa é listar todos os clientes e descobrir o número de vinis que cada cliente comprou. O resultado também precisa mostrar o número de vinis que ninguém comprou ainda.

Conjunto de dados: Trabalharemos com três tabelas. A primeira é customercom o script aqui:

id	first_name	last_name
1	Marvin	Simmons
2	Marianne	Dickens
3	Susan	Strozzi

Você pode encontrar o script para a segunda tabela, vinyl, aqui. A tabela mostra a lista de vinis, como você pode ver abaixo:

id	artist	album_name	price
1	Callier, Terry	What Color is Love	24.99
2	Guy, Buddy	Sweet Tea	32.99
3	Little Simz	A Curious Tale of Trials	32.99
4	LaVette, Bettye	Scene of the Crime	36.99

A terceira tabela é uma tabela de junção chamada purchase. Você pode encontrar o script aqui:

id	customer_id	vinyl_id	purchase_date
1	1	2	2023-01-03
2	1	3	2023-01-12
3	1	2	2023-02-18
4	1	3	2023-03-01
5	2	3	2023-03-01
6	2	2	2023-04-01
7	2	4	2023-05-01

Solução:

SELECT 
  first_name,
  last_name,
  COUNT (v.id) AS vinyl_count	   
FROM customer c
FULL JOIN purchase p
  ON c.id = p.customer_id
FULL JOIN vinyl v
  ON p.vinyl_id = v.id
GROUP BY first_name, last_name;

Explicação: Esse é um exemplo de uso do site FULL JOIN e de junção de mais de duas tabelas.

A consulta seleciona os nomes dos clientes. Em seguida, ela usa a função de agregação COUNT() para encontrar o número de vinis comprados. Nós FULL JOIN a tabela customer com a tabela purchase tabela. Isso é feito nos IDs dos clientes em ambas as tabelas.

Agora, precisamos unir a terceira tabela. É simples: escreva FULL JOIN novamente e faça referência à tabela vinyl. Agora, você pode unir essa tabela com a tabela purchase nos IDs de vinil.

Resultado: O resultado mostra que há um vinil que ninguém encomendou ainda. Você pode reconhecê-lo pelos NULLs.

Ela também mostra uma lista de todos os clientes e o número de vinis que eles compraram. Susan Strozzi não comprou nada. Marianne Dickens e Marvin Simmons compraram três e quatro vinis, respectivamente.

first_name	last_name	vinyl_count
NULL	NULL	1
Susan	Strozzi	0
Marianne	Dickens	3
Marvin	Simmons	4

Exemplo 3: Listar todos os pares de certificação de alunos e as notas dos alunos

Tarefa: Você trabalha em uma plataforma de certificação SQL on-line. Ela oferece várias certificações; todo ano, há uma nova edição de cada certificação. Encontre todos os pares possíveis de certificação de alunos e a nota que cada aluno obteve em todas as edições da certificação.

Conjunto de dados: O conjunto de dados está aumentando; agora inclui quatro tabelas. A primeira tabela é studentaqui está o script. É uma lista dos alunos da plataforma:

nossos vinis, respectivamente.

id	first_name	last_name
1	Tom	Frank
2	Mary	Maddison
3	Pavel	Kuba
4	Amanda	Wilson

A segunda tabela é a tabela de certificados, que contém a lista de certificados. Aqui está o script:

id	certificate_name
1	Microsoft Certified: Azure Data Fundamentals
2	Oracle Database SQL Certified Associate Certification
3	IBM Certified Database Associate
4	MySQL 5.7 Database Administrator Certification
5	EDB PostgreSQL 12 Associate Certification

A próxima tabela é uma tabela de junção chamada certificate_enrollment

id	student_id	edition_id	grade	pass
1	2	1	620	FALSE
2	2	6	850	TRUE
3	2	10	900	TRUE
4	1	2	100	FALSE
5	1	7	500	FALSE
6	1	7	800	TRUE
7	4	8	800	TRUE

. Você pode encontrar o script aqui. A tabela mostra quais alunos se inscreveram em qual certificado, juntamente com suas notas e se foram aprovados:

A última tabela é uma tabela de junção chamada certificate_edition. Ela mostra a lista de edições do certificado, vinculando-se à tabela certificate. O script está aqui:

id	certificate_id	edition
1	1	2022
2	2	2022
3	3	2022
4	4	2022
5	5	2022
6	1	2023
7	2	2023
8	3	2023
9	4	2023
10	5	2023

Solução:

SELECT 
  first_name,
  last_name,
  certificate_name,
  edition,
  grade
FROM student s
FULL JOIN certificate_enrollment cen
  ON s.id = cen.student_id
FULL JOIN certificate_edition ced
  ON cen.edition_id = ced.id
FULL JOIN certificate c
  ON ced.certificate_id = c.id;

Explicação: Primeiro, selecionamos todas as colunas relevantes de todas as quatro tabelas: first_name e last_name from student, certificate_name from certificate, edition de certificate_edition e grade de certificate_enrollment.

Em seguida, unimos as tabelas como nos exemplos anteriores. Primeiro, é a tabela student tabela FULL JOINed com certificate_enrollment nos IDs dos alunos.

A segunda FULL JOIN adiciona a tabela certificate_edition para unir a tabela com certificate_enrollment. As tabelas são unidas com base no ID de edição do certificado.

Agora que juntamos três tabelas, podemos adicionar a quarta. Fazemos referência à tabela certificate em FULL JOIN. Nós a unimos com certificate_edition no ID do certificado.

Saída: O resultado mostra exatamente o que queremos.

Há uma lista de todos os alunos que receberam um certificado, sua edição e a nota que cada aluno obteve. Se a nota for inferior a 700, o aluno foi reprovado e teve de refazer o exame do certificado.

Um aluno ainda não se inscreveu em um programa de certificação; é Pavel Kuba. Além disso, há quatro edições do certificado sem nenhum aluno matriculado.

first_name	last_name	certificate_name	edition	grade
Mary	Maddison	Microsoft Certified: Azure Data Fundamentals	2022	620
Mary	Maddison	Microsoft Certified: Azure Data Fundamentals	2023	850
Mary	Maddison	EDB PostgreSQL 12 Associate Certification	2023	900
Tom	Frank	Oracle Database SQL Certified Associate Certification	2022	100
Tom	Frank	Oracle Database SQL Certified Associate Certification	2023	500
Tom	Frank	Oracle Database SQL Certified Associate Certification	2023	800
Amanda	Wilson	IBM Certified Database Associate	2023	800
Pavel	Kuba	NULL	NULL	NULL
NULL	NULL	EDB PostgreSQL 12 Associate Certification	2022	NULL
NULL	NULL	MySQL 5.7 Database Administrator Certification	2022	NULL
NULL	NULL	IBM Certified Database Associate	2022	NULL
NULL	NULL	MySQL 5.7 Database Administrator Certification	2023	NULL

Exemplo 4: Localizar todos os pares escritor-tópico e mostrar os artigos e seus status

Tarefa: Você trabalha para uma empresa que contrata redatores de artigos para seus clientes. Há uma lista de redatores e tópicos que você oferece. Além disso, cada tópico tem vários artigos disponíveis no momento. Os redatores são atribuídos a esses artigos. Um artigo pode ter um dos dois status: "Escrevendo" (o artigo está sendo escrito) ou "Revisando" (o artigo está sendo revisado).

Encontre todos os pares escritor-tópico. Além disso, liste todos os artigos e seus status.

Conjunto de dados: Esse conjunto de dados também consiste em quatro tabelas. A primeira tabela é writercom o script vinculado aqui:

id	first_name	last_name
1	Victoria	Thompson
2	Mike	McGill
3	Sky	Herrera
4	Jimmy	Goodman

A próxima tabela é topic. Aqui está o script:

id	topic_name
1	SQL
2	Python
3	ML
4	SQL Careers
5	Python Careers

A terceira tabela é article_assignment. Ela vincula o autor ao artigo e mostra o status atual do artigo. Aqui está o script:

id	writer_id	article_id	status
1	4	3	Revising
2	4	1	Writing
3	3	5	Writing
4	3	8	Revising
5	3	10	Revising
6	1	7	Writing

A quarta tabela vincula o artigo ao tópico. Ela se chama articlee o script está aqui:

id	article_title	topic_id
1	What is FULL JOIN in SQL? An Explanation with 4 Examples	1
2	Pandas in Python	2
3	Supervised Learning	3
4	Basic SQL Interview Questions	4
5	Basic Python Interview Questions	5
6	SQL Funções de Janela (Window Functions) em SQL	1
7	Ranking Data in Python	2
8	Unsupervised Learning	3
9	Intermediate SQL Interview Questions	4
10	Intermediate Python Interview Questions	5

Solução:

SELECT 
  first_name,
  last_name,
  topic_name,
  article_title,
  status
FROM writer w
FULL JOIN article_assignment aa
  ON w.id = aa.writer_id
FULL JOIN article a
  ON aa.article_id = a.id
FULL JOIN topic t
  ON a.topic_id = t.id;

Explicação: A consulta é semelhante à anterior. Ela seleciona todas as colunas relevantes: o nome do autor, o tópico, o título do artigo e seu status. Depois disso, ela une as tabelas writer e article_assignment no ID do autor usando FULL OUTER JOIN.

Outro FULL JOIN adiciona a terceira tabela article e a associa ao ID do artigo. Por fim, a quarta tabela é unida ao ID do tópico.

Resultado: Aqui está uma lista de todos os autores, os tópicos, os artigos e os status.

Os tópicos de Jimmy Goodman são ML e SQL. Sky Herrera escreve sobre carreiras em Python e ML. Victoria Thompson escreve apenas sobre Python.

Mike McGill não tem nenhum artigo atribuído. Além disso, há um artigo sobre Python, um sobre SQL e dois sobre Carreira em SQL que não foram atribuídos a nenhum escritor.

first_name	last_name	topic_name	article_title	status
Jimmy	Goodman	ML	Supervised Learning	Revising
Jimmy	Goodman	SQL	What is FULL JOIN in SQL? An Explanation with 4 Examples	Writing
Sky	Herrera	Python Careers	Basic Python Interview Questions	Writing
Sky	Herrera	ML	Unsupervised Learning	Revising
Sky	Herrera	Python Careers	Intermediate Python Interview Questions	Revising
Victoria	Thompson	Python	Ranking Data in Python	Writing
Mike	McGill	NULL	NULL	NULL
NULL	NULL	Python	Pandas in Python	NULL
NULL	NULL	SQL	SQL Funções de Janela (Window Functions) em SQL	NULL
NULL	NULL	SQL Careers	Basic SQL Interview Questions	NULL
NULL	NULL	SQL Careers	Intermediate SQL Interview Questions	NULL

No FULL JOIN, No SQL Mastery

FULL JOIN provavelmente não é usado com tanta frequência quanto JOIN ou LEFT JOIN. É uma junção humilde que espera até que você precise dela para brilhar. Mas sem ela em seu repertório, você não pode se considerar um mestre em SQL.

Os exemplos acima mostraram cenários práticos em que você precisará usar todos os dados de duas (ou mais) tabelas. FULL JOIN torna isso muito fácil!

É claro que você precisará de um pouco mais de prática, pois a parte mais importante do uso de uniões SQL é decidir qual união usar. Quando você dominar isso, as uniões se tornarão fáceis - a sintaxe é a mesma, independentemente do tipo de união que você usar. Nosso Cláusulas JOIN em SQL curso pode ajudá-lo a dominar o JOINs, pois está repleto de exercícios práticos. O mesmo pode ser dito de nosso artigo prático sobre junções SQL com 12 exemplos. Depois de aprender tudo isso, até mesmo essas perguntas da entrevista sobre junções SQL não serão difíceis de responder.

Agora, cabe a você usar todos esses recursos para dominar o FULL JOIN!