Como você se livra de duplicatas em um SQL JOIN?

Você tem duplicatas indesejadas de sua consulta SQL JOIN? Neste artigo, discutirei os possíveis motivos para obter duplicatas após juntar tabelas em SQL e mostrarei como consertar uma consulta, dependendo do motivo por trás das duplicatas.

Analistas de dados com pouca experiência em Cláusulas JOIN em SQL frequentemente encontram duplicatas indesejadas no conjunto de resultados. É um desafio para iniciantes identificar o motivo por trás dessas duplicatas em JOINs.

A melhor maneira de aprender Cláusulas JOIN em SQL é através da prática. Eu recomendo o Cláusulas JOIN em SQL curso. Ele contém mais de 90 exercícios que fazem você praticar os diferentes tipos de JOIN em SQL.

Neste artigo, vou discutir as questões mais comuns que levam às duplicatas nas saídas SQL JOIN. Também vou mostrar possíveis soluções para estas questões comuns.

Vamos começar com uma breve visão geral de Cláusulas JOIN em SQL.

Cláusulas JOIN em SQL Visão geral .

JOIN é uma construção SQL para solicitar informações de duas ou mais tabelas dentro da mesma consulta.

Por exemplo, digamos que você tenha uma lista dos 100 melhores filmes do século 20, e você queira subestimá-la aos filmes feitos por diretores vivos atualmente. Em seu movies tabela, você não tem informações detalhadas sobre os diretores de cinema, apenas suas identificações. Mas você tem uma tabela separada directors tabela, com a identificação, o nome completo, o ano de nascimento e o ano da morte (se aplicável) de cada diretor.

Em sua consulta, você pode unir duas tabelas pelo ID do diretor para obter uma lista de filmes feitos por diretores vivos atualmente:

SELECT movies.title, directors.full_name
FROM movies
JOIN directors
ON movies.director_id = directors.id
WHERE directors.death_year IS NULL;

Como você pode ver, nós especificamos as tabelas que queremos unir nas cláusulas FROM e JOIN. Em seguida, na cláusula ON, especificamos as colunas de cada tabela a serem utilizadas para unir-se a estas tabelas. Se você é novo no Cláusulas JOIN em SQL, confira este guia introdutório. Aqui está também uma folha de consultas SQL JOIN com sintaxe e exemplos de diferentes JOINs.

O SQL JOIN é uma grande ferramenta que fornece uma variedade de opções além da simples junção de duas tabelas. Se você não estiver familiarizado com os tipos de SQL JOIN, leia este artigo que os explica com ilustrações e exemplos. Dependendo de seu caso de uso, você pode escolher INNER JOIN, LEFT JOIN, RIGHT JOIN, e FULL JOIN. Você pode até mesmo precisar juntar tabelas sem uma coluna comum ou juntar mais de duas tabelas.

Agora, vamos ver como estas diferentes uniões podem resultar em duplicatas indesejadas.

Quais são as razões para duplicatas em Cláusulas JOIN em SQL?

Há muitas razões possíveis para obter duplicatas no resultado de sua consulta SQL JOIN. Vou analisar as 5 principais razões; para cada uma delas, mostrarei uma consulta de amostra com o problema e uma consulta corrigida para obter um resultado sem duplicatas.

Vamos começar revendo brevemente os dados a serem utilizados para nossos exemplos. Imagine que administramos uma agência imobiliária que vende casas em algum lugar nos Estados Unidos. Temos tabelas com agents, customerse sales. Veja abaixo quais dados são armazenados em cada tabela.

Sem mais demora, vamos passar aos nossos exemplos.

1. Condição de Falta

Os iniciantes não familiarizados com Cláusulas JOIN em SQL geralmente simplesmente listam as tabelas em FROM sem especificar a condição JOIN quando tentam combinar informações de duas ou mais tabelas. Esta é uma sintaxe válida, de modo que você não recebe nenhuma mensagem de erro. Mas o resultado é uma junção cruzada com todas as linhas de uma tabela combinada com todas as linhas de outra tabela.

Por exemplo, suponha que queremos obter informações sobre o cliente que comprou uma determinada casa (ID nº 2134). Se utilizarmos a seguinte consulta:

SELECT house_id, first_name, last_name, email
FROM sales, customers
WHERE house_id = 2134;

Este é o resultado que obtemos:

Em vez de um registro com o cliente que queremos, temos todos os nossos clientes listados no conjunto de resultados.

Para consertar a consulta, é necessária uma sintaxe explícita JOIN. As tabelas a serem combinadas estão especificadas em FROM e JOIN, e a condição de junção está especificada na cláusula ON:

SELECT s.house_id, c.first_name, c.last_name, c.email
FROM sales s
JOIN customers c
ON s.customer_id = c.id
WHERE s.house_id = 2134;

Aqui, nós especificamos o ID do cliente a partir do sales tabela para corresponder à identificação do cliente do customers mesa. Isto nos dá o resultado desejado:

Você poderia especificar a condição de junção na cláusula WHERE para obter o mesmo resultado. Mas isso é contra o uso pretendido da cláusula WHERE. Além disso, há benefícios adicionais pelo uso da sintaxe JOIN em vez de listar as tabelas em FROM. Verifique este artigo para entender porque a sintaxe JOIN é a preferida.

2. Usando uma condição Incompleta ON

Linhas indesejadas no conjunto de resultados podem vir de condições ON incompletas. Em alguns casos, você precisa unir tabelas por várias colunas. Nessas situações, se você usar apenas um par de colunas, isso resulta em linhas duplicadas.

Digamos que queremos ver o nível de experiência do agente imobiliário para cada casa vendida. Se começarmos por nos juntar ao sales e agents tabelas com o sobrenome do agente:

SELECT s.house_id, a.first_name, a.last_name, a.experience_years
FROM sales s
JOIN agents a
ON s.agent_last_name = a.last_name
ORDER BY s.house_id;

isto é o que você obtém:

Isso não funcionou bem. Temos dois agentes diferentes com o sobrenome Scott: Maria e Sophia. Como resultado, as casas #1015 e #2134 estão incluídas duas vezes cada uma com agentes diferentes.

Para consertar esta consulta, precisamos nos unir ao sales e agents tabelas utilizando dois pares de colunas, correspondentes ao sobrenome e ao primeiro nome do agente:

SELECT s.house_id, a.first_name, a.last_name, a.experience_years
FROM sales s
JOIN agents a
ON s.agent_last_name = a.last_name
   AND s.agent_first_name = a.first_name
ORDER BY s.house_id;

E aqui está o resultado que estávamos procurando.

Embora JOIN seja uma das ferramentas básicas em SQL, você precisa estar ciente das muitas nuances diferentes para unir as tabelas de forma eficaz. Recomendo praticar Cláusulas JOIN em SQL com este curso interativo que cobre uma variedade de cenários de junção com 93 desafios de codificação.

3. Selecionando um sub-conjunto de colunas

Em alguns casos, os registros no conjunto de resultados não são duplicados, mas aparecem como se fossem porque o subconjunto selecionado de colunas não mostra todas as diferenças entre os registros.

Por exemplo, imagine que queremos ver as datas em que cada agente imobiliário vendeu uma casa. Se utilizarmos a seguinte consulta:

SELECT a.first_name, a.last_name, s.date
FROM agents a
JOIN sales s
ON s.agent_last_name = a.last_name
   AND s.agent_first_name = a.first_name;

ela produz o seguinte resultado:

O conjunto de resultados inclui dois registros com Alexandr McGregor que parecem idênticos. Entretanto, se você adicionar a identificação da casa à declaração SELECT, você verá que estes dois registros correspondem à venda de duas casas diferentes no mesmo dia.

Se você não estiver interessado nestas informações adicionais e quiser ter apenas uma fila exibida aqui, use DISTINCT:

SELECT DISTINCT a.first_name, a.last_name, s.date
FROM agents a
JOIN sales s
ON s.agent_last_name = a.last_name
   AND s.agent_first_name = a.first_name;

Agora, o resultado é:

4. Listando apenas as linhas de correspondência

Um problema semelhante pode ocorrer se você quiser listar apenas as linhas de uma tabela, mas há vários registros correspondentes na outra tabela. Você acaba com duplicatas indesejadas em seu conjunto de resultados.

Por exemplo, digamos que queremos listar todos os clientes que compraram casas através de nossa agência. Se utilizarmos a seguinte consulta:

SELECT c.first_name, c.last_name, c.email
FROM customers c
JOIN sales s
ON c.id = s.customer_id;

aqui está o resultado:

Como você vê, a tabela resultante inclui Ivan Lee duas vezes. Isto porque ele comprou duas casas e há dois registros correspondentes na tabela sales mesa. Uma solução possível é usar DISTINCT, como no exemplo anterior. Uma solução ainda melhor é evitar o uso de SQL JOIN, filtrando o conjunto de resultados usando a palavra-chave EXISTS:

SELECT c.first_name, c.last_name, c.email
FROM customers c
WHERE EXISTS (SELECT customer_id FROM sales);

Agora, o resultado é:

Isto lhe dá o resultado desejado e também torna a intenção de sua consulta mais clara.

5. Usando a Auto-Adesão

Finalmente, as duplicatas indesejadas em JOINs freqüentemente resultam de uma especificação incorreta das condições de união em auto-adesões - ou seja, quando uma mesa é unida a si mesma.

Digamos que queremos que nossos agentes formem pares para nosso próximo treinamento. Obviamente, não queremos que nenhum agente seja emparelhado consigo mesmo. Portanto, podemos especificar a condição ON a1.id <> a2.id :

SELECT
    a1.first_name as agent1_first_name,
    a1.last_name as agent1_last_name,
    a1.experience_years as agent1_experience,
    a2.first_name as agent2_first_name,
    a2.last_name as agent2_last_name,
    a2.experience_years as agent2_experience
FROM agents a1
JOIN agents a2
ON a1.id <> a2.id
ORDER BY a1.id;

No entanto, esta consulta sai duas vezes a cada par. Por exemplo, na primeira linha da tabela abaixo, Kate White é considerada Agente 1, e Maria Scott é considerada Agente 2. Mas mais perto do final da tabela, obtém-se o mesmo par de agentes, mas com Maria Scott como Agente 1, e Kate White como Agente 2.

Para resolver este problema, você precisa acrescentar uma condição explícita para incluir cada par apenas uma vez. Uma solução comum é especificar a condição de união a1.id < a2.id. Com isto, você obtém o par Kate White e Maria Scott, mas não o contrário. Isto porque o ID da Kate (1) é um número inferior ao ID da Maria (6).

Na prática, você pode ter algumas outras condições para a junção dos agentes. Por exemplo, você pode querer emparelhar agentes mais experientes (3+ anos) com agentes menos experientes (< 3 anos). A condição de filtragem correspondente em WHERE resolve o problema:

SELECT
    a1.first_name as agent1_first_name,
    a1.last_name as agent1_last_name,
    a1.experience_years as agent1_experience,
    a2.first_name as agent2_first_name,
    a2.last_name as agent2_last_name,
    a2.experience_years as agent2_experience
FROM agents a1
JOIN agents a2
ON a1.id <> a2.id
WHERE a1.experience_years>=3 AND a2.experience_years < 3
ORDER BY a1.id;

O resultado é o seguinte:

Este conjunto de resultados parece muito melhor e facilita a seleção de três pares, cada um consistindo de um agente com mais experiência e outro com menos experiência.

Vamos praticar Cláusulas JOIN em SQL!

Juntar tabelas em SQL não é tão difícil. Mas isso requer muita prática. Se você quiser evitar armadilhas como duplicatas indesejadas em JOINs e registros faltantes, siga este guia sobre como praticar Cláusulas JOIN em SQL.

Se você tem apenas experiência básica com SQL e quer combinar dados de várias tabelas com mais confiança, eu recomendo isto Cláusulas JOIN em SQL curso interativo. Ele cobre todos os principais tipos de JOINs, bem como unir uma tabela consigo mesmo, unindo várias tabelas em uma consulta, e unindo tabelas em colunas não-chave. Obtenha mais detalhes sobre este curso neste artigo de visão geral.

Bônus. Aqui estão as 10 principais perguntas da entrevista SQL JOIN com respostas.

Obrigado por ler, e feliz aprendizado!