¿Cómo deshacerse de los duplicados en un JOIN SQL?

¿Tiene duplicados no deseados en su consulta SQL JOIN? En este artículo, hablaré de las posibles razones por las que se obtienen duplicados después de unir tablas en SQL y mostraré cómo arreglar una consulta dependiendo de la razón detrás de los duplicados.

Los analistas de datos con poca experiencia en Tipos de JOIN en SQL a menudo encuentran duplicados no deseados en el conjunto de resultados. Para los principiantes es un reto identificar la razón detrás de estos duplicados en los JOINs.

La mejor manera de aprender Tipos de JOIN en SQL es mediante la práctica. Recomiendo el curso interactivo Tipos de JOIN en SQL curso interactivo. Contiene más de 90 ejercicios que te hacen practicar los diferentes tipos de JOIN en SQL.

En este artículo, discutiré los problemas más comunes que conducen a los duplicados en los resultados de los JOINs de SQL. También mostraré las posibles soluciones a estos problemas comunes.

Comencemos con una breve descripción de Tipos de JOIN en SQL.

Tipos de JOIN en SQL Visión general

JOIN es una construcción SQL para solicitar información de dos o más tablas dentro de la misma consulta.

Por ejemplo, digamos que tienes una lista de las 100 mejores películas del siglo XX, y quieres hacer un subconjunto de las películas hechas por directores que viven actualmente. En su movies no tiene información detallada sobre los directores de cine, sino sólo sus identificaciones. Pero tiene una tabla separada directors con el ID, el nombre completo, el año de nacimiento y el año de fallecimiento (si procede) de cada director.

En su consulta, puede unir dos tablas por el ID del director para obtener una lista de las películas realizadas por los directores que viven actualmente:

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

Como puedes ver, especificamos las tablas que queremos unir en las cláusulas FROM y JOIN. Luego, en la cláusula ON, especificamos las columnas de cada tabla que se utilizarán para unir estas tablas. Si usted es nuevo en Tipos de JOIN en SQL, revise esta guía introductoria. Aquí también hay una hoja de trucos de SQL JOIN con sintaxis y ejemplos de diferentes JOINs.

El SQL JOIN es una gran herramienta que proporciona una variedad de opciones más allá de la simple unión de dos tablas. Si no está familiarizado con los tipos de JOIN de SQL, lea este artículo que los explica con ilustraciones y ejemplos. Dependiendo de su caso de uso, puede elegir INNER JOIN, LEFT JOIN, RIGHT JOIN y FULL JOIN. Incluso puede necesitar unir tablas sin una columna común o unir más de dos tablas.

Ahora, veamos como estos diferentes JOINs pueden resultar en duplicados no deseados.

¿Cuáles son las razones de los duplicados en Tipos de JOIN en SQL?

Hay muchas razones posibles para obtener duplicados en el resultado de su consulta SQL JOIN. Revisaré las 5 razones principales; para cada una, mostraré una consulta de ejemplo con el problema y una consulta corregida para obtener un resultado sin duplicados.

Empecemos por repasar brevemente los datos que vamos a utilizar en nuestros ejemplos. Imaginemos que tenemos una agencia inmobiliaria que vende casas en algún lugar de Estados Unidos. Tenemos tablas con agents, customers, y sales. Vea a continuación qué datos se almacenan en cada tabla.

Sin más dilación, pasemos a nuestros ejemplos.

1. Falta la condición ON

Los principiantes que no están familiarizados con Tipos de JOIN en SQL suelen limitarse a enumerar las tablas en FROM sin especificar en absoluto la condición JOIN cuando intentan combinar información de dos o más tablas. Esta es una sintaxis válida, por lo que no se obtiene ningún mensaje de error. Pero el resultado es una unión cruzada con todas las filas de una tabla combinadas con todas las filas de otra tabla.

Por ejemplo, supongamos que queremos obtener información sobre el cliente que compró una casa en particular (ID #2134). Si utilizamos la siguiente consulta:

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

Este es el resultado que obtenemos:

En lugar de un registro con el cliente que queremos, tenemos todos los clientes listados en el conjunto de resultados.

Para arreglar la consulta, se necesita una sintaxis explícita de JOIN. Las tablas a combinar se especifican en FROM y JOIN, y la condición de unión se especifica en la 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;

Aquí, especificamos el ID de cliente de la tabla sales para que coincida con el ID de cliente de la tabla customers de la tabla. Esto nos da el resultado deseado:

Se podría especificar la condición de unión en la cláusula WHERE para obtener el mismo resultado. Pero eso va en contra del uso previsto de la cláusula WHERE. Además, existen beneficios adicionales al utilizar la sintaxis JOIN en lugar de listar las tablas en FROM. Consulte este artículo para entender por qué se prefiere la sintaxis de JOIN.

2. Uso de una condición ON incompleta

Las filas no deseadas en el conjunto de resultados pueden provenir de condiciones ON incompletas. En algunos casos, es necesario unir tablas por varias columnas. En estas situaciones, si se utiliza sólo un par de columnas, se obtienen filas duplicadas.

Digamos que queremos ver el nivel de experiencia del agente inmobiliario para cada casa vendida. Si empezamos uniendo las tablas sales y agents por el apellido del 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;

Esto es lo que se obtiene:

Eso no funcionó bien. Tenemos dos agentes diferentes con el apellido Scott: María y Sofía. Como resultado, las casas #1015 y #2134 están incluidas dos veces con diferentes agentes.

Para solucionar esta consulta, tenemos que unir las tablas sales y agents utilizando dos pares de columnas, correspondientes al apellido y al nombre del 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;

Y aquí está el resultado que buscábamos.

Aunque el JOIN es una de las herramientas básicas de SQL, es necesario conocer los diferentes matices para unir tablas de forma efectiva. Recomiendo practicar Tipos de JOIN en SQL con este curso interactivo que cubre una variedad de escenarios de unión con 93 desafíos de codificación.

3. Seleccionar un subconjunto de columnas

En algunos casos, los registros del conjunto de resultados no son duplicados pero parecen serlo porque el subconjunto de columnas seleccionado no muestra todas las diferencias entre los registros.

Por ejemplo, imaginemos que queremos ver las fechas en que cada agente inmobiliario vendió una casa. Si utilizamos la siguiente 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;

se obtiene el siguiente resultado:

El conjunto de resultados incluye dos registros con Alexandr McGregor que parecen idénticos. Sin embargo, si se añade el ID de la casa a la consulta SELECT, se ve que estos dos registros corresponden a la venta de dos casas diferentes en el mismo día.

Si no le interesa esta información adicional y quiere que sólo se muestre una fila, utilice 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;

Ahora, el resultado es:

4. Listado de filas coincidentes solamente

Un problema similar puede ocurrir si quiere listar sólo las filas de una tabla pero hay varios registros coincidentes en la otra tabla. En este caso, el conjunto de resultados se verá afectado por duplicados no deseados.

Por ejemplo, digamos que queremos listar todos los clientes que compraron casas a través de nuestra agencia. Si utilizamos la siguiente consulta

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

este es el resultado:

Como ves, la tabla resultante incluye a Ivan Lee dos veces. Esto se debe a que compró dos casas y hay dos registros correspondientes en la tabla sales tabla. Una posible solución es utilizar DISTINCT como en el ejemplo anterior. Una solución aún mejor es evitar el uso de SQL JOIN en absoluto, filtrando el conjunto de resultados mediante la palabra clave EXISTS:

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

Ahora, el resultado es:

Esto le da la salida deseada y también hace que la intención de su consulta sea más clara.

5. Uso de autouniones

Por último, los duplicados no deseados en los JOINs suelen ser el resultado de una especificación incorrecta de las condiciones de unión en los self joins - es decir, cuando una tabla se une consigo misma.

Digamos que queremos que nuestros agentes formen parejas para nuestro próximo entrenamiento. Obviamente, no queremos que ningún agente se empareje consigo mismo. Así que podríamos especificar la condición 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;

Sin embargo, esta consulta muestra cada pareja dos veces. Por ejemplo, en la primera fila de la tabla que aparece a continuación, Kate White se considera el agente 1 y Maria Scott el agente 2. Pero más cerca del final de la tabla, se obtiene el mismo par de agentes pero con María Scott como Agente 1 y Kate White como Agente 2.

Para resolver este problema, es necesario añadir una condición explícita para incluir cada par sólo una vez. Una solución común es especificar la condición de unión a1.id < a2.id. Con esto, se obtiene el par Kate White y Maria Scott, pero no a la inversa. Esto se debe a que el ID de Kate (1) es un número menor que el ID de María (6).

En la práctica, puede tener otras condiciones para emparejar a los agentes. Por ejemplo, puede querer emparejar a los agentes más experimentados (3+ años) con los menos experimentados (< 3 años). La condición de filtrado correspondiente en WHERE resuelve el 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;

Este es el resultado:

Este conjunto de resultados se ve mucho mejor y facilita la selección de tres parejas, cada una de ellas formada por un agente con más experiencia y otro con menos experiencia.

¡Practiquemos Tipos de JOIN en SQL!

Unir tablas en SQL no es tan difícil. Pero requiere mucha práctica. Si quiere evitar trampas como duplicados no deseados en JOINs y registros perdidos, siga esta guía para practicar Tipos de JOIN en SQL.

Si sólo tiene una experiencia básica con SQL y quiere combinar datos de varias tablas con más seguridad, le recomiendo este Tipos de JOIN en SQL curso interactivo. Cubre todos los tipos principales de JOINs así como la unión de una tabla consigo misma, la unión de múltiples tablas en una consulta y la unión de tablas en columnas no clave. Obtenga más detalles sobre este curso en este artículo de resumen.

Bonificación. Aquí están las 10 mejores preguntas de entrevista de SQL JOIN con respuestas.

Gracias por leer y ¡feliz aprendizaje!