O que é um LEFT OUTER JOIN no SQL? Uma explicação com 4 exemplos

O artigo de hoje discutirá o LEFT OUTER JOIN em SQL. Examinaremos vários exemplos de uso do LEFT OUTER JOIN e o compararemos com o INNER JOIN.

O JOIN é um recurso do SQL que permite combinar dados de duas ou mais tabelas. A natureza dos bancos de dados relacionais torna o JOIN um dos recursos mais usados no SQL. Por quê? Na prática, muito raramente você terá todos os dados necessários em uma única tabela. Até mesmo os bancos de dados mais primitivos consistem em pelo menos duas tabelas.

Há muitos tipos diferentes de JOINs. Vamos nos concentrar em LEFT OUTER JOIN aqui. Para obter uma recapitulação abrangente dos diferentes tipos de JOINs, experimente nosso Cláusulas JOIN em SQL curso interativo. Além dos conceitos básicos, ele explica como unir várias tabelas e usar uniões auto e não equitativas. Você realizará mais de 90 exercícios práticos, todos baseados em exemplos do mundo real!

Vamos apresentar o site LEFT OUTER JOIN; depois, vamos torná-lo mais interessante com exemplos práticos. Se você precisar relembrar alguns conceitos à medida que avançamos, lembre-se de nossa Folha de consulta SQL JOIN.

O que é um LEFT OUTER JOIN?

O JOIN mais comum é o INNER JOIN. É um tipo de união que retorna somente as linhas correspondentes de ambas as tabelas unidas. Há outros tipos de JOIN que podem retornar linhas de uma tabela unida, mesmo que a linha não tenha uma linha correspondente na outra tabela. Esses tipos de JOINs são chamados de junções externas.

Uma LEFT JOIN é um tipo de união externa que gera todas as linhas da tabela da esquerda e as linhas correspondentes da tabela da direita.

Qual é a diferença entre LEFT OUTER JOIN e LEFT JOIN?

Resposta curta: Não há diferença!

Resposta longa: O nome completo desse tipo de união é realmente LEFT OUTER JOIN. Você provavelmente já viu exemplos de código SQL em que as tabelas são unidas somente com LEFT JOIN. Isso ocorre porque o SQL aceita tanto LEFT OUTER JOIN quanto LEFT JOIN.

Como LEFT JOIN é mais curto, ele é usado com mais frequência. Quando você o vê, ele significa simplesmente LEFT OUTER JOIN. O "Outer" está implícito, pois não há outra junção à esquerda que não seja uma junção externa esquerda.

Sintaxe de LEFT [OUTER] JOIN

A sintaxe de LEFT JOIN segue a sintaxe padrão de JOIN:

1. Faça referência à primeira tabela em FROM.
2. Use a palavra-chave LEFT JOIN para fazer referência à segunda tabela.
3. Use a palavra-chave ON para especificar a condição de união.

Em outras palavras, a sintaxe é:

SELECT …
FROM table_1
LEFT JOIN table_2
ON table_1.column = table_2.column;

Então, como funciona o LEFT OUTER JOIN? No exemplo genérico acima, a consulta fará referência a table_1 e se unirá à esquerda com table_2. Ela retornará primeiro todas as linhas de table_1, independentemente da condição de união. Por quê? Porque essa é a natureza do LEFT JOIN - ele retorna todas as linhas da tabela da esquerda (ou seja, da primeira).

Em seguida, a consulta examinará a condição de união - nesse caso, quando um valor na coluna de table_2 corresponde a um valor da coluna de table_1. O site LEFT JOIN retornará somente os valores da tabela da direita (ou seja, da segunda tabela, ou tabela_2) em que a condição de união for correspondente. Quando esses valores não estiverem na tabela correta, os valores retornados serão NULL. Você pode ver um exemplo visual disso em nosso artigo sobre como funciona o LEFT JOIN.

Quando as tabelas são unidas, nem é preciso dizer que você pode escolher qualquer coluna de ambas as tabelas em SELECT.

Agora que você já conhece a sintaxe, só falta colocá-la em prática.

4 Exemplos de LEFT OUTER JOIN

O conjunto de dados dos exemplos 1-3

Primeiro, vamos apresentar o conjunto de dados.

A primeira tabela, departmentstem os seguintes dados:

Você pode obter a consulta para criar a tabela aqui.

A segunda tabela é employeese você pode criá-la usando esta consulta. A tabela tem os seguintes dados:

Dois funcionários têm um valor NULL na coluna department_id. Esses são novos registros de funcionários que ainda não foram atualizados com o departamento.

Exemplo 1: Localizar todos os funcionários e seus departamentos

Vamos usar as tabelas acima para listar todos os funcionários e seus departamentos.

Este é um artigo sobre LEFT OUTER JOIN, portanto, não há nenhuma surpresa: usaremos exatamente essa união para resolver esse problema. Aqui está a consulta:

SELECT e.id,
	 e.first_name,
	 e.last_name,
	 d.department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.id
ORDER BY e.id;

Os dados de resultado de que precisamos são os números de identificação, os nomes e os departamentos dos funcionários. É por isso que essas colunas estão em SELECT.

Para obter todas essas colunas, precisamos acessar os dados de ambas as tabelas. Vamos ver como isso é feito.

Primeiro, a tabela employees é referenciada em FROM. Por que essa tabela e não a outra? Lembre-se: Qual tabela é referenciada primeiro importa em LEFT JOIN. Por quê? Porque essa tabela é a tabela da esquerda, e o site LEFT JOIN retornará todas as linhas dessa tabela, não importa o que aconteça.

Fazemos referência à tabela employees primeiro porque precisamos listar todos os funcionários dessa tabela. Em seguida, fazemos referência à tabela departments. Unimos as duas tabelas com a condição de que a coluna department_id da tabela employees seja a mesma que a coluna id da tabela departments. Essas duas colunas são dimensões compartilhadas (a chave primária e a chave estrangeira) entre essas duas tabelas, portanto, são ideais para uso na condição de união.

Para obter uma saída mais legível, ordenamos os resultados por ID de funcionário:

O resultado lista todos os funcionários - todos os 15. Ele também mostra seus departamentos, que são "Contabilidade" e "Vendas". Você perceberá que alguns funcionários têm valores NULL na coluna department_name. Dois deles foram mencionados anteriormente: novos funcionários sem um ID de departamento atualizado na tabela. Eles tinham valores NULL na tabela inicial e estão marcados em azul. employees inicial e estão marcados em azul.

Entretanto, há outros funcionários - marcados em verde - com valores NULL. Esse é o resultado do LEFT JOIN. Todos esses funcionários têm um ID de departamento 4, mas a tabela departments não contém esse valor. Parece que a tabela departments pode estar pedindo uma atualização também. Um banco de dados muito ruim para uma empresa fictícia tão respeitável!

Lembre-se: Os valores da tabela da esquerda não encontrados na tabela da direita serão mostrados como NULL.

E se usássemos um INNER JOIN?

Vamos escrever a consulta acima novamente, desta vez com um INNER JOIN (geralmente abreviado como JOIN) em vez de um LEFT JOIN:

SELECT e.id,
	 e.first_name,
	 e.last_name,
	 d.department_name
FROM departments d
JOIN employees e
ON e.department_id = d.id
ORDER BY e.id;

A sintaxe é exatamente a mesma; apenas usamos um tipo de junção diferente. Vamos ver qual será o resultado:

Alguns funcionários estão faltando; há apenas nove aqui. Uma inspeção mais detalhada mostra que os funcionários com IDs 3, 4, 7, 9, 11 e 12 não estão incluídos no resultado. Por quê? Se você voltar atrás, verá que os funcionários que estão faltando são aqueles com NULLs em department_name na saída LEFT OUTER JOIN.

Lembre-se de que INNER JOIN retornará somente as linhas correspondentes de ambas as tabelas - em outras palavras, somente os funcionários que têm um ID de departamento encontrado em ambas as tabelas.

Portanto, nessa situação, em que queríamos todos os funcionários e seus departamentos, o LEFT JOIN é a escolha certa. Agora também vemos os funcionários sem o departamento e podemos dizer que nosso banco de dados precisa ser atualizado.

Exemplo 2: Listar todos os departamentos e seus funcionários

Para obter o resultado desejado, temos de trocar a ordem das tabelas em LEFT JOIN.

Aqui está a consulta:

SELECT d.id,
	 d.department_name,
	 e.first_name,
	 e.last_name
FROM  departments d
LEFT JOIN employees e
ON d.id = e.department_id
ORDER BY d.id;

Ajustamos a ordem das colunas em SELECT para apresentar melhor o resultado. A maioria das colunas é a mesma de antes. Desta vez, estamos selecionando o ID do departamento na tabela departmentse não o ID do funcionário.

Agora, a tabela departments é a nossa tabela da esquerda. Isso ocorre porque queremos mostrar todos os departamentos dessa tabela, quer eles apareçam ou não na tabela employees - nossa tabela da direita.

A condição ON permanece a mesma; apenas invertemos a ordem das colunas. Isso realmente não importa; poderia ter permanecido na mesma ordem. Isso é apenas por razões estéticas, pois é mais fácil ler a condição quando ela segue a ordem das tabelas em LEFT JOIN.

Além disso, classificamos o resultado por ID de departamento.

Aqui está o resultado:

Esse resultado mostra apenas nove funcionários, em comparação com os 15 do exemplo anterior. Todos os funcionários com valores NULL na saída do último exemplo não aparecem nessa saída.

O motivo é, novamente, LEFT JOIN. Quando criamos departments nossa tabela à esquerda, ela listou todos os departamentos e seus funcionários. Todos os outros funcionários não estão aqui. Por quê? Ou eles têm o ID de departamento 4, que não aparece na tabela departmentsou são novos funcionários (lembra-se deles?) com NULL como ID de departamento.

O único NULLs nessa saída aparece na última linha. Há o departamento "Compliance", que não tem nenhum funcionário alocado a ele. Em outras palavras, não há funcionários com o valor 5 na coluna department_id da tabela. employees tabela.

Lembre-se: A ordem das tabelas em LEFT JOIN é importante!

E se usássemos um INNER JOIN?

Vamos alterar a consulta e usar um INNER JOIN:

SELECT d.id,
	 d.department_name,
	 e.first_name,
	 e.last_name
FROM  departments d
INNER JOIN employees e
ON d.id = e.department_id
ORDER BY d.id;

O resultado é basicamente o mesmo de quando fizemos um inner join no Exemplo 1. A única diferença é que há um ID de departamento em vez de um ID de funcionário. Fora isso, é a mesma coisa, portanto, não perderemos tempo analisando-a.

Exemplo 3: listar todos os departamentos e o número de funcionários em cada um

LEFT JOINA junção de dados, como todas as outras junções, é frequentemente usada com as funções de agregação do SQL. Ela será útil aqui.

Dê uma olhada nesta consulta:

SELECT d.department_name, 
 COUNT(e.id) AS number_of_employees
FROM departments d
LEFT JOIN employees e
ON d.id = e.department_id
GROUP BY d.department_name;

Vamos começar a explicar a partir da cláusula FROM. Como queremos listar todos os departamentos existentes, primeiro fazemos referência à tabela departments em FROM. Por quê? Porque há alguns funcionários sem um departamento e não queremos que eles apareçam em nosso resultado. Como vimos, também há um departamento sem funcionários; queremos esse departamento no resultado de qualquer forma.

A seguir, a tabela employees após LEFT JOIN. As tabelas são unidas com a mesma condição do exemplo anterior.

Para obter o resultado correto, precisamos listar o nome do departamento em SELECT. Também precisamos usar a função de agregação COUNT(e.id) para contar o número de IDs de funcionários.

Por que não podemos usar COUNT(*)? Porque ela conta todas as linhas, inclusive os valores de NULL. Isso distorceria nossos resultados. O departamento de conformidade tem zero funcionários, o que teria sido mostrado como NULL quando unido. COUNT(*) teria contado esse NULL como um, o que não seria exato.

A opção COUNT(e.id) ignora os valores de NULL e contará apenas os funcionários cujo ID não seja NULL. Esse é mais um tópico de função agregada do que um tópico de LEFT JOIN, portanto, não entraremos em mais detalhes.

Entretanto, é extremamente importante entender quando usar as várias opções do COUNT(). Para obter mais informações, consulte nosso artigo que detalha todas as diferentes encarnações e usos da função COUNT().

Voltemos ao nosso código. Após a contagem e a junção, o resultado é agrupado pelo nome do departamento. Tudo isso resultará na lista de departamentos com o número de funcionários em cada departamento:

O resultado mostra que há, no total, nove funcionários nos departamentos da empresa: seis em Contabilidade, zero em Conformidade e três em Vendas.

E se usássemos INNER JOIN?

Agora, vamos fazer a mesma consulta, mas com INNER JOIN.

SELECT d.department_name, 
	 COUNT(e.id) AS number_of_employees
FROM departments d
JOIN employees e
ON d.id = e.department_id
GROUP BY d.department_name;

O resultado será diferente? Vamos ver.

O resultado, mais uma vez, mostra que há nove funcionários em vários departamentos. O que obviamente está faltando é que a empresa tem um departamento de Compliance que não é mostrado nesse resultado.

Se tivéssemos usado INNER JOIN, teríamos concluído que há apenas dois departamentos na empresa. LEFT JOIN foi, novamente, a escolha certa, pois pode haver (pelo menos temporariamente) um departamento sem funcionários.

Conjunto de dados para o Exemplo 4

Usaremos três tabelas neste exemplo. A primeira é artistse contém os seguintes dados sobre artistas musicais:

Crie a tabela usando a consulta aqui.

A próxima tabela é albums:

E aqui está a consulta para criar essa tabela.

A consulta para a terceira tabela está aqui. A tabela é denominada grammy_award. É uma lista de categorias do prêmio Grammy. Ela tem apenas duas categorias: o vencedor do melhor álbum e o indicado ao prêmio de melhor álbum.

Exemplo 4: Listar todos os artistas, seus álbuns e a categoria do prêmio Grammy

LEFT JOIN também pode ser usado para unir mais de duas tabelas, e veremos como na consulta abaixo:

SELECT artist_name,
	 album_title,
	 grammy_category
FROM artists ar
LEFT JOIN albums al
ON ar.id = al.artist_id
LEFT JOIN grammy_award ga
ON al.grammy_category_id = ga.id;

Primeiro, listamos todas as colunas necessárias para mostrar o nome do artista, o título do álbum, o ano de lançamento e a categoria do Prêmio Grammy.

Vemos nas tabelas apresentadas que há álbuns sem informações sobre o artista. Nosso catálogo está incompleto, portanto, a maneira mais segura de listar todos os artistas disponíveis é por meio das tabelas LEFT JOIN e considerar a tabela artists como a tabela da esquerda. Ela é unida à esquerda com a tabela albums no ID do artista.

Para obter os dados sobre os Grammys, precisamos de alguma forma unir a terceira tabela. Como isso é feito? Simples: escreva o comando LEFT JOIN novamente e faça referência à tabela grammy_award depois dele. Isso fará com que LEFT JOIN albums com grammy_award. As tabelas são unidas pelo ID da categoria Grammy.

Vamos ver o resultado:

O resultado mostra todos os artistas, seus álbuns e se o álbum foi indicado ou vencedor do prêmio de Álbum do Ano. Ele também mostra os álbuns que não foram indicados nem vencedores do Grammy.

E se usássemos INNER JOIN?

Aqui está o mesmo código acima, com JOIN em vez de LEFT JOIN:

SELECT artist_name,
	 album_title,
	 grammy_category
FROM artists ar
JOIN albums al
ON ar.id = al.artist_id
JOIN grammy_award ga
ON al.grammy_category_id = ga.id;

Vamos ver o que o código retorna:

Esse resultado está incompleto. Está faltando um artista: George Benson. Também estão faltando os álbuns sem nenhuma categoria do Grammy. Como não estávamos tentando listar apenas os álbuns com uma categoria do Grammy, o LEFT JOIN foi a escolha certa.

Aqui estão algumas explicações adicionais sobre como fazer LEFT JOIN em várias tabelas.

Espaço para iniciantes: Algumas dicas sobre como escrever JOINs

Aqui está uma visão geral dos erros mais comuns que os iniciantes cometem ao usar o LEFT OUTER JOIN no SQL.

Já os cobrimos nos exemplos, mas talvez não sejam tão óbvios para um olho menos experiente. Portanto, vamos colocá-los em palavras.

Escolhendo o Join correto

Comparamos todos os exemplos do LEFT JOIN com as versões do INNER JOIN. Como você viu, o resultado muda significativamente. É por isso que você precisa escolher cuidadosamente a junção que usará.

A melhor dica é pensar nessas duas definições de junção. Portanto, se você precisar apenas de dados correspondentes de duas tabelas, precisará de INNER JOIN. Em todos os outros casos, LEFT JOIN provavelmente será a escolha correta.

Como decidir qual tabela é a esquerda

Depois de optar por usar o LEFT JOIN, como você sabe qual tabela deve ser a tabela da esquerda? Em primeiro lugar, a tabela da esquerda é a tabela que vem imediatamente após FROM. A direita é a que vem depois de LEFT JOIN.

Mas como você decide qual é qual? Você precisa entender corretamente o problema que está resolvendo. Procure pistas no texto e na lógica. Se você precisar de todos os funcionários, a tabela com os funcionários será a da esquerda. Em outras palavras, se você acha que uma das tabelas precisa ser mostrada sem alterações, essa é a tabela da esquerda.

Cuidado com as funções agregadas

Mesmo que você cometa um erro e escolha a junção errada, é muito provável que você o detecte no resultado se não agregar os dados. Se você estiver familiarizado com seus dados, verá algumas linhas faltando ou nenhuma NULLs onde você esperava que elas estivessem.

Entretanto, tenha muito cuidado ao usar funções de agregação. Como a saída será agregada, será muito mais difícil ver o erro na saída - a menos que você saiba de cor todos os resultados da agregação de dados, o que é altamente improvável. Nesse caso, você não precisaria de SQL, não é mesmo?

Isso é especialmente verdadeiro quando se usa COUNT(). Como você viu em um dos nossos exemplos, o COUNT(*) pode fornecer resultados diferentes do COUNT(column_name). É extremamente importante que você saiba o que deseja alcançar e como o COUNT() funciona.

Lembre-se: COUNT(column_name) ignora os valores de NULL, enquanto COUNT(*) não!

Portanto, ao trabalhar com funções de agregação, teste sua consulta com INNER JOIN - se tiver a oportunidade - e veja se ela retorna resultados diferentes. Se isso não acontecer, então não importa qual junção você usará. Se isso acontecer, volte à definição do problema e veja qual lógica de junção é mais adequada à sua lógica comercial.

LEFT JOINs em cascata

Isso é observado ao unir mais de duas tabelas. Se você optar por LEFT JOIN as duas primeiras tabelas, geralmente terá de LEFT JOIN todas as outras tabelas.

Você viu isso em nosso último exemplo, pois usamos duas LEFT JOINs. Mesmo que usássemos primeiro uma LEFT JOIN e depois uma INNER JOIN, não obteríamos a lista de todos os artistas e os outros dados. Incluir INNER JOIN nessa cadeia de junções dará o mesmo resultado que usar INNER JOIN em vez de cada LEFT JOIN.

Esse nem sempre é o caso. Mas é bastante seguro seguir esta regra geral: se você precisa de um LEFT JOIN, você precisa de todos os LEFT JOINs.

Procurando mais prática de LEFT JOIN?

Este artigo lhe deu uma ideia do que é um LEFT JOIN e de como gerenciar suas eventuais artimanhas. Também praticamos a escrita de código enquanto você se familiarizava com a sintaxe do LEFT JOIN.

Para dominar todas as nuances do LEFT OUTER JOIN em SQL, recomendamos mais prática. Três exemplos não são suficientes! Para realmente desenvolver o que você aprendeu aqui, recomendamos nosso Cláusulas JOIN em SQL curso. Além dos fundamentos teóricos, você também terá muita prática com cenários do mundo real.

Aqui estão algumas ideias adicionais sobre como praticar Cláusulas JOIN em SQL. As perguntas da entrevista Cláusulas JOIN em SQL também são um recurso valioso para a prática de materiais. Bom aprendizado!