Una buena manera de entenderlo es practicando, por lo que es recomendable que crees algunas tablas sencillas y lances las querys como forma de ver en vivo los resultados de cada tipo de JOIN y así entender para qué casos te pueden servir. No hace falta que tengan muchos campos, ni muchos registros.
Un buen ejemplo puede ser una tabla de libros y una de autores, como las que siguen (en este ejemplo estoy usando MySQL):
CREATE TABLE `books` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(200) default NULL,
`author_id` int(11) default NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `authors` (
`id` int(11) NOT NULL auto_increment,
`author` varchar(200) default NULL,
PRIMARY KEY (`id`)
)Los datos para las tablas:
# Dump of table authors# ------------------------------------------------------------
INSERT INTO `authors` (`id`,`author`) VALUES ('1','Cervantes');
INSERT INTO `authors` (`id`,`author`) VALUES ('2','García Márquez');
INSERT INTO `authors` (`id`,`author`) VALUES ('3','Saint_Exupery');
# Dump of table books
# ------------------------------------------------------------
INSERT INTO `books` (`id`,`title`,`author_id`) VALUES ('1','El quijote','1');
INSERT INTO `books` (`id`,`title`,`author_id`) VALUES ('2','100 años de soledad','2');
INSERT INTO `books` (`id`,`title`,`author_id`) VALUES ('3','El Principito','3');Producto cartesiano
Para entender cómo funcionan los JOIN tenemos que empezar repasando un concepto que muchos aprendimos en la Primaria: el producto cartesiano. Ya sabes: dados dos conjuntos A y B, su producto cartesiano es otro conjunto C (A × B) formado por todos los pares ordenados en los que el primer elemento del par pertenece a A y el segundo elemento del par pertenece a B.
Así, JOIN es básicamente el producto cartesiano de las tablas, es decir, una nueva tabla en que cada registro combina un registro de la primera tabla con cada uno de los registros de la segunda tabla.
La query
SELECT * FROM books JOIN authorsnos dará como resultado todas las posibles combinaciones de books y authors (en nuestro caso 9 registros). Algo así:
+----+---------------------+-----------+----+----------------+
| id | title | author_id | id | author |
+----+---------------------+-----------+----+----------------+
| 1 | El quijote | 1 | 1 | Cervantes |
| 2 | 100 a?os de soledad | 2 | 1 | Cervantes |
| 3 | El Principito | 3 | 1 | Cervantes |
| 1 | El quijote | 1 | 2 | Garc?a M?rquez |
| 2 | 100 a?os de soledad | 2 | 2 | Garc?a M?rquez |
| 3 | El Principito | 3 | 2 | Garc?a M?rquez |
| 1 | El quijote | 1 | 3 | Saint_Exupery |
| 2 | 100 a?os de soledad | 2 | 3 | Saint_Exupery |
| 3 | El Principito | 3 | 3 | Saint_Exupery |
+----+---------------------+-----------+----+----------------+
9 rows in set (0,03 sec)
Como puedes suponer, hacer este resultado no es muy útil para este tipo de datos, aunque hay muchos casos en que si que lo puede ser.
+----+---------------------+-----------+----+----------------+
| id | title | author_id | id | author |
+----+---------------------+-----------+----+----------------+
| 1 | El quijote | 1 | 1 | Cervantes |
| 2 | 100 a?os de soledad | 2 | 1 | Cervantes |
| 3 | El Principito | 3 | 1 | Cervantes |
| 1 | El quijote | 1 | 2 | Garc?a M?rquez |
| 2 | 100 a?os de soledad | 2 | 2 | Garc?a M?rquez |
| 3 | El Principito | 3 | 2 | Garc?a M?rquez |
| 1 | El quijote | 1 | 3 | Saint_Exupery |
| 2 | 100 a?os de soledad | 2 | 3 | Saint_Exupery |
| 3 | El Principito | 3 | 3 | Saint_Exupery |
+----+---------------------+-----------+----+----------------+
9 rows in set (0,03 sec)
Por ejemplo, en un campeonato deportivo como una liga de fútbol tendremos una tabla equipos, que recoge el nombre de todos los equipos participantes.
+----+------------+
| id | equipo |
+----+------------+
| 1 | Barcelona |
| 2 | Madrid |
| 3 | Celta |
| 4 | Villarreal |
+----+------------+
4 rows in set (0,02 sec)
Pues bien, un JOIN de la tabla consigo misma, nos permitirá obtener todos los partidos del campeonato. Eso sí, tendremos que recurrir a los alias para evitar un error de MySQL.
SELECT * FROM equipos AS Local JOIN equipos as Visitante+----+------------+----+------------+
| id | equipo | id | equipo |
+----+------------+----+------------+
| 1 | Barcelona | 1 | Barcelona |
| 2 | Madrid | 1 | Barcelona |
| 3 | Celta | 1 | Barcelona |
| 4 | Villarreal | 1 | Barcelona |
| 1 | Barcelona | 2 | Madrid |
| 2 | Madrid | 2 | Madrid |
| 3 | Celta | 2 | Madrid |
| 4 | Villarreal | 2 | Madrid |
| 1 | Barcelona | 3 | Celta |
| 2 | Madrid | 3 | Celta |
| 3 | Celta | 3 | Celta |
| 4 | Villarreal | 3 | Celta |
| 1 | Barcelona | 4 | Villarreal |
| 2 | Madrid | 4 | Villarreal |
| 3 | Celta | 4 | Villarreal |
| 4 | Villarreal | 4 | Villarreal |
+----+------------+----+------------+
16 rows in set (0,03 sec)
Con todo, esta query necesita alguna restricción para se perfecta, pues nos empareja cada equipo consigo misma, así que podemos añadir condiciones para eliminar esas parejas del resultado.
SELECT * FROM equipos AS Local JOIN equipos AS Visitante WHERE Local.id != Visitante.id+----+------------+----+------------+
| id | equipo | id | equipo |
+----+------------+----+------------+
| 2 | Madrid | 1 | Barcelona |
| 3 | Celta | 1 | Barcelona |
| 4 | Villarreal | 1 | Barcelona |
| 1 | Barcelona | 2 | Madrid |
| 3 | Celta | 2 | Madrid |
| 4 | Villarreal | 2 | Madrid |
| 1 | Barcelona | 3 | Celta |
| 2 | Madrid | 3 | Celta |
| 4 | Villarreal | 3 | Celta |
| 1 | Barcelona | 4 | Villarreal |
| 2 | Madrid | 4 | Villarreal |
| 3 | Celta | 4 | Villarreal |
+----+------------+----+------------+
12 rows in set (0,00 sec)
INNER JOIN
Este tipo de JOINS que nos dan el producto cartesiano son del tipo INNER y los resultados que podemos obtener de ellas estan siempre dentro de ese producto cartesiano.
Como deciamos antes, este tipo de resultados no es muy útil en algunos casos. Volviendo a nuestro ejemplo de ibros y autores, la query nos empareja obras y autores de todas las maneras posibles, lo que no se corresponde con la realidad. Nuestro sistema tiene que tener más conocimiento del mundo y poder utilizarlo al hacer la combinacion de tablas.
Nuestra tabla books cuenta con el campo author_id, la clave foránea que nos indica qué autor corresponde a cada libro. ¿Qué papel puede jugar en la combinacion de tablas?
JOIN admite una cláusula ON para definir qué condiciones deben usarse para que dos registros se combinen. En nuestro ejemplo, el cambo books.author_id debe coincidir con el campo author.id y lo expresamos así:
SELECT * FROM books INNER JOIN authors ON books.author_id = authors.id+----+---------------------+-----------+----+----------------+
| id | title | author_id | id | author |
+----+---------------------+-----------+----+----------------+
| 1 | El quijote | 1 | 1 | Cervantes |
| 2 | 100 a?os de soledad | 2 | 2 | Garc?a M?rquez |
| 3 | El Principito | 3 | 3 | Saint_Exupery |
+----+---------------------+-----------+----+----------------+
3 rows in set (0,05 sec)
De este modo, la query nos devuelve los libros correctamente emparejados con sus autores.
Podemos añadir la cláusula WHERE para especificar condiciones que restrinjan la busqueda de datos y esta puede usar campos de las tablas combinadas. Así, podemos buscar un libro por el nombre de su autor, a pesar de que este dato no está en la tabla books.
SELECT * FROM books INNER JOIN authors ON books.author_id = authors.id WHERE authors.author = 'Cervantes'+----+------------+-----------+----+-----------+
| id | title | author_id | id | author |
+----+------------+-----------+----+-----------+
| 1 | El quijote | 1 | 1 | Cervantes |
+----+------------+-----------+----+-----------+
1 row in set (0,00 sec)
Cuando combinamos ON y WHERE nos puede surgir la duda de si sería mejor poner las condiciones en ON o en WHERE.
La regla práctica sería poner en ON las condiciones para decidir que registros deben emparejarse y en WHERE las condiciones para filtrar o restringir el resultado. La base de datos primero genera la tabla temporal y luego hace el filtrado.
LEFT JOIN
Puede ocurrir que tengamos datos en una tabla que no tengan un registro asociado en la otra. Por ejemplo, añadimos un nuevo libro a nuestra tabla books pero no sabemos su autor (o es anónimo).
Ahora si pedimos una lista de todos los libros registrados con sus autores con la query anterior veremos que no aparecen los libros que no tengan autor. ¡Vaya! En muchos casos este comportamiento no nos interesa, querríamos tener toda la lista de libros aunque no sepamos el autor.
Para eso utilizamos LEFT JOIN.
Este tipo de combinación toma todos los registros válidos de la primera tabla (o tabla izquierda/left) y los combina con los registros de la otra tabla (derecha). Si no hay ningún registro que se pueda combinar lo hace con uno nuevo cuyos campos están todos en NULL.
En nuestros datos actuales tenemos tres libros y conocemos a sus autores correspondientes, así que al pedir la información a la base de datos nos devolverá este resultado:
SELECT * FROM books LEFT JOIN authors ON books.author_id = authors.id+----+---------------------+-----------+------+----------------+
| id | title | author_id | id | author |
+----+---------------------+-----------+------+----------------+
| 1 | El quijote | 1 | 1 | Cervantes |
| 2 | 100 a?os de soledad | 2 | 2 | Garc?a M?rquez |
| 3 | El Principito | 3 | 3 | Saint_Exupery |
+----+---------------------+-----------+------+----------------+
3 rows in set (0,00 sec)
Ahora introduciremos un nuevo libro del cual no conocemos el autor
INSERT INTO books (title) values ('Lazarillo de Tormes');y repetimos la misma petición anterior. Este es el resultado:
+----+---------------------+-----------+------+----------------+
| id | title | author_id | id | author |
+----+---------------------+-----------+------+----------------+
| 1 | El quijote | 1 | 1 | Cervantes |
| 2 | 100 a?os de soledad | 2 | 2 | Garc?a M?rquez |
| 3 | El Principito | 3 | 3 | Saint_Exupery |
| 4 | Lazarillo de Tormes | NULL | NULL | NULL |
+----+---------------------+-----------+------+----------------+
4 rows in set (0,00 sec)
La base de datos no encuentra un registro en authors que pueda emparejar con "Lazarillo de Tormes", pero al utilizar un LEFT JOIN también nos devuelve este libro, aunque deja los campos de authors sin definir. Para nosotros es útil porque de este modo podemos saber qué libros tenemos con independencia de si tenemos los datos de autor o no. La misma petición con un INNER JOIN nos dará el siguiente resultado:
SELECT * FROM books INNER JOIN authors ON books.author_id = authors.id;+----+---------------------+-----------+----+----------------+
| id | title | author_id | id | author |
+----+---------------------+-----------+----+----------------+
| 1 | El quijote | 1 | 1 | Cervantes |
| 2 | 100 a?os de soledad | 2 | 2 | Garc?a M?rquez |
| 3 | El Principito | 3 | 3 | Saint_Exupery |
+----+---------------------+-----------+----+----------------+
3 rows in set (0,00 sec)
Ves la diferencia, ¿verdad? Con el INNER JOIN sólo se devuelven resultados "dentro" del producto cartesiano (o dicho de otro modo, se devuelven pares de registros de ambas tablas). Es decir, se toman los registros de la tabla "izquierda" y se combinan con el registro correspondiente de la tabla "derecha" que cumpla las condiciones del ON y si no existe se ignora esa fila.
Con LEFT JOIN se podría decir que se toman todos los registros de la tabla izquierda relevantes (que cumplan las condiciones de WHERE si está presente) y se combinan con el registro correspondiente de la tabla "derecha" tanto si existe o no un registro en ella que cumpla las condiciones de ON.
RIGHT JOIN
Si entendiste bien el significado de LEFT JOIN seguro que eres capaz de deducir lo que significa RIGHT JOIN. Exacto: en este caso se parte de los registros de la tabla "derecha" y se busca si hay algún registro en la tabla "izquierda" que cumpla las condiciones en ON. En caso de no encontrarlo se ponen sus campos a NULL.
Para poder verlo en acción necesitamos añadir un author a nuestra tabla, que no tenga libros.
INSERT authors (author) values ('Quevedo')A continuación ejecutamos una petición con RIGHT JOIN
SELECT * FROM books RIGHT JOIN authors ON books.author_id = authors.id;El resultado es:
+------+---------------------+-----------+----+----------------+
| id | title | author_id | id | author |
+------+---------------------+-----------+----+----------------+
| 1 | El quijote | 1 | 1 | Cervantes |
| 2 | 100 a?os de soledad | 2 | 2 | Garc?a M?rquez |
| 3 | El Principito | 3 | 3 | Saint_Exupery |
| NULL | NULL | NULL | 4 | Quevedo |
+------+---------------------+-----------+----+----------------+
4 rows in set (0,00 sec)
Como era de esperar, el registro correspondiente al author "Quevedo" aparece recogido con los campos de la tabla books en null.
Y esto es todo de momento. En próximas entregas me dedicaré a las relaciones "muchos a muchos" y a hacer algunas consideraciones sobre la utilidad de los JOIN en CakePHP:
No hay comentarios:
Publicar un comentario