Todos los Join MySQL – Select Con Dos o Más Tablas

Saludos estimado lector, en esta entrega vengo a hablarte acerca de como realizar un select con dos o mas tablas, y te presentaré todos los join que existen en MySQL.

El modelo relacional utilizado en SQL permite crear estructuras donde los datos son almacenados en tablas, estas tablas no almacenan información de forma aislada, es decir una tabla puede tener una relación con una o más tablas, esto permite que se pueda obtener datos coherentes desde varias tablas.

Para poder llevar esta relación eficientemente en el lenguaje SQL se utilizan llaves o claves primaria y foránea, estas llaves permiten llevar a cabo dicha relación, ya que vienen a ser campos comunes en las diferentes tablas y esto permitirá saber cuál fila en una tabla especifica tiene relación con otra(s) fila(s) en otra(s) tabla(s).

Creo que hasta aquí vamos claros, ahora veremos el tema sobre los joins.

La sentencia SELECT MySQL

Operaciones con dos tablas o más – Join

Para realizar un select con dos o más tablas hay que tener en cuenta las llaves y relaciones que existen entre las tablas de la base de datos, este proceso consiste en una especie de unión de tablas que en SQL es llamada JOIN, del cual existen diferentes tipos de uniones de tablas.

Tipo de JoinFunción
INNER JOIN MySQLRecupera todos los registros con coincidencias en las tablas involucradas, excluye los que no tiene coincidencias
LEFT JOIN MySQLRecupera todos los registros con coincidencias en las tablas involucradas y todos los de la tabla ubicada a la izquierda del join, excluye los que no tiene coincidencias
RIGHT JOIN MySQLRecupera todos los registros con coincidencias en las tablas involucradas y todos los de la tabla ubicada a la derecha del join, excluye los que no tiene coincidencias
CROSS JOIN MySQLRecupera el producto cruzado de todos los registros de las tablas ivnolucradas.
NATURAL JOIN MySQLRecupera todos los registros con coincidencias en las tablas involucradas ,no requiere el uso de la palabra ON, excluye los que no tiene coincidencias
SELF JOIN MySQLUne una tabla consigo misma, utilizando alias, puede usa cualquiera de los join mencionado más arriba.

Cada tipo de JOIN ofrece su propia ventaja y debe ser utilizado según la necesidad.

Inner Join MySQL

inner-join

Es el mas sencillo de todos los join en MySQL y el mas fácil, ya que se basa en las igualdades de los valores de una o más columnas especificas de las tablas involucradas.

La sintaxis del INNER JOIN es la siguiente:

SELECT tabla1.columna(s), tabla2.columna(s)  
FROM tabla1   
INNER JOIN tabla2   
ON tabla1.columna = tabla2.columna 

Tambíen podemos agregar más clausuras:

SELECT tabla1.columna(s), tabla2.columna(s)  
FROM tabla1   
INNER JOIN tabla2   
ON tabla1.columna = tabla2.columna  
WHERE condición  
GROUP BY tabla1.columna(s) | tabla2.columna(s)  
ORDER BY tabla1.columna(s) | tabla2.columna(s)

Una de las principales diferencias entre el SELECT con una tabla y el SELECT con dos o mas tablas es que en el SELECT con una sola tabla no es obligatorio utilizar la notación objeto.atributo (donde el objeto es la tabla y el atributo es el campo), sin embargo cuando se ven involucrada más de una tabla entonces se hace necesario utilizar la notación objeto.atributo.

Operadores relacionales / comparación MySQL

Notación Objeto-Atríbuto

La notación Objeto-atributo es propia de la programación orientada a objeto, donde para hacer referencia a una propiedad o atributo se utiliza el punto después de nombrar el objeto y luego del punto al atributo al cual se quiere acceder.

El motivo de utilizarlo en SQL es simple, si dos tablas tienen un atributo(campo, columna) que lleva el mismo nombre que otra que se encuentra presente en la consulta y no se especifica a cual tabla pertenece se produciría un error de ambigüedad por no indiciar a cual tabla pertenece el campo seleccionado.

From – Inner Join – On

Por lo demás en la clausura FROM se coloca la tabla y se le agrega INNER JOIN seguido de la tabla con la cual se unirá la primera tabla (la que esta en FROM) y a continuación se coloca la palabra ON,aquí debemos indicar cuales son los campos que se tomaran en cuenta al momento de unir las tablas, si el valor de esos campos son iguales entonces se seleccionan las columnas que pertenecen a esos registros.

Bien, teniendo esto en cuenta vamos con los ejemplos.

La tabla que utilizaremos pertenecen a una base de dato que está disponible para descargar desde este enlace, y es la que utilizaré a lo largo de este tutorial y los demás tutoriales.

Para empezar tomaremos dos tablas, empleado y almacenes, dado que un almacén tiene un empleado como encargado, nos gustaría conocer el nombre de los encargados y sus respectivos almacenes.

Las tablas tienen los siguientes registros (Puede que exista alguna variación en cantidad con la real en las tablas).

Tabla almacén

IdAlmacenCodigoIdEmpleadoEncargadoCiudadEstado
1 al-01 1 Seattle WA
2 al-02 2 Seattle WA
3 al-03 3 Portland OR
4 al-04 NULL Columbus OH
5 al-05 5 Trenton NJ
6 al-06 6 Seattle WA
7 al-07 NULL Seattle WA
8 al-08 8 Columbus OH
9 al-09 8 Columbus OH
10 al-10 6 Seattle WA
11 al-11 5 Los Angeles CA
12 al-12 NULL Portland OR
13 al-13 7 Portland OR
14 al-14 5 Los Angeles CA
15 al-15 1 Seatle WA
16 al-16 6 Columbus OH
17 al-17 3 Los Angeles CA
18 al-18 2 Portland OR

Tabla Empleado

IdEmpleadoNombreApellidos
1 Nancy Davolio
2 Andrew Fuller
3 Janet Leverling
4 Margaret Peacock
5 Steven Buchanan
6 Michael Suyama
7 Robert King
8 Laura Callahan
9 Anne Dodsworth

El diagrama de la base de datos para la relación de estas tablas es el siguiente.

todos-los-joins

Ya con todos los puntos claros, la consulta que ejecutaremos será la siguiente.

SELECT almacen.codigo, empleado.Nombre, empleado.ciudad  
FROM almacen  
INNER JOIN empleado  
ON almacen.idEmpleadoEncargado = empleado.IdEmpleado 

Si observamos las tablas utilizadas en este JOIN podremos notar que ambas tablas poseen una columna que víncula o más bien relaciona ambas tablas, esta columnas son idEmpleadoEncargado para la tabla almacen y idEmpleado para la tabla Empleado.

Instala y configura MySQL

Lo ideal sería los campos que las relacionan tengan el mismo nombre en ambas tablas, pero puede darse el caso de que no sean de igual nombre, como lo es el ejemplo anterior.

Y al ejecutar el comando obtendremos el siguiente resultado.

CodigoNombreCiudad
al-01 Nancy Seattle
al-02 Andrew Tacoma
al-03 Janet Kirkland
al-05 Steven Londres
al-06 Michael Londres
al-08 Laura Seattle
al-09 Laura Seattle
al-10 Michael Londres
al-11 Steven Londres
al-13 Robert Londres
al-14 Steven Londres
al-15 Nancy Seattle
al-16 Michael Londres
al-17 Janet Kirkland
al-18 Andrew Tacoma

Antes de continuar con otros joins quiero resaltar que si observamos los registros de las tablas podemos ver que los almacenes que tiene el valor NULL en el IdEmpleadoEncargado (4, 7 , 12),se excluyeron del resultado por no tener coincidencia en ambas tablas.

Left Join MySQL

left-join

El LEFT JOIN es identico al INNER JOIN, incluso la sintaxis es la misma, la única diferencia es que en vez de utilizar la palabra INNER esta es cambiada por LEFT. Otra diferencia es su funcionamiento, a diferencia del INNER JOIN donde solo se recuperan las filas que coinciden en valor en la clausura ON, en el LEFT JOIN son recuperados todos los registros de la tabla ubicada en la clausura FROM y solo aquellas filas de la tabla ubicada en el LEFT JOIN que coinciden en valor en la clausura ON.

De forma resumida podemos decir que el LEFT JOIN recupera todas las filas de la tabla ubicada en el FROM y solo lo que coincide de la tabla ubicada en el LEFT JOIN.

Su sintaxis

SELECT tabla1.columna(s), tabla2.columna(s)    
FROM tabla1     
LEFT JOIN tabla2     
ON tabla1.columna = tabla2.columna   

O podría ser la sintaxis con mas elementos:

SELECT tabla1.columna(s), tabla2.columna(s)    
FROM tabla1     
LEFT JOIN tabla2     
ON tabla1.columna = tabla2.columna    
WHERE condición    
GROUP BY tabla1.columna(s) | tabla2.columna(s)    
ORDER BY tabla1.columna(s) | tabla2.columna(s) 

Para el proximo ejemplo utilizaremos la consulta anterior, pero en esta ocasión cambiaremos la clausura INNER JOIN por LEFT JOIN.

SELECT almacen.codigo, empleado.Nombre, empleado.ciudad    
FROM almacen    
LEFT JOIN empleado    
ON almacen.idEmpleadoEncargado = empleado.IdEmpleado 
ORDER BY almacen.codigo  

Al ejecutar la consulta obtendremos el siguiente resultado

CodigoNombreCiudad
al-01 Nancy Seattle
al-02 Andrew Tacoma
al-03 Janet Kirkland
al-04 NULL NULL
al-05 Steven Londres
al-06 Michael Londres
al-07 NULL NULL
al-08 Laura Seattle
al-09 Laura Seattle
al-10 Michael Londres
al-11 Steven Londres
al-12 NULL NULL
al-13 Robert Londres
al-14 Steven Londres
al-15 Nancy Seattle
al-16 Michael Londres
al-17 Janet Kirkland
al-18 Andrew Tacoma

Quiero llamar tu atención al resultado en los códigos al-04, al-07 y al-12  ya que estos no fueron incluidos en el resultado.

Sin embargo, en el campo nombre y ciudad que pertenecen a la tabla empleado tiene el valor NULL, esto se debe a que el idEmpleadoEncargado de los códigos al-04,  al-07 y al-12 no tienen un idEmpleado asignado, por lo tanto, es imposible encontrar un valor que coincida con ellos.

Right Join MySQL

right-join

El RIGHT JOIN realiza la acción inversa al LEFT JOIN, el cual incluye todos los registros de la tabla colocada a la izquierda del JOIN, pero solo incluye los registros que coinciden en la clausura ON.

Al realizar lo inverso entonces quiere decir que recupera todos los registros de la tabla colocada a la derecha del JOIN y excluye aquellos que no tienen ninguna coincidencia en el ON de la tabla colocada a la izquierda del JOIN.

La sintaxis es la siguiente

SELECT tabla1.columna(s), tabla2.columna(s)    
FROM tabla1     
RIGHT JOIN tabla2     
ON tabla1.columna = tabla2.columna

Y con más clausuras agregadas

SELECT tabla1.columna(s), tabla2.columna(s)    
FROM tabla1     
RIGHT JOIN tabla2     
ON tabla1.columna = tabla2.columna    
WHERE condición    
GROUP BY tabla1.columna(s) | tabla2.columna(s)    
ORDER BY tabla1.columna(s) | tabla2.columna(s) 

Utilizaremos la consulta anterior, pero en esta ocasión cambiaremos la clausura LEFT JOIN por RIGHT JOIN.

SELECT almacen.codigo, empleado.Nombre, empleado.ciudad    
FROM almacen    
RIGHT JOIN empleado    
ON almacen.idEmpleadoEncargado = empleado.IdEmpleado    
ORDER BY almacen.codigo 

Una vez que ejecutemos eUna vez que ejecutemos el código tendremos el siguiente resultado.

CodigoNombreCiudad
NULL Anne Londres
NULL Margaret Redmond
al-01 Nancy Seattle
al-02 Andrew Tacoma
al-03 Janet Kirkland
al-05 Steven Londres
al-06 Michael Londres
al-08 Laura Seattle
al-09 Laura Seattle
al-10 Michael Londres
al-11 Steven Londres
al-13 Robert Londres
al-14 Steven Londres
al-15 Nancy Seattle
al-16 Michael Londres
al-17 Janet Kirkland
al-18 Andrew Tacoma

En el resultado podemos notar que todos los registros de la tabla empleado fueron recuperados, pero no sucedió los mismo con los registros de la tabla almacen ya que solo se recuperaron los almacenes que tienen valores en el campo idEmpleadoEncargado diferente de NULL y es un valor que existe en el campo idEmpleado en la tabla empleado.

Distintc en MySQL

Cross Join MySQL

El CROSS JOIN presenta el producto cartesiano de las filas de las tablas cruzadas. El resultado tendrá todos los registros de la tabla ubicada en el FROM izquierda combinados con cada uno de los registros de la tabla ubicada en CROSS JOIN.

Algo que debemos destacar es que no se utiliza la clausura ON para establecer condiciones.

Tal y como lo muestra la siguiente ilustración.

todos-los-joins

Una consulta utilizando CROSS JOIN quedaría así.

SELECT * FROM almacenes CROSS JOIN empleado 

No hay mucho que decir sobre este join, pero es posible que le encuentres alguna utilidad.

Natural Join

El NATURAL JOIN es una simplificación de INNER JOIN con la característica de que cuando los campos de las tablas que serán utilizadas en el JOIN, dígase los campos por los cuales serán comparados los valores en la clausura ON, tienen el mismo nombre se puede omitir el uso de la clausura ON, y el NATURAL JOIN se encargará de comparar las coincidencias y el resultado será el mismo que un INNER JOIN con la clausura ON.

Si los campos no tienen el mismo nombre, entonces no se recomienda utilizarlo.

SELECT producto.NombreProducto, categoria.NombreCategoria  
FROM producto  
NATURAL JOIN categoria  
WHERE IdProducto < 10

El equivalente a esta consulta sería la siguiente con el INNER JOIN

SELECT producto.NombreProducto, categoria.NombreCategoria  
FROM producto  
INNER JOIN categoria  
ON producto.IdCategoria = categoria.IdCategoria  
WHERE IdProducto < 10

En ambas consultas el resultado será el mismo

NombreProductoNombreCategoria
Té DharamsalaBebidas
Cerveza tibetana BarleyBebidas
Sirope de regalizCondimentos
Especias Cajun del chef AntonCondimentos
Mezcla Gumbo del chef AntonCondimentos
Mermelada de grosellas de la abuelaCondimentos
Peras secas orgánicas del tío BobFrutas/Verduras
Salsa de arándanos NorthwoodsCondimentos
Buey Mishi KobeCarnes

ya sabes, si el nombre de las llaves o campos que unen las tablas en un join son el mismo, puedes utilizar este join sin necesidad del inner join.

Alias En Las tablas

Existe la posibilidad de renombrar el nombre de las tablas, pero a diferencia de las columnas cuyo propósito es una mejor presentación de los datos, cuando se renombra una tabla se hace para realizar consultas con más facilidad.

Tomaremos como ejemplo la consulta anterior, pero ahora utilizaremos el alias.

SELECT prod.NombreProducto, cat.NombreCategoria  
FROM producto AS prod
INNER JOIN categoria AS cat
ON prod.IdCategoria = cat.IdCategoria  
WHERE prod.IdProducto < 10

Si observamos, una vez colocada el alias a la tabla, es posible utilizarla en cualquier parte de la consulta, esto es útil cuando se realizan consultas largas con nombre de tablas largos y se necesitan seleccionar muchos campos.

Self Join

El self join no es más que hacer un join a una tabla consigo misma.

¿Cuál sería la finalidad?, simple, comparar los datos de una tabla consigo mismo, en los joins se ha mostrado como unir tablas mediante campos relacionado, y recuperar valores por coincidencias, pero cuando se necesita comparar los datos de una tabla con los datos de sí misma, entonces tenemos solo dos (2) opciones:

  1. Crear otra tabla y copiar los registros de la primera en la segunda y luego comparar.
  2. Utilizar self join

Yo me descarto por la segunda, … y creeme que la primera no es buena practica y sobre todo en un escenario de producción.

Ahora bien, para poder realizar el self join es necesario el uso de alias, la temática es utilizar un alias diferente para la misma tabla, de manera que SQL creerá que son dos tablas distintas.

Vamos a listar todos los almacenes que tienen el mismo encargado:

SELECT a1.codigo, a1.idEmpleadoEncargado   
FROM almacen AS a1  
INNER JOIN almacen AS a2  
ON a1.idAlmacen != a2.idAlmacen  
AND a1.idEmpleadoEncargado = a2.idEmpleadoEncargado  
ORDER BY a1.idEmpleadoEncargado  

En esta consulta al utilizar alias en la tablas, SQL visualiza cada alias como una tabla independiente, eso permite operar sobre ellas utilizando un join.

En este caso no se buscaron id que coincidan, sino que se buscaron id diferentes pero que tengan el mismo IdEmpleado, tambíen se le agregó un AND al ON para agregar otra condición al JOIN la cual hace que consigamos el resultado deseado.

Si conoces otra forma de hacer esto, puedes dejarlo en los comentarios.

Join Con Más De Dos Tablas

Aquí ya esto se pone super bueno, ya que cuando se utiliza el modelo relacional de bases de datos junto con el lenguaje SQL es 100% seguro que existan relaciones con mas de dos (2) tabla, es decir existen tablas que tienen mas de una (1) llave foránea, y también una tabla se relaciona con otras tantas según fuese necesario.

Entonces los joins no solo deben limitarse a unir o cruzar solo dos (2) tablas, sino que según los requerimientos de la consulta serán la cantidad de join que estarán en ella.

En el siguiente diagrama podemos ver como la tabla producto esta relacionada con la tabla categoria y tambien proveedor.

join

Vamos a listar los nombres de productos con su categoría y proveedor

SELECT 
    producto.NombreProducto, 
    categoria.NombreCategoria, 
    proveedor.NombreEmpresa  
FROM producto   
INNER JOIN categoria  
ON producto.IdCategoria = categoria.IdCategoria  
INNER JOIN proveedor  
ON producto.IdProveedor = proveedor.IdProveedor 

Como podemos apreciar, la mecánica es simple, después del ON, podemos agregar más JOIN, siempre y cuando existan columnas que relacionen las tablas.

Esta consulta dará el siguiente resultado

NombreProductoNombreCategoriaNombreEmpresa
Té DharamsalaBebidasExotic Liquids
Cerveza tibetana BarleyBebidasExotic Liquids
Sirope de regalizCondimentosExotic Liquids
Especias Cajun del chef AntonCondimentosNew Orleans Cajun Delights
Mezcla Gumbo del chef AntonCondimentosNew Orleans Cajun Delights
Mermelada de grosellas de la …CondimentosGrandma Kelly’s Homestead
Peras secas orgánicas del tío BobFrutas/VerdurasGrandma Kelly’s Homestead
Salsa de arándanos NorthwoodsCondimentosGrandma Kelly’s Homestead
Buey Mishi KobeCarnesTokyo Traders
Pez espadaPescado/MariscoTokyo Traders
Queso CabralesLácteosCooperativa de Quesos ‘Las Cabras’
Queso Manchego La PastoraLácteosCooperativa de Quesos ‘Las Cabras’
Algas KonbuPescado/MariscoMayumi’s

Espero que este post sobre todos los Join de MySQL y como realizar un select con dos o más tablas te sea de utilidad.

Hasta la próxima y no te olvides de compartir.

Deja un comentario

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.