Tutorial MySQL JOINS: INTERIOR, EXTERIOR, IZQUIERDA, DERECHA, CRUZADA

Anonim

¿Qué son UNIONES?

Las uniones ayudan a recuperar datos de dos o más tablas de bases de datos. Las tablas están relacionadas entre sí mediante claves primarias y externas.

Nota: JOIN es el tema más incomprendido entre los usuarios de SQL. En aras de la simplicidad y la facilidad de comprensión, utilizaremos una nueva base de datos para practicar la muestra. Como se muestra abajo

identificación nombre de pila apellido movie_id
1 Adán Herrero 1
2 Ravi Kumar 2
3 Susan Davidson 5
4 Jenny Adrianna 8
6 Sotavento Apestar 10
identificación título categoría
1 CREDO DEL ASESINO: EMBERS Animaciones
2 Acero real (2012) Animaciones
3 Alvin y las ardillas Animaciones
4 Las aventuras de Tintin Animaciones
5 Seguro (2012) Acción
6 Casa segura (2012) Acción
7 GIA 18+
8 Fecha límite 2009 18+
9 La foto sucia 18+
10 Marley y yo Romance

Tipos de uniones

Cruz ÚNETE

Cross JOIN es una forma más simple de JOIN que hace coincidir cada fila de una tabla de base de datos con todas las filas de otra.

En otras palabras, nos da combinaciones de cada fila de la primera tabla con todos los registros de la segunda tabla.

Supongamos que queremos obtener todos los registros de miembros contra todos los registros de películas, podemos usar el script que se muestra a continuación para obtener los resultados deseados.

SELECT * FROM `movies` CROSS JOIN `members`

La ejecución del script anterior en MySQL workbench nos da los siguientes resultados.

 

 

 

 id title   id first_name last_name movie_id 


 

 1 ASSASSIN'S CREED: EMBERS Animations 1 Adam Smith 1 


 

 1 ASSASSIN'S CREED: EMBERS Animations 2 Ravi Kumar 2 


 

 1 ASSASSIN'S CREED: EMBERS Animations 3 Susan Davidson 5 


 

 1 ASSASSIN'S CREED: EMBERS Animations 4 Jenny Adrianna 8 


 

 1 ASSASSIN'S CREED: EMBERS Animations 6 Lee Pong 10 


 

 2 Real Steel(2012) Animations 1 Adam Smith 1 


 

 2 Real Steel(2012) Animations 2 Ravi Kumar 2 


 

 2 Real Steel(2012) Animations 3 Susan Davidson 5 


 

 2 Real Steel(2012) Animations 4 Jenny Adrianna 8 


 

 2 Real Steel(2012) Animations 6 Lee Pong 10 


 

 3 Alvin and the Chipmunks Animations 1 Adam Smith 1 


 

 3 Alvin and the Chipmunks Animations 2 Ravi Kumar 2 


 

 3 Alvin and the Chipmunks Animations 3 Susan Davidson 5 


 

 3 Alvin and the Chipmunks Animations 4 Jenny Adrianna 8 


 

 3 Alvin and the Chipmunks Animations 6 Lee Pong 10 


 

 4 The Adventures of Tin Tin Animations 1 Adam Smith 1 


 

 4 The Adventures of Tin Tin Animations 2 Ravi Kumar 2 


 

 4 The Adventures of Tin Tin Animations 3 Susan Davidson 5 


 

 4 The Adventures of Tin Tin Animations 4 Jenny Adrianna 8 


 

 4 The Adventures of Tin Tin Animations 6 Lee Pong 10 


 

 5 Safe (2012) Action 1 Adam Smith 1 


 

 5 Safe (2012) Action 2 Ravi Kumar 2 


 

 5 Safe (2012) Action 3 Susan Davidson 5 


 

 5 Safe (2012) Action 4 Jenny Adrianna 8 


 

 5 Safe (2012) Action 6 Lee Pong 10 


 

 6 Safe House(2012) Action 1 Adam Smith 1 


 

 6 Safe House(2012) Action 2 Ravi Kumar 2 


 

 6 Safe House(2012) Action 3 Susan Davidson 5 


 

 6 Safe House(2012) Action 4 Jenny Adrianna 8 


 

 6 Safe House(2012) Action 6 Lee Pong 10 


 

 7 GIA 18+ 1 Adam Smith 1 


 

 7 GIA 18+ 2 Ravi Kumar 2 


 

 7 GIA 18+ 3 Susan Davidson 5 


 

 7 GIA 18+ 4 Jenny Adrianna 8 


 

 7 GIA 18+ 6 Lee Pong 10 


 

 8 Deadline(2009) 18+ 1 Adam Smith 1 


 

 8 Deadline(2009) 18+ 2 Ravi Kumar 2 


 

 8 Deadline(2009) 18+ 3 Susan Davidson 5 


 

 8 Deadline(2009) 18+ 4 Jenny Adrianna 8 


 

 8 Deadline(2009) 18+ 6 Lee Pong 10 


 

 9 The Dirty Picture 18+ 1 Adam Smith 1 


 

 9 The Dirty Picture 18+ 2 Ravi Kumar 2 


 

 9 The Dirty Picture 18+ 3 Susan Davidson 5 


 

 9 The Dirty Picture 18+ 4 Jenny Adrianna 8 


 

 9 The Dirty Picture 18+ 6 Lee Pong 10 


 

 10 Marley and me Romance 1 Adam Smith 1 


 

 10 Marley and me Romance 2 Ravi Kumar 2 


 

 10 Marley and me Romance 3 Susan Davidson 5 


 

 10 Marley and me Romance 4 Jenny Adrianna 8 


 

 10 Marley and me Romance 6 Lee Pong 10

UNIR INTERNAMENTE

El JOIN interno se usa para devolver filas de ambas tablas que satisfacen la condición dada.

Suponga que desea obtener una lista de los miembros que han alquilado películas junto con los títulos de las películas que alquilaron. Simplemente puede usar INNER JOIN para eso, que devuelve filas de ambas tablas que cumplen con las condiciones dadas.

SELECT members.`first_name` , members.`last_name` , movies.`title`FROM members ,moviesWHERE movies.`id` = members.`movie_id`

Ejecutando el script anterior, dé

 

 

 

 first_name last_name title 


 

 Adam Smith ASSASSIN'S CREED: EMBERS 


 

 Ravi Kumar Real Steel(2012) 


 

 Susan Davidson Safe (2012) 


 

 Jenny Adrianna Deadline(2009) 


 

 Lee Pong Marley and me

Tenga en cuenta que el script de resultados anterior también se puede escribir de la siguiente manera para lograr los mismos resultados.

SELECT A.`first_name` , A.`last_name` , B.`title`FROM `members`AS AINNER JOIN `movies` AS BON B.`id` = A.`movie_id`

Uniones externas

Las JOIN externas de MySQL devuelven todos los registros que coinciden con ambas tablas.

Puede detectar registros que no tienen coincidencias en la tabla unida. Devuelve valores NULL para los registros de la tabla unida si no se encuentra ninguna coincidencia.

¿Suena confuso? Veamos un ejemplo:

ÚNETE A LA IZQUIERDA

Suponga ahora que desea obtener los títulos de todas las películas junto con los nombres de los miembros que las han alquilado. Está claro que algunas películas no han sido alquiladas por nadie. Simplemente podemos usar LEFT JOIN para este propósito.

LEFT JOIN devuelve todas las filas de la tabla de la izquierda incluso si no se han encontrado filas coincidentes en la tabla de la derecha. Cuando no se han encontrado coincidencias en la tabla de la derecha, se devuelve NULL.

SELECT A.`title` , B.`first_name` , B.`last_name`FROM `movies` AS ALEFT JOIN `members` AS BON B.`movie_id` = A.`id`

La ejecución del script anterior en MySQL workbench da. Puede ver que en el resultado devuelto que se enumera a continuación, para las películas que no se alquilan, los campos de nombre de miembro tienen valores NULL. Eso significa que ningún miembro que coincida encontró la tabla de miembros para esa película en particular.

 

 

 

 title first_name last_name 


 

 ASSASSIN'S CREED: EMBERS Adam Smith 


 

 Real Steel(2012) Ravi Kumar 


 

 Safe (2012) Susan Davidson 


 

 Deadline(2009) Jenny Adrianna 


 

 Marley and me Lee Pong 


 

 Alvin and the Chipmunks NULL NULL 


 

 The Adventures of Tin Tin NULL NULL 


 

 Safe House(2012) NULL NULL 


 

 GIA NULL NULL 


 

 The Dirty Picture NULL NULL 


 

 


Note: Null is returned for non-matching rows on right

UNIRSE CORRECTAMENTE

RIGHT JOIN es obviamente lo contrario de LEFT JOIN. RIGHT JOIN devuelve todas las columnas de la tabla de la derecha incluso si no se han encontrado filas coincidentes en la tabla de la izquierda. Cuando no se han encontrado coincidencias en la tabla de la izquierda, se devuelve NULL.

En nuestro ejemplo, supongamos que necesita obtener los nombres de los miembros y las películas alquiladas por ellos. Ahora tenemos un nuevo miembro que aún no ha alquilado ninguna película.

SELECT A.`first_name` , A.`last_name`, B.`title`FROM `members` AS ARIGHT JOIN `movies` AS BON B.`id` = A.`movie_id`

La ejecución del script anterior en MySQL workbench da los siguientes resultados.

 

 

 

 first_name last_name title 


 

 Adam Smith ASSASSIN'S CREED: EMBERS 


 

 Ravi Kumar Real Steel(2012) 


 

 Susan Davidson Safe (2012) 


 

 Jenny Adrianna Deadline(2009) 


 

 Lee Pong Marley and me 


 

 NULL NULL Alvin and the Chipmunks 


 

 NULL NULL The Adventures of Tin Tin 


 

 NULL NULL Safe House(2012) 


 

 NULL NULL GIA 


 

 NULL NULL The Dirty Picture 


 

 


Note: Null is returned for non-matching rows on left

Cláusulas "ON" y "USING"

En los ejemplos de consultas JOIN anteriores, hemos utilizado la cláusula ON para hacer coincidir los registros entre la tabla.

La cláusula USING también se puede utilizar para el mismo propósito. La diferencia con USING es que debe tener nombres idénticos para las columnas coincidentes en ambas tablas.

En la tabla "películas" hasta ahora usamos su clave principal con el nombre "id". Nos referimos a lo mismo en la tabla "miembros" con el nombre "movie_id".

Cambiemos el nombre de las tablas de "películas" al campo "id" para que tenga el nombre "movie_id". Hacemos esto para tener nombres de campo coincidentes idénticos.

ALTER TABLE `movies` CHANGE `id` `movie_id` INT( 11 ) NOT NULL AUTO_INCREMENT;

A continuación, usemos USING con el ejemplo de LEFT JOIN anterior.

SELECT A.`title` , B.`first_name` , B.`last_name`FROM `movies` AS ALEFT JOIN `members` AS BUSING ( `movie_id` )

Además de usar ON y USING con JOIN , puede usar muchas otras cláusulas de MySQL como GROUP BY, WHERE e incluso funciones como SUM , AVG , etc.

¿Por qué deberíamos usar uniones?

Ahora puede pensar, por qué usamos JOINs cuando podemos hacer la misma tarea ejecutando consultas. Especialmente si tiene algo de experiencia en programación de bases de datos, sabe que podemos ejecutar consultas una por una, utilice la salida de cada una en consultas sucesivas. Por supuesto, eso es posible. Pero al usar JOIN, puede hacer el trabajo usando solo una consulta con cualquier parámetro de búsqueda. Por otro lado, MySQL puede lograr un mejor rendimiento con JOIN, ya que puede usar Indexación. El simple uso de una sola consulta JOIN en lugar de ejecutar múltiples consultas reduce la sobrecarga del servidor. El uso de múltiples consultas en su lugar genera más transferencias de datos entre MySQL y aplicaciones (software). Además, también requiere más manipulaciones de datos en el extremo de la aplicación.

Está claro que podemos lograr mejores rendimientos de aplicaciones y MySQL mediante el uso de JOIN.

Resumen

  • JOINS nos permite combinar datos de más de una tabla en un solo conjunto de resultados.
  • JOINS tienen un mejor rendimiento en comparación con las subconsultas
  • INNER JOINS solo devuelve filas que cumplen los criterios dados.
  • OUTER JOINS también puede devolver filas donde no se han encontrado coincidencias. Las filas no coincidentes se devuelven con la palabra clave NULL.
  • Los principales tipos de JOIN incluyen Inner, Left Outer, Right Outer, Cross JOINS, etc.
  • La cláusula de uso frecuente en las operaciones JOIN es "ON". La cláusula "USING" requiere que las columnas coincidentes tengan el mismo nombre.
  • JOINS también se puede utilizar en otras cláusulas como GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS, etc.