26th Oct 2023 12 minutos de leitura O que é FULL JOIN no SQL? Uma explicação com 4 exemplos Tihomir Babic sql join join joins 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: iddepartment_name 1IT 2Accounting 3Sales A segunda tabela é employees, com os dados mostrados abaixo. O script está aqui: idfirst_namelast_namedepartment_id 1SarahZimmerman2 2ThomasTyson1 3DanielRichardson1 4SofiaTardelli2 5MarkFitzpatrick4 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_namelast_namedepartment_name SarahZimmermanAccounting ThomasTysonIT DanielRichardsonIT SofiaTardelliAccounting MarkFitzpatrickNULL NULLNULLSales 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: idfirst_namelast_name 1MarvinSimmons 2MarianneDickens 3SusanStrozzi Você pode encontrar o script para a segunda tabela, vinyl, aqui. A tabela mostra a lista de vinis, como você pode ver abaixo: idartistalbum_nameprice 1Callier, TerryWhat Color is Love24.99 2Guy, BuddySweet Tea32.99 3Little SimzA Curious Tale of Trials32.99 4LaVette, BettyeScene of the Crime36.99 A terceira tabela é uma tabela de junção chamada purchase. Você pode encontrar o script aqui: idcustomer_idvinyl_idpurchase_date 1122023-01-03 2132023-01-12 3122023-02-18 4132023-03-01 5232023-03-01 6222023-04-01 7242023-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_namelast_namevinyl_count NULLNULL1 SusanStrozzi0 MarianneDickens3 MarvinSimmons4 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. idfirst_namelast_name 1TomFrank 2MaryMaddison 3PavelKuba 4AmandaWilson A segunda tabela é a tabela de certificados, que contém a lista de certificados. Aqui está o script: idcertificate_name 1Microsoft Certified: Azure Data Fundamentals 2Oracle Database SQL Certified Associate Certification 3IBM Certified Database Associate 4MySQL 5.7 Database Administrator Certification 5EDB PostgreSQL 12 Associate Certification A próxima tabela é uma tabela de junção chamada certificate_enrollment idstudent_idedition_idgradepass 121620FALSE 226850TRUE 3210900TRUE 412100FALSE 517500FALSE 617800TRUE 748800TRUE . 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: idcertificate_idedition 112022 222022 332022 442022 552022 612023 722023 832023 942023 1052023 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_namelast_namecertificate_nameeditiongrade MaryMaddisonMicrosoft Certified: Azure Data Fundamentals2022620 MaryMaddisonMicrosoft Certified: Azure Data Fundamentals2023850 MaryMaddisonEDB PostgreSQL 12 Associate Certification2023900 TomFrankOracle Database SQL Certified Associate Certification2022100 TomFrankOracle Database SQL Certified Associate Certification2023500 TomFrankOracle Database SQL Certified Associate Certification2023800 AmandaWilsonIBM Certified Database Associate2023800 PavelKubaNULLNULLNULL NULLNULLEDB PostgreSQL 12 Associate Certification2022NULL NULLNULLMySQL 5.7 Database Administrator Certification2022NULL NULLNULLIBM Certified Database Associate2022NULL NULLNULLMySQL 5.7 Database Administrator Certification2023NULL 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: idfirst_namelast_name 1VictoriaThompson 2MikeMcGill 3SkyHerrera 4JimmyGoodman A próxima tabela é topic. Aqui está o script: idtopic_name 1SQL 2Python 3ML 4SQL Careers 5Python Careers A terceira tabela é article_assignment. Ela vincula o autor ao artigo e mostra o status atual do artigo. Aqui está o script: idwriter_idarticle_idstatus 143Revising 241Writing 335Writing 438Revising 5310Revising 617Writing A quarta tabela vincula o artigo ao tópico. Ela se chama articlee o script está aqui: idarticle_titletopic_id 1What is FULL JOIN in SQL? An Explanation with 4 Examples1 2Pandas in Python2 3Supervised Learning3 4Basic SQL Interview Questions4 5Basic Python Interview Questions5 6SQL Funções de Janela (Window Functions) em SQL1 7Ranking Data in Python2 8Unsupervised Learning3 9Intermediate SQL Interview Questions4 10Intermediate Python Interview Questions5 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_namelast_nametopic_namearticle_titlestatus JimmyGoodmanMLSupervised LearningRevising JimmyGoodmanSQLWhat is FULL JOIN in SQL? An Explanation with 4 ExamplesWriting SkyHerreraPython CareersBasic Python Interview QuestionsWriting SkyHerreraMLUnsupervised LearningRevising SkyHerreraPython CareersIntermediate Python Interview QuestionsRevising VictoriaThompsonPythonRanking Data in PythonWriting MikeMcGillNULLNULLNULL NULLNULLPythonPandas in PythonNULL NULLNULLSQLSQL Funções de Janela (Window Functions) em SQLNULL NULLNULLSQL CareersBasic SQL Interview QuestionsNULL NULLNULLSQL CareersIntermediate SQL Interview QuestionsNULL 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! Tags: sql join join joins