JOIN SQL: ¿Qué son y cómo funcionan INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN y CROSS JOIN?

Para comprender esta clase a la perfección, te recomiendo que des un repaso a estos vídeos, no olvides en suscribirte a mi canal de YouTube:

• Modelo relacional
• Álgebra relacional
• Lenguaje SQL
• SELECT SQL

⚠️ NOTA: recuerda que SQL Server utiliza el lenguaje Transact-SQL (T-SQL) el cual es una variante mejorada del lenguaje SQL, por lo tanto, el comando JOIN lo intentaré hacer lo más apegado a SQL, pero, si hay que profundizar, lo haré tomando en cuenta la sintaxis que proporciona Transact-SQL.

 

JOIN SQL: ¿Cómo seleccionar datos de dos o más tablas en SQL Server?

Seleccionar información dentro de una base de datos pareciera ser algo simple, no obstante, crear consultas (querys) de selección, requiere conocer muchas clausulas que nos ayude a operar nuestros datos, por ejemplo: debemos conocer como funciona la clausula SELECT y, entender como podemos agregar condicionales, agrupación de datos y manejo de funciones de agregación.

• Todo lo anterior ya lo hemos visto en clases pasadas, así que te recomiendo revisarlas sino estas muy familiarizado con el lenguaje SQL.

Hoy ampliaremos el uso de SELECT a través de la clausula JOIN para crear consultas un poco más complejas de lo que hemos venido estudiando, pero vayamos paso a paso, primero definamos: ¿Qué es JOIN SQL?

 

¿Qué es y para qué sirve JOIN SQL?

A través de la clausula JOIN podemos recuperar datos de dos o más tablas a través de la relación lógica que existe entre ellas.

Lo anterior quiere decir que, a través de un JOIN podemos indicarle a SQL Server como debe tratar los datos de una tabla, para, seleccionar los registros de otra, un JOIN lo definimos a través de una consulta (query) donde:

• Especificamos las columnas de cada tabla que se utilizarán para llevar acabo la combinación de datos.
• Típicamente indicamos la condición de unión entre las diferentes llaves de las tablas en cuestión.
• Definimos los operadores lógicos que se utilizarán en la comparación de valores de las columnas.
• Especificamos los atributos de cada tabla que deseamos consultar.

Tal y como vimos en la clase de álgebra relacional, existen algunos tipos de JOIN, los cuales iremos viendo a lo largo de este post a través de varios ejemplos.

 

Tipos de JOIN SQL

Existen cinco tipos de JOIN principalmente:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• CROSS JOIN

En la clase de álgebra relacional vimos una introducción a este tipo de elementos, te recomiendo repasar nuevamente esa clase. Por lo pronto, veamos ¿Qué es y para qué sirve cada tipo de JOIN SQL?

¿Qué es INNER JOIN y para qué sirve?

La clausula INNER JOIN nos permite seleccionar datos de dos o mas tablas, las cuales, tienen uno o más atributos relacionados lógicamente entre si.

 

Sintaxis INNER JOIN

SELECT *
FROM tabla_a
INNER JOIN tabla_b
ON tabla_a
.nombre_columna = tabla_b.nombre_columna;

La sintaxis es muy simple, y te quedará mucho más clara con el siguiente diagrama.

 

Diagrama de Venn de un INNER JOIN

Un INNER JOIN es similar a lo que en teoría de conjuntos sería una intersección, tal como se ve en la siguiente imagen.

Lo anterior nos indica que un INNER JOIN regresa únicamente los datos que se encuentran en todas las tablas según la columna o columnas que las relaciona.

 

¿Cómo seleccionar datos de dos tablas mediante un INNER JOIN?

Excelente, comencemos nuestros ejemplos utilizando las tablas que hemos creado e insertado datos en las clases pasadas: equipos_gps (Tabla A) y tipos_gps (Tabla B); lo que haremos es seleccionar los equipos GPS y obtendremos los detalles del fabricante de cada uno.

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
INNER JOIN tipos_gps	AS t ON e.cod_gps = t.cod_gps
WHERE
	e.rfc = 'aaaa111111'
;

Del código anterior podemos destacar:

• En cuanto al SELECT, no hay mucho que decir, pues es algo que ya hemos visto en clases anteriores, no obstante, en el ejemplo retornamos todas las columnas de ambas tablas, pero tu puedes indicar que campos quieres regresar.
• En la línea: FROM equipos_gps  AS e
  • Agregamos un alias “e” a equipos_gps.
• La línea importante es: INNER JOIN tipos_gps AS t ON e.cod_gps = t.cod_gps
  • Donde, con INNER JOIN definimos el tipo de unión que deseamos llevar acabo entre: equipos_gps y tipos_gps.
  • A tipos_gps le agregamos un alias “t”.
  • Y, a través de la clausula ON, especificamos la condición que deben reunir las columnas que tienen relación lógica en ambas tablas.
  • Para este caso, la condición lógica que deben cumplir las columnas es una igualdad: e.cod_gps = t.cod_gps
  • Y es la razón por la que agregamos un alias a las tablas, ya que, ambas tablas tienen un atributo con el mismo nombre, y precisamente es el atributo que las relaciona, entonces, para identificar, cual es cual, lo accedemos a través del alias.nombre_atributo.
• En el WHERE simplemente indicamos que queremos consultar los equipos del cliente con rfc = aaaa111111.
• Por ultimo, la consulta regresa un solo registro, como puedes ver en la siguiente imagen.

 

¿Cómo seleccionar datos de múltiples tablas mediante un INNER JOIN?

Bien, el ejercicio anterior creo que ha sido muy fácil de entender, recuerda que un INNER JOIN es prácticamente una intersección, entonces, la unión evaluará todas las tablas involucradas ,y regresará, esa intersección entre ellas, veamos el siguiente ejemplo, donde, agregaremos a nuestra consulta la tabla: tipos_gps2; la cual, es una copia de los valore cod_gps impares de tipos_gps.

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
INNER JOIN tipos_gps	AS t ON e.cod_gps = t.cod_gps
INNER JOIN tipos_gps_2	AS t2 ON e.cod_gps = t2.cod_gps
WHERE
	e.rfc = 'aaaa111111'
;

select * from tipos_gps;
select * from tipos_gps_2;

• Para seleccionar datos desde múltiples tablas, solo basta agregar un INNER JOIN por cada tabla que deseas relacionar en tu consulta, en este caso, he agregado a tipos_gps_2: INNER JOIN tipos_gps_2 AS t2 ON e.cod_gps = t2.cod_gps.
  • En este nuevo INNER JOIN indicamos la misma condición con respecto a la tabla equipos_gps: e.cod_gps = t2.cod_gps.
• Si ejecutamos la consulta nos regresa un sólo registro, ya que, en todas las tablas hay una sola coincidencia que satisface las condiciones, y esto, lo podemos comprobar en la siguiente imagen.

• En todas las tablas el único valor que satisface es cod_gps = 1.

¿Qué es LEFT JOIN y para qué sirve?

Mediante la clausula LEFT JOIN podemos seleccionar todos los registros pertenecientes a la tabla que se encuentra a la izquierda de nuestra consulta, más todos los datos que coinciden en la tabla de la derecha, incluso, si en la tabla de la derecha no existen registros coincidentes.

• Si en la tabla de la derecha no se encuentran registros que coincidan, la consulta regresará valores NULL para rellenar los datos faltantes.

Entonces, la tabla que deseas consultar existan o no datos en las otras tablas, la debes considerar como tu tabla de la izquierda, y las demás, estarán a la derecha.

 

Sintaxis LEFT JOIN

SELECT *
FROM tabla_izquierda
LEFT JOIN tabla_derecha
ON tabla_izquierda

.nombre_columna = tabla_derecha.nombre_columna;

La sintaxis es muy simple, y te quedará mucho más clara con el siguiente diagrama.

 

Diagrama de Venn de un LEFT JOIN

En el diagrama siguiente podemos ver claramente que los datos de la izquierda, más los coincidentes de la derecha, son regresados en la consulta.

¿Cómo seleccionar datos de dos tablas mediante un LEFT JOIN?

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
LEFT JOIN tipos_gps	AS t ON e.cod_gps = t.cod_gps
WHERE
	e.rfc = 'aaaa111111'
;

• Hemos definido a: equipos_gps como tabla de la izquierda y tipos_gps como nuestra tabla de la derecha.
• En LEFT JOIN hemos creado la condición donde e.cod_gps = t.cod_gps.
• Y al ejecutar la consulta, nos regresa lo siguiente.

• Pero ahora ¿Qué pasa si la condición no se cumple y en la tabla de la derecha no hay coincidencia? Para ello, cambiemos la consulta para forzar este caso.

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
LEFT JOIN tipos_gps_2	AS t ON e.cod_gps = t.cod_gps
WHERE
	e.cod_gps = 2
;

• En el WHERE hemos establecido que nos regrese los registros donde e.cod_gps = 2.
• Pero, en tipos_gps_2, ese registro no existe, por lo tanto, la consulta regresará el valor NULL para rellenar los registros faltantes, vemos el resultado.

• Como puedes ver, dado que cod_gps = 2, no existe coincidencia en la tabla de la derecha, la consulta rellena los registros faltantes con NULL.

 

¿Cómo seleccionar datos de múltiples tablas mediante un LEFT JOIN?

Al igual que en nuestro ejemplo con INNER JOIN, debemos definir un LEFT JOIN por tabla que deseamos cruzar, por ejemplo:

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
LEFT JOIN tipos_gps		AS t ON e.cod_gps = t.cod_gps
LEFT JOIN tipos_gps_2	AS t2 ON e.cod_gps = t2.cod_gps
WHERE
	e.cod_gps = 2
;

• La sintaxis es muy sencilla, sólo debes definir tu tabla de la izquierda y mediante LEFT JOIN las tablas de la derecha.
• El resultado se comportará igual que en el primer ejemplo, si no hay coincidencias, regresará valores NULL, tal como se ve en la siguiente imagen.

• Como puedes observar, ahora tenemos los campos de todas las tablas:
  • En tipos_gps: si existen datos y regresa valores.
  • En tipos_gps_2: no existen coincidentes y regresa NULL.

Y listo, ya sabes como hacer un LEFT JOIN en SQL Server, ahora continuemos con más ejemplos.

¿Qué es RIGHT JOIN y para qué sirve?

Este clausula es lo contrario a LEFT JOIN, donde el protagonismo cae en la tabla de la derecha:

Mediante la clausula RIGHT JOIN podemos seleccionar todos los registros pertenecientes a la tabla que se encuentra a la derecha de nuestra consulta, más todos los datos que coinciden en la tabla de la izquierda, incluso, si en la tabla de la izquierda no existen registros coincidentes.

• Si en la tabla de la izquierda no se encuentran registros que coincidan, la consulta regresará valores NULL para rellenar los datos faltantes.

Entonces, la tabla que deseas consultar existan o no datos en las otras tablas, la debes considerar como tu tabla de la izquierda, y las demás, estarán a la derecha.

 

Sintaxis RIGHT JOIN

SELECT *
FROM tabla_izquierda
RIGHT JOIN tabla_derecha
ON tabla_izquierda.nombre_columna = tabla_derecha.nombre_columna;

La sintaxis es muy simple, y te quedará mucho más clara con el siguiente diagrama.

 

Diagrama de Venn de un RIGHT JOIN

En el diagrama siguiente podemos ver claramente que los datos de la izquierda, más los coincidentes de la derecha, son regresados en la consulta.

¿Cómo seleccionar datos de dos tablas mediante un RIGHT JOIN?

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
RIGHT JOIN tipos_gps	AS t ON e.cod_gps = t.cod_gps
WHERE
	t.cod_gps = 2
;

• Como puedes observar, la consulta es prácticamente la misma que en LEFT JOIN, solo he cambiado que regrese los datos donde cod_gps es igual a 2.
• Sin embargo, recuerda que esta línea: RIGHT JOIN tipos_gps AS t ON e.cod_gps = t.cod_gps, nos dice que deseamos regresar los datos donde cod_gps en ambas tablas sean iguales, esta condición influye totalmente en el resultado, si tu la cambias, obtendrás resultados completamente diferente a lo que estoy haciendo en este momento.
• Veamos el resultado de la ejecución en la siguiente imagen.

• Quizá en el anterior resultado no se notan muchas diferencias, por lo tanto, que pasa si en nuestra tabla de la izquierda no hay coincidencias, forcemos este caso, con la siguiente consulta, donde, ahora solicitamos que cod_gps es igual a 4.

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
RIGHT JOIN tipos_gps	AS t ON e.cod_gps = t.cod_gps
WHERE
	t.cod_gps = 4
;

• Con un cod_gps igual a 4 queremos buscar los datos del fabricante 4, pero, ese tipo de equipo no cuenta con registros en la tabla de la izquierda (equipos_gps), por lo tanto, la consulta debe regresar valores NULL para rellenar los registros faltantes, tal como se ve en la siguiente imagen.

¿Cómo seleccionar datos de múltiples tablas mediante un RIGHT JOIN?

Al igual que en nuestro ejemplo con LEFT JOIN, debemos definir un RIGHT JOIN por tabla que deseamos cruzar, por ejemplo:

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
RIGHT JOIN tipos_gps		AS t ON e.cod_gps = t.cod_gps
RIGHT JOIN tipos_gps_2	AS t2 ON e.cod_gps = t2.cod_gps;

• En el código anterior he decido quitar el WHERE, para explicarte algo interesante, dado que la condición del RIGHT JOIN es que cod_gps sea igual en todas las tablas, la consulta evaluara este caso y nos regresará todos los registros que cumplan con esa condición y lo que no cumpla, lo rellenará con NULL.

• Tanto cod_gps igual a 1 y 3, existen en todas las tablas y podemos ver información completa.
• Pero, cod_gps igual a 5 sólo esta en tipos_gps y tipos_gps_2, es decir, en ambas tablas de la derecha, esto causa que al no estar en la tabla de la izquierda, vemos los NULL, ya que no cumple con las condiciones de los RIGHT JOIN.

Y listo, ya tienes las nociones básicas de como obtener datos de varias tablas a través de RIGHT JOIN, continuemos con más ejemplos.

¿Qué es FULL JOIN y para qué sirve?

La clausula FULL JOIN nos permite seleccionar datos de dos o más tablas siempre y cuando exista una coincidencia en la tabla izquierda o derecha.

• FULL JOIN es como combinar los resultado de las tablas de la izquierda y la derecha y los registros que no coincida, se llenarán con NULL, en cualquiera de los lados.

Sintaxis FULL JOIN

SELECT *
FROM tabla_izquierda
FULL JOIN tabla_derecha
ON tabla_izquierda.nombre_columna = tabla_derecha.nombre_columna;

La sintaxis es muy simple, y te quedará mucho más clara con el siguiente diagrama.

 

Diagrama de Venn de un FULL JOIN

En el diagrama siguiente podemos ver claramente que la combinación de datos.

¿Cómo seleccionar datos de dos tablas mediante un FULL JOIN?

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
FULL JOIN tipos_gps	AS t ON e.cod_gps = t.cod_gps;

• Para FULL JOIN, también hemos establecido que los cod_gps sean iguales, y lo podemos ver en la línea:
  • FULL JOIN tipos_gps AS t ON e.cod_gps = t.cod_gps;
• He eliminado el WHERE, tu debes poner la condición que satisfaga la combinación que necesitas, yo he eliminado el WHERE con la intención de que veas como es que regresa todos los registros que cumplen con que cod_gps sean iguales y, lo que no satisface lo regresa como NULL, tal como vemos en la siguiente imagen.

• Recuerda que los campos que no satisfagan la condición del FULL JOIN, serán rellenados con NULL en ambos lados, para ello veamos el siguiente ejemplo, donde te quedará mas claro.

 

¿Cómo seleccionar datos de múltiples tablas mediante un FULL JOIN?

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
FULL JOIN tipos_gps		AS t ON e.cod_gps = t.cod_gps
FULL JOIN tipos_gps_2	AS t2 ON e.cod_gps = t2.cod_gps;

• Como puedes observar, también he eliminado el WHERE, y quiero que combinar todos los datos donde cod_gps sea iguales en todas las tablas.
• Pero lo hago con la intención de que veas que todo aquello que no tenga coincidencias será rellenado con NULL en ambos lados, tal como se ve en la siguiente imagen.

• La combinación sigue perfectamente la definición de FULL JOIN, regresa todo lo que cumple la condicional, y lo que no, lo rellena con NULL en ambos lados.

Y es todo, ahora ya sabes como llevar acabo un FULL JOIN y que resultados debe regresarte, recuerda que dependiendo de la condición que establezcas es como se llevará acabo la selección de datos.

¿Qué es CROSS JOIN y para qué sirve?

La clausula CROSS JOIN nos permite llevar a cabo el producto cartesiano, recuerda que el producto cartesiano es: combinar cada registro de una tabla con cada registro de otra tabla.

• Dado que el producto cartesiano es una combinación total de registros, aquí no veremos que retorna valores NULL.

Recuerda revisar la clase de álgebra lineal donde hacemos esta operación a mano.

 

Sintaxis CROSS JOIN

SELECT *
FROM tabla_izquierda
CROSS JOIN tabla_derecha
WHERE
	condiciones;

• Como puedes ver, no hay una condicional como tal para el CROSS JOIN, si deseas crear condiciones a tu consulta, debes establecerlas en el WHERE como en todo SELECT.

La sintaxis es muy simple, y te quedará mucho más clara con el siguiente diagrama.

 

Diagrama de Venn de un CROSS JOIN

En el diagrama siguiente podemos ver claramente que el producto cartesiano, donde cada registro de un conjunto se combina con todos los conjuntos del otro registro.

 

¿Cómo seleccionar datos de dos tablas mediante un CROSS JOIN?

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
CROSS JOIN tipos_gps	AS t
WHERE
	e.imei = '111111111111111';

• En este CROSS JOIN solo seleccionamos un registro en equipos_gps, y lo combinamos con todos los registros de tipos_gps.
• El resultado del producto cartesiano es el siguiente:

• Ahora, para que veas todas las combinaciones posibles entre estas dos tablas, solo basta con quitar el WHERE y ejecutar la consulta nuevamente.

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
CROSS JOIN tipos_gps	AS t;

• El resultado del producto cartesiano sería el siguiente:

• Como puedes observar en el producto cartesiano no regresa valores NULL:

 

¿Cómo seleccionar datos de múltiples tablas mediante un CROSS JOIN?

USE ewebikdb;

SELECT *
FROM equipos_gps		AS e
CROSS JOIN tipos_gps	AS t
CROSS JOIN tipos_gps_2	AS t2;

• Al igual que en ejemplos anteriores, por cada tabla que deseamos consultar, debemos agrega una clausula CROSS JOIN.
• Al ejecutar la consulta anterior nos regresa 54 filas, lo cual no es muy usable ponerlo como imagen o agregar los datos, te invito a llevar acabo este ejemplo y verlo por ti mismo.
• También ve el vídeo que ahí vendrá todo esto.

Y listo, ahora ya sabes como utilizar el CROSS JOIN y para que sirve, te invito a probar con más datos y hacer cambios en las condiciones para ir experimentando.

 

Conclusión: JOIN SQL

Excelente, hemos llegado al final de esta clase, hemos visto varios conceptos nuevos, pero sobre todo, hemos ocupado la teoría de clases pasadas, no olvides hacer un repaso para afianzar tus conocimientos.