Cómo conservar las filas no coincidentes de dos tablas en un JOIN de SQL

¿Quiere unir dos tablas en SQL sin eliminar las filas no coincidentes? ¿Quiere mantener las filas no coincidentes de una o ambas tablas? En este artículo, le explicaré cómo mantener todos los registros que desee utilizando los JOINs externos, tales como LEFT JOIN, RIGHT JOIN y FULL JOIN. Se incluyen ejemplos.

El SQL JOIN es una poderosa herramienta que le ayuda a combinar datos de múltiples tablas de su base de datos. Esta es una idea central detrás de las bases de datos relacionales: almacenar datos en diferentes tablas interrelacionadas y combinar los datos de estas tablas cuando sea necesario para el análisis de datos y la elaboración de informes. Si necesita una recapitulación sobre la unión de tablas en SQL, consulte esta guía para principiantes y nuestro curso interactivo Tipos de JOIN en SQL interactivo.

Desafortunadamente, cuando está empezando con SQL, las consultas JOIN pueden producir resultados frustrantes. Por ejemplo, puede obtener duplicados, datos faltantes, valores NULL inesperados, etc. En este artículo, quiero centrarme en el caso en el que usted quiere mantener las filas no coincidentes de dos tablas pero un JOIN de SQL las elimina. Veremos por qué sucede esto y cómo obtener la salida que se espera.

Comencemos con un ejemplo.

Digamos que está planeando una campaña de marketing en la que da un bono especial a un conjunto de sus clientes. Hay varios criterios para que un cliente sea elegible para la bonificación. Para esta campaña en particular, usted busca clientes que tengan pedidos con el estado "Completado" del último mes pero que no hayan ganado en su campaña de marketing anterior.

Quiere combinar la información de las tres tablas siguientes:

Puede utilizar la siguiente consulta para unir estas tablas y obtener una lista de clientes con la información adicional que necesita:

SELECT 
  c.id, 
  c.first_name, 
  c.last_name, 
  o.order_status, 
  lcp.winner
FROM customers c
JOIN orders o
ON c.id = o.customer_id
JOIN last_campaign_participants lcp
ON c.id = lcp.customer_id;

Si no está seguro de cómo funciona esta consulta, consulte nuestro curso interactivo con 93 desafíos de codificación que cubren diferentes tipos de JOINs.

Aquí está el resultado de la consulta SQL anterior:

Como puede ver, el resultado incluye sólo dos de los cuatro clientes. Esto se debe a que sólo estos dos clientes tienen registros correspondientes en las tres tablas, y JOIN o INNER JOIN da como resultado sólo las filas coincidentes. Con este resultado, sólo vemos un cliente elegible para la siguiente campaña: Kate Wilson (ID 101). La otra fue la ganadora de nuestra última campaña y, por tanto, no es elegible para esta campaña.

Pero, ¿podemos pasar por alto a alguien eliminando las filas no coincidentes? En realidad, sí. Podemos pasar por alto a los clientes que han completado pedidos pero que no han participado en la última campaña. Estos clientes faltan en esta tabla. Queremos todos los clientes en la salida del JOIN de SQL, y luego filtrar los resultados según sea necesario.

Afortunadamente, SQL tiene JOINs externos que le permiten mantener las filas no coincidentes de dos tablas.

Inner JOINs vs. Outer JOINs

En contraste con INNER JOIN, o simplemente JOIN, que sólo devuelve las filas coincidentes de dos tablas, los JOINs externos también devuelven las filas no coincidentes en SQL. Hay varios tipos de JOINs externos:

• Un LEFT JOIN devuelve todos los registros de la tabla izquierda (primera) aunque no haya coincidencias en la tabla derecha (segunda).
• Un RIGHT JOIN devuelve todos los registros de la tabla derecha (segunda) aunque no haya coincidencias en la tabla izquierda (primera).
• Una FULL JOIN devuelve todos los registros de ambas tablas, incluidos los que no coinciden en ninguna de ellas.

Este artículo explica estos tipos de JOIN de SQL con más detalle, con ejemplos e ilustraciones. También, vea esta gran hoja de trucos deTipos de JOIN en SQL para obtener todos los matices de la sintaxis.

Para usar un JOIN externo, sólo tiene que reemplazar la palabra clave JOIN con la palabra clave LEFT JOIN, RIGHT JOIN, o FULL JOIN dependiendo de su caso. Sin embargo, tenga en cuenta que para LEFT JOIN y RIGHT JOIN, el orden de las tablas en la consulta SQL importa.

Ahora, modifiquemos nuestra primera consulta para mantener las filas no emparejadas y obtener todos los clientes en la salida. Una opción es utilizar LEFT JOIN asegurándose de que la tabla customers aparezca primero en la consulta (es decir, justo después de la palabra clave FROM ):

SELECT 
  c.id, 
  c.first_name, 
  c.last_name, 
  o.order_status, 
  lcp.winner
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
LEFT JOIN last_campaign_participants lcp
ON c.id = lcp.customer_id;

La salida de esta consulta incluye una lista de todos los clientes actuales con la información adicional correspondiente de las otras dos tablas. Tenga en cuenta que si ningún registro coincide con el cliente de la tabla de la izquierda, estos registros se siguen incluyendo pero con los valores de NULL en las respectivas columnas:

Al mantener las filas no coincidentes con un LEFT JOIN, detectamos otro cliente que puede participar en la campaña. En concreto, María White (ID 102) ha completado pedidos y no participó en la última campaña, por lo que obviamente no ganó.

Para comprender mejor los JOIN externos, veamos algunos ejemplos más.

Ejemplos de Outer JOIN

Ahora, imagine que tenemos una librería. Queremos comparar dos grupos de clientes: los que compraron Harry Potter y la Piedra Filosofal y los que compraron Harry Potter y la Cámara de los Secretos. ¿Son las mismas personas? ¿Hay alguien que haya comprado un libro y no otro? Tal vez queramos recomendarles el otro libro.

Ejemplo con LEFT JOIN

En primer lugar, queremos ver todas las personas que nos han comprado Harry Potter y la Piedra Filosofal y comprobar si también han comprado Harry Potter y la Cámara de los Secretos.

Para obtener este resultado, utilizamos LEFT JOIN con una lista de los que compraron el primer libro incluida en la cláusula FROM de la consulta (tabla izquierda).

SELECT 
  ps.product_id, 
  ps.first_name, 
  ps.last_name, 
  cs.product_id, 
  cs.first_name, 
  cs.last_name
FROM philosophers_stone ps
LEFT JOIN chamber_of_secrets cs
ON ps.customer_id = cs.customer_id;

Esto nos permite conservar todos los compradores de Harry Potter y la piedra filosofal aunque no haya registros coincidentes en la segunda tabla:

Ahora vemos que dos clientes compraron el primer libro de la serie pero no el segundo. Podemos recomendarles el segundo libro en nuestra próxima campaña de marketing.

Ejemplo con FULL JOIN

¿Pero qué pasa si hay clientes que han comprado sólo el segundo libro pero no el primero? También sería bueno saberlo.

En lugar de escribir una consulta separada para detectar este grupo de clientes, es mejor utilizar FULL JOIN que nos proporciona la imagen completa: los que compraron sólo el primer libro, los que compraron sólo el segundo libro y los que compraron ambos libros:

SELECT 
  ps.product_id, 
  ps.first_name, 
  ps.last_name, 
  cs.product_id, 
  cs.first_name, 
  cs.last_name
FROM philosophers_stone ps
FULL JOIN chamber_of_secrets cs
ON ps.customer_id = cs.customer_id;

¡Estupendo! Ahora podemos comparar fácilmente dos grupos de clientes y encontrar oportunidades de buenas recomendaciones de libros. ¡Los JOINs externos pueden ser súper útiles!

¡Es hora de practicar Tipos de JOIN en SQL!

Unir tablas es una de las habilidades fundamentales requeridas para el uso efectivo de SQL en el análisis de datos y la elaboración de informes. No es tan difícil, pero se necesita mucha práctica con Tipos de JOIN en SQL para evitar escollos como registros perdidos y duplicados inesperados.

Recomiendo empezar con el Tipos de JOIN en SQL curso interactivo. Cubre todos los tipos clave de JOINs, uniendo una tabla consigo misma, uniendo múltiples tablas en una consulta, y uniendo 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 la entrevista de SQL JOIN con respuestas.

Gracias por leer y ¡feliz aprendizaje!