Entendiendo los Mysql JOIN

Este es un problema bastante común para los desarrolladores que trabajan sobre bases de datos:

Llaman un set de resultados y se encuentran que uno de los campos no es mas sino un listado de ID’s. Ahora como traigo los datos de ese campo?

Muy a menudo me encuentro con desarrolladores SQL que aplican la siguiente lógica para solucionar este problema:

SELECT * FROM países

foreach($países as $país) {
SELECT * FROM ciudades WHERE país = $país
foreach ($ciudades as $ciudad) {
…

Que como pueden ver, no solo es difícil de manejar sino que es imposible de crecer logicamente y en ultimas, confuso de leer. Mas que genera una cantidad de gasto de memoria tanto en el PC corriendo php como en la base de datos que genera la busqueda especifica.

Afortunadamente, tenemos la flexibilidad de una función MySQL hecha precisamente para llamar datos entre dos tablas conectadas con un campo en común: JOIN

Por ejemplo, usando JOIN, llamar un set de resultados de direcciones a base de país requeriría del siguiente query:

SELECT * FROM direcciones. AS dir
JOIN países AS p ON p.país = dir.pais
WHERE … cualquier condicion

Mejor! Esta búsqueda generaría el siguiente set hipotético de resultados para presentar usando php (ojo que esta organizado por las columnas de “país”):

 p.id  |  p.pais   | dir.id  | dir.pais  | dir.direccion
1     |  USA      | 3       | USA       | 200 West Chadderdon Avenue
2     |  Mexico   | 4       | Mexico    | 823 Monte Libano
4     |  Colombia | 2       | Colombia  | Carrera 9B Bis #117-32
3     |  Colombia | 1       | Colombia  | Calle 127 #42-45

Fácil, no? Bueno, a simple vista si, pero con esto estamos tan solo raspando la superficie del problema en el que nos podemos encontrar si tenemos dos tablas que no necesariamente contengan datos simétricos.

Para solucionar este problema, hay que considerar el juego de herramientas JOIN entero:
LEFT JOIN, RIGHT JOIN, INNER JOIN y OUTER JOIN.

Cuando me lo cruce originalmente, este post de Jeff Atwood que explica como funcionan los JOIN me soluciono una cantidad de problemas, hoy en día ya lo he reenviado tanto que decidí colgarlo aquí:

JOIN, una explicacion visual

Entonces, para lograr entender entonces que tan poderosa es la utilidad JOIN, voy a visualizar dos tablas que contengan tan solo algunos datos en común:

id nombre       id  nombre
-- ----         --  ----
1  Pirata       1   Rutabaga
2  Mico         2   Pirata
3  Ninja        3   Darth Vader
4  Spaghetti    4   Ninja

Listo? ahora veamos a ver como como caen las mezclas de datos usando JOIN

INNER JOIN

SELECT * FROM TablaA
INNER JOIN TablaB
ON TablaA.name = TablaB.name

id  name       id   name
--  ----       --   ----
1   Pirata     2    Pirata
3   Ninja      4    Ninja

Este genera una lista de resultados que existan tanto en A como en B simultaneamente

---

FULL OUTER JOIN

SELECT * FROM TablaA
FULL OUTER JOIN TablaB
ON TablaA.name = TablaB.name

id    name       id    name
--    ----       --    ----
1     Pirata     2     Pirata
2     Mico       null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

Genera un listado que incluya todos los resultados en A y en B, asi no exista relacion entre algun campo

---

LEFT OUTER JOIN

SELECT * FROM TablaA
LEFT OUTER JOIN TablaB
ON TablaA.name = TablaB.name

id  name       id    name
--  ----       --    ----
1   Pirata     2     Pirata
2   Mico       null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

Entrega todos los resultados que encuentra en la tabla A, pero tambien trae los resultados que empaten que aparezcan en la tabla B.

---

Usos y Derivados

… Conociendo como funcionan los JOIN, estos son otros ejemplos que se pueden generar usando la sintáxis JOIN completa.

SELECT * FROM TablaA
LEFT OUTER JOIN TablaB
ON TablaA.name = TablaB.name
WHERE TablaB.id IS null

id  name       id     name
--  ----       --     ----
2   Mico       null   null
4   Spaghetti  null   null

Solo quiero ver los records unicos que existan en la tabla A

SELECT * FROM TablaA
FULL OUTER JOIN TablaB
ON TablaA.name = TablaB.name
WHERE TablaA.id IS null
OR TablaB.id IS null

id    name       id    name
--    ----       --    ----
2     Mico       null  null
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

Muestrame los records que no tengan relacion alguna entre la tabla A y la tabla B

---

Y ojo que el LEFT JOIN es intercambiable en sintaxis (mas no en logica) con un RIGHT JOIN donde

SELECT * FROM TablaA
LEFT JOIN TablaB
ON TablaA.name = TablaB.name

… es equivalente a

SELECT * FROM TablaB
RIGHT JOIN TablaA
ON TablaA.name = TablaB.name
Descargar ejemplo de una base de datos aqui