Aprendiendo a hacer JOINs con Ejemplos de SQL del Mundo Real

La sentencia JOIN le permite trabajar con datos almacenados en múltiples tablas. En este artículo, le guiaré a través del tema de las cláusulas JOIN utilizando ejemplos de SQL del mundo real.

Imagina que sólo pudieras trabajar con una tabla de la base de datos a la vez. Afortunadamente, esto no es algo de lo que tengamos que preocuparnos. Una vez que aprendas la sentencia JOIN, podrás empezar a enlazar datos. En este artículo, usaré ejemplos de SQL del mundo real que ilustran cómo usamos JOINs, cómo funciona cada tipo de JOIN y cuándo usar cada tipo. Además, compartiré algunos consejos que le ayudarán a evitar cometer errores comunes al usar JOINs.

¿Qué es la cláusula JOIN de SQL?

La cláusula JOIN nos permite combinar las columnas de dos o más tablas basándose en valores de columnas compartidas. Lo explicaré usando ejemplos de SQL en tiempo real. Este es el primero: digamos que tenemos una tabla students que contiene los nombres de los estudiantes, sus respectivos nombres de usuario y un número de identificación. También tenemos una tabla "comentarios" que almacena cualquier comments que los estudiantes hayan publicado en un foro. Estas son las dos tablas.

Añadamos algunos datos de prueba:

INSERT INTO `students` VALUES
(1,'Jhon','jj2005'),(2,'Albert','salbert'),(3,'Mathew','powermath'),
(4,'Lisa','lisabbc'),(5,'Sandy','imsandyw'),(6,'Tamara','tamy21');

INSERT INTO 'comments' VALUES 
(1,'jj2005','Awesome!'),(2,'jj2005','This is great :)'),
(3,'powermath','Hmmm...'),(4,'imsandyw','Let\'s wait a moment'),
(5,'lisabbc','Sure thing'),(6,'lisabbc','wow!'),
(7,'lisabbc','lol :) :) :)');

Como puede ver, ambas tablas tienen la columna forum_username en común. Por lo tanto, esta columna puede utilizarse en una sentencia JOIN para relacionar las dos tablas.

Por ejemplo, si quisiéramos saber el nombre real del estudiante para cada uno de los comentarios del foro, escribiríamos esta consulta JOIN:

SELECT students.name, comments.forum_username, comments.comment
FROM students 
INNER JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

Los resultados se verían así:

Nota: He utilizado a propósito forum_username para ilustrar el concepto de JOIN, pero en la práctica se utilizaría la clave primaria (en este caso, la columna id de la tabla students ) para relacionar las tablas.

Conociendo los tipos de JOIN

Existen varios tipos de JOINs. Vamos a repasarlos rápidamente:

INNER JOIN: Este JOIN devuelve los registros que tienen una coincidencia en ambas tablas según el predicado de unión (que viene después de la palabra clave ON ). Se trata del mismo JOIN que utilizamos en el ejemplo anterior. La palabra clave INNER es opcional.

LEFT [OUTER] JOIN: Esta opción devolverá todos los registros de la tabla de la izquierda (es decir, la tabla que aparece en primer lugar en JOIN) y sólo los registros coincidentes de la tabla de la derecha (es decir, la segunda). La palabra clave OUTER es opcional.

Volviendo a nuestros ejemplos de

SELECT students.name, comments.forum_username, comments.comment
FROM students 
LEFT JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

Este LEFT JOIN devolvería un registro para todos los estudiantes - incluyendo a Tamara y Albert, quienes no tienen ningún comentario y no estarían listados en los resultados de un INNER JOIN. Observe también que en este ejemplo de LEFT JOIN, los resultados de las columnas de Tamara y Albert comment serán NULL.

RIGHT [OUTER] JOIN: Es la inversa de LEFT JOIN; devuelve todos los registros de la tabla derecha (segunda) y sólo los que coinciden con los de la tabla izquierda (primera).

Una consulta similar a la del ejemplo

SELECT 
  students.name,
  comments.forum_username,
  comments.comment
FROM students 
RIGHT JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

FULL [OUTER] JOIN: Se trata esencialmente de la combinación de un LEFT JOIN y un RIGHT JOIN. El conjunto de resultados incluirá todas las filas de ambas tablas, rellenando las columnas con los valores de la tabla cuando sea posible o con NULLs cuando no haya ninguna coincidencia en la tabla homóloga. Este no es un JOIN que vaya a utilizar muy a menudo en la vida real. Nota: MySQL carece de esta sentencia, pero se puede conseguir un resultado similar utilizando la UNION de LEFT JOIN y RIGHT JOIN.

En el caso de nuestros ejemplos SQL del mundo real, vale la pena notar que en la siguiente cláusula FULL JOIN estamos suministrando la palabra clave ON como lo hacemos en LEFT o RIGHT JOINs :

SELECT *
FROM students 
FULL OUTER JOIN comments
  ON students.forum_username = comments.forum_username
ORDER BY students.name ASC;

CROSS JOIN: Este es otro tipo de unión que no usará muy a menudo - en este caso, porque recupera el producto cartesiano de ambas tablas. Básicamente, esto le da la combinación de todos los registros de ambas tablas. CROSS JOIN no aplica un predicado (no hay una palabra clave ON ), pero todavía es posible filtrar filas usando WHERE. Al hacer esto, el conjunto de resultados podría ser equivalente a un INNER JOIN. En MySQL, JOIN, CROSS JOIN, y INNER JOIN son equivalentes sintácticos, es decir, pueden sustituirse entre sí.

A continuación, no hay ninguna cláusula ON para filtrar los resultados. Todas las combinaciones posibles de ambas tablas se mostrarán en el conjunto de resultados:

SELECT *
FROM students 
CROSS JOIN comments 
ORDER BY students.name ASC;

Puedes aprender más sobre los tipos de Tipos de JOIN en SQL en nuestro canal de YouTube - We Learn SQL. Recuerda hacer clic en suscribirte.

Ejemplos de JOINs en SQL en tiempo real

El número de escenarios que requieren un JOIN es interminable, pero algunos escenarios aparecen más a menudo. En lugar de repasar el típico ejemplo de tabla1/tabla2, prefiero presentarle ejemplos de SQL del mundo real. Podemos usarlos para obtener algunos consejos prácticos.

La Relación Abuelo, Padre e Hijo

Un escenario común en tiempo real trata con datos que siguen este tipo de relación. Por ejemplo, un user se encuentra en un city que pertenece a un state. Las tablas (con más o menos columnas) se ven algo así:

Vamos a añadir algunos datos:

INSERT INTO `states` VALUES (3,'California'),(2,'Florida'),(1,'Nevada');

INSERT INTO `cities` VALUES (1,1,'Miami'),(2,1,'Orlando'),
(3,2,'Las Vegas'),(4,2,'Coyote Springs');

INSERT INTO `users` VALUES (1,1,'Jhon','Doe'),(2,1,'Albert','Thomson'),
(3,2,'Robert','Ford'),(4,3,'Samantha','Simpson');

Para obtener la lista completa de usuarios en una ciudad y estado determinados tendremos que unir la tabla de hijos (User) con su padre (City) y su abuelo (State).

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id;

Ya tenemos un par de consejos útiles:

• Las columnas utilizadas para enlazar las tablas deben ser indexadas para un mayor rendimiento.
• Cuando las columnas que enlazan las tablas (como el ejemplo anterior) apuntan a la clave primaria de la tabla relacionada, entonces estamos hablando de claves foráneas. En este caso, es mejor incluir esta relación como parte de la definición de la tabla; aumentará el rendimiento. En MySQL, puede crear una clave foránea usuarios/ciudad de la siguiente manera:

  ALTER TABLE `users` 
  ADD INDEX `fk_city_idx` (`city_id` ASC);
  ALTER TABLE `users` 
  ADD CONSTRAINT `fk_city`
    FOREIGN KEY (`city_id`)
    REFERENCES `cities` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;
  

  Esto le proporciona el beneficio adicional de la comprobación de integridad que realizará el motor cuando se actualicen o eliminen los datos de estas tablas.

Supongamos que queremos encontrar todos los usuarios de un estado. Se añade una condición de filtrado a la consulta, como se muestra a continuación:

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id
  AND State.statename = 'Nevada';

O incluso puede utilizar una unión implícita (mostrada en negrita), como ésta:

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM
   users User,
   cities City,
   states State
WHERE User.city_id = City.id
  AND City.state_id = State.id
  AND State.statename = 'Nevada';

Pero le sugiero que escriba explícitamente el JOIN y mantenga separados los criterios de unión y las condiciones de filtrado:

SELECT 
  User.first_name,
  user.last_name,
  City.cityname,
  State.statename
FROM users User
INNER JOIN cities City
  ON User.city_id = City.id
INNER JOIN states State
  ON City.state_id = State.id
WHERE State.statename = 'Nevada';

Un par de conceptos importantes:

• Podemos ver cómo la tabla hijo (user) se filtra en función de las condiciones utilizadas en la tabla del abuelo (state). Es decir, los usuarios se basaron en un estado determinado. Del mismo modo, podríamos haber filtrado los resultados basándonos en la tabla del padre (city) y obtener una lista de usuarios basada en una ciudad determinada.
• Una regla general es que los predicados de JOIN (las condiciones después de la palabra clave ON ) deben usarse sólo para la relación de unión. Deje el resto de las condiciones de filtrado dentro de la sección WHERE. Esto simplificará la legibilidad de la consulta y el mantenimiento futuro del código.

A la inversa, podríamos devolver estados en función de ciertos criterios. Por ejemplo, podríamos recuperar los estados que tienen al menos un usuario que pertenece a ese estado:

SELECT 
  DISTINCT State.statename
FROM states State
INNER JOIN cities City
  ON City.state_id = State.id
INNER JOIN users User
  ON User.city_id = City.id

¿Qué aprendemos aquí?

• El INNER JOIN elimina los registros "no utilizados" o no coincidentes (registros sin coincidencia en ambos lados del JOIN).
• DISTINCT filtra los registros duplicados. Dado que puede haber varios usuarios para un estado, si no utilizáramos DISTINCT obtendríamos tantos estados repetidos como usuarios pertenecientes a él. (El mismo efecto de filtrado podría conseguirse también utilizando GROUP BY).
• Se podría conseguir un mayor filtrado añadiendo las condiciones de WHERE.

La relación muchos-a-muchos

¿Quieres más ejemplos de SQL en tiempo real? Otro escenario típico para JOINs es cuando los registros se relacionan entre sí de una manera "muchos-a-muchos" o N-a-N. Digamos que tiene un sistema en el que crea insignias que se otorgan a los usuarios. En este caso, un usuario tiene insignias y al mismo tiempo una insignia tiene usuarios. Estas relaciones necesitarán una tercera tabla que conecte las claves primarias de users y badges. Se vería algo así:

Añadamos algunos datos de muestra:

INSERT INTO `badges` VALUES 
(1,'gold heart',100),(2,'silver heart',50),(3,'metal heart',10),(4,'star',5),(5,'wood',2),(6,'dust',1);
INSERT INTO `users` VALUES (1,'Robert','Williams'),(2,'Anthony','McPeters'),(3,'Tania','Krugger'),(4,'JJ','Richards'),(5,'Katy','Thomas');
INSERT INTO `badges_users` VALUES
(1,1,1),(2,4,1),(3,4,2),(4,4,2),(5,4,2),(6,3,3),(7,3,3),(8,2,4);

¿Cómo puede un JOIN buscarnos todos los usuarios con sus respectivas insignias?

SELECT 
  User.first_name,
  User.last_name,
  BU.user_id,
  BU.badge_id,
  Badge.badge_name,
  Badge.badge_points
FROM users User
LEFT JOIN badges_users B
  ON User.id = BU.user_id
LEFT JOIN badges Badge
  ON BU.badge_id = Badge.id
ORDER BY Badge.badge_points DESC

Hay que tener en cuenta algunas cosas sobre este tipo de consulta:

• Aquí hemos utilizado LEFT JOIN a propósito porque mostrará a los usuarios que no tienen ninguna insignia. Si hubiéramos utilizado un INNER JOIN o un inner join implícito (estableciendo las igualdades del ID en un WHERE), los usuarios que no tienen insignias no se incluirían en los resultados. Si quiere excluir a estos usuarios, debe utilizar un INNER JOIN.
• Además, el uso de un LEFT JOIN significa que las insignias no utilizadas no aparecerán en la lista; nos estamos centrando en los usuarios y no en las insignias.
• Por último, recuerde indexar correctamente la tabla intermedia (badges_users). Todas sus claves externas deben estar definidas.

Ahora vamos a coger todas las insignias que tienen al menos un usuario. Expresado de otra manera, se trata de insignias que han sido utilizadas al menos una vez

SELECT DISTINCT Badge.badge_name
FROM badges Badge
LEFT JOIN badges_users BadgeUser
  ON Badge.id = BadgeUser.badge_id

Y si quisiéramos obtener todas las insignias no utilizadas, la consulta se convierte en:

SELECT Badge.badge_name, Badge.badge_points
FROM badges Badge
LEFT JOIN badges_users BadgeUser
  ON Badge.id = BadgeUser.badge_id
WHERE BadgeUser.badge_id IS NULL

Fíjate que:

• No siempre es necesario unir las tres tablas. Si sólo nos unimos a la tabla intermedia (badges_users), que contiene las referencias a las tablas user y badge podemos seguir ejecutando nuestras consultas con éxito.
• La tabla intermedia también podría utilizarse para guardar información adicional. Por ejemplo, podría almacenar el valor de la marca de tiempo cuando un usuario recibió una determinada insignia.

Pruébelo usted mismo

Es probable que te enfrentes a diario a este tipo de situaciones al tratar con tablas que contienen datos relacionados. Le recomiendo encarecidamente que examine los ejemplos de SQL del mundo real anteriores y los pruebe con los datos reales. Esto le dará una comprensión más clara de los conceptos involucrados.

Si tiene algún comentario sobre nuestros ejemplos de SQL del mundo real o sus propias ideas de otras cláusulas JOIN de la vida real, por favor siéntase libre de compartirlo para que todos podamos seguir aprendiendo.