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

¿Qué son las 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 menos comprendido entre los estudiantes 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

id nombre de pila apellido id_película
1 Adam Smith 1
2 Ravi Kumar 2
3 Susan Davidson 5
4 Jenny Adrianna 8
5 Lee Pong 10
id título categoría
1 ASSASSIN'S CREED: BRAZAS Animaciones
2 Acero real (2012) Animaciones
3 Alvin y las Ardillas Animaciones
4 Las aventuras de Tintin Animaciones
5 Seguro (2012) Tarjetas, flyers, carteles personalizados
6 Casa segura (2012) Tarjetas, flyers, carteles personalizados
7 GIA +18
8 Fecha límite 2009 +18
9 La imagen sucia +18
10 marley y yo Romance

Tipos de uniones

Cruz ÚNETE

Cross JOIN es la 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 comparar todos los registros de miembros con todos los registros de películas, podemos usar el script que se muestra a continuación para obtener los resultados deseados.

Tipos de uniones

SELECT * FROM `movies` CROSS JOIN `members`

Ejecutando el script anterior en Banco de trabajo MySQL nos da el siguientewing 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

INNER JOIN

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

Supongamos que desea obtener una lista de 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.

INNER JOIN

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

Al ejecutar 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 A
INNER JOIN `movies` AS B
ON B.`id` = A.`movie_id`

UNIONES externas

Los JOIN externos de MySQL devuelven todos los registros que coinciden de ambas tablas.

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

¿Suena confuso? Veamos un ejemplo:

LEFT JOIN

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

UNIONES externas

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 A
LEFT JOIN `members` AS B
ON B.`movie_id` = A.`id`

La ejecución del script anterior en MySQL Workbench da como resultado. 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 coincidente encontró una 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 A LA DERECHA

UNIRSE DERECHA es obviamente lo opuesto a UNIRSE IZQUIERDA. 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 que alquilaron. Ahora tenemos un nuevo miembro que aún no ha alquilado ninguna película.

UNIRSE A LA DERECHA

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

Al ejecutar el script anterior en MySQL Workbench se obtiene lo siguientewing 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 consulta JOIN anteriores, hemos utilizado la cláusula ON para hacer coincidir los registros entre las tablas.

La cláusula USING también se puede utilizar para el mismo propósito. la diferencia con USO es lo 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 del campo "id" de las tablas "películas" para que tenga el nombre "movie_id". Hacemos esto para tener nombres de campos 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 UNIÓN IZQUIERDA anterior.

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

Aparte de usar ON y USANDO con JOINs puedes usar muchas otras cláusulas de MySQL como AGRUPAR POR, DONDE e incluso funciona como SUM, AVG, etc.

¿Por qué deberíamos utilizar uniones?

Ahora puede pensar por qué usamos JOIN cuando podemos realizar 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 y utilizar la salida de cada una en consultas sucesivas. Por supuesto, eso es posible. Pero al utilizar JOIN, puede realizar el trabajo utilizando 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 única consulta JOIN en lugar de ejecutar varias consultas reduce la sobrecarga del servidor. En su lugar, utilizar múltiples consultas genera más transferencias de datos entre MySQL y las aplicaciones (software). Además, también requiere más manipulaciones de datos al final de la aplicación.

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

Resumen

  • JOINS nos permite combinar datos de más de una tabla en un único conjunto de resultados.
  • JOINS tienen un mejor rendimiento en comparación con las subconsultas
  • INNER JOINS solo devuelve filas que cumplen con 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 UNIONES incluyen UNIONES Internas, Externas Izquierdas, Externas Derechas, UNIONES Cruzadas, etc.
  • La cláusula utilizada con frecuencia 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.