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

Esta entrada fué publicada el 3/11 del 2008 a las 3:19 am y esta archivada bajo Gadgets y Tecnologia. Puedes seguir cualquier comentario de esta entrada a travez del Feed RSS 2.0. Puedes dejar un comentario, o un trackback desde tu propio sitio.

27 Respuestas en “Entendiendo los Mysql JOIN”

base de datos de argentina ,chile , brasil, españa y uruguay Dice:
Noviembre 3rd, 2008 a las 4:19 pm
Hola, como estas, tu pagina esta excelente, si queres pasate por mi pagina y dejame un comentario, estan entrando mucho en mi sitio, si te interesa podemos hacer un intercambio de links, banners, cualquier cosa avisame, te dejo mi email tvinternet08@gmail.com, te mando un abrazo.
DiV666 Dice:
Noviembre 17th, 2008 a las 11:16 am
Increible tutorial sobre el uso del Join, me ha aclarado muchas dudas sobre esta sentencia “maldita” para mi.

Muchas Gracias.
Alex Dice:
Enero 26th, 2009 a las 3:55 pm
el FULL OUTER JOIN
no sirve en mysql
kenteli Dice:
Abril 2nd, 2009 a las 1:02 am
Estupendo tutorial:
Un detalle full outer join no está implementado en mysql(Aqui oracle gana).

Gracias por tu aportación.
Retrokaoz Dice:
Enero 12th, 2010 a las 9:07 pm
ecxeleeeeeeeeeeeeenteeee
@chinoger00 Dice:
Febrero 6th, 2010 a las 2:14 pm
Excelente la explicación Pastrana muy entendible,, no conocía tus dotes d developer XD . Saludos!
sunsue Dice:
Marzo 1st, 2010 a las 7:59 am
Genial con esos ejemplos y esos gráfico queda todo muy claro. Gracias!!!
nanot Dice:
Julio 8th, 2010 a las 8:37 am
Fantásticamente bien explicado. Nunca lo había entendido y esta vez si. Gracias.
Julian Dice:
Julio 8th, 2010 a las 2:21 pm
Tengo la siguiente consulta:

SELECT funcionario.id, funcionario.nombre, funcionario.cargo
FROM funcionario, responsablelaboral, responsablecivil
WHERE funcionario.id = responsablelaboral.funcionario_id OR
funcionario.id = responsablecivil.funcionario_id
GROUP BY funcionario.id

el resultado es vacio, lo que se pretende es consultar es todos los funcionarios que tengan alguna responsabilidad.
GRACIAS
links for 2010-07-09 « Java Asgaya Dice:
Julio 9th, 2010 a las 6:01 am
[...] Entendiendo los Mysql JOIN | Mauricio Pastrana – Since 1982 (tags: mysql howto join tutoriales sql) [...]
ZoomWolf Dice:
Agosto 24th, 2010 a las 11:44 am
Oye excelente tu post, me sirvio de mucho ya que me ahorre codigo a lo cañon, asi quedo mi vista en ves de hacer todopor codigo funete solo uso la vista que cree:

select curriculum.num_empl, empleados.completo as nombre, ct_desfun.descripcion as puesto, nivel_esco.descripcion as nivel_de_estudios,
carrera.descripcion as carrera, curriculum.dependencia, curriculum.puesto, curriculum.fecha_ini as fecha_de_inicio, curriculum.fecha_fin as fecha_fin
from curriculum
left outer join empleados on curriculum.num_empl = empleados.num_empl
left outer join ct_desfun on empleados.funcion = ct_Desfun.funcion
left outer join escolaridad on escolaridad.num_empl = curriculum.num_empl
left outer join nivel_esco on escolaridad.niv_esc = nivel_esco.id_nivel
left outer join carrera on carrera.id = escolaridad.prof_Car
Carolina Dice:
Septiembre 19th, 2010 a las 2:57 pm
Buenísimo! Siempre me hice líos con el JOIN, con esto ya se entiende bien!

Gracias!
Botiz Dice:
Septiembre 23rd, 2010 a las 9:37 am
Gracias señor Pastrana (:
Paco Mondragon Dice:
Diciembre 7th, 2010 a las 7:15 pm
Muy fácil de entender, ya resolviste mis problemas, es solo cuestion de buscar en los diferentes casos que mencionas y aplicarlo.

Mil Gracias.
rezorte Dice:
Enero 10th, 2011 a las 9:08 pm
Excelente post!!!!
Rodrigo Dice:
Febrero 17th, 2011 a las 5:20 am
Quiero agradecerte enormemente la explicación, fácil y sencilla de entender. Me ha venido genial para la página web que estoy desarrollando.

Un saludo.
leonardo Dice:
Febrero 27th, 2011 a las 5:08 pm
Hola yo quisiera saber como hago esto cuando necesito usar mas de dos tablas en una misma consulta? gracias
Javier RuizCanela Dice:
Marzo 16th, 2011 a las 12:28 pm
Gracias, más claro agua.
luis Dice:
Marzo 24th, 2011 a las 2:29 pm
muy bueno
yeahh Dice:
Abril 1st, 2011 a las 3:31 pm
Es la mejor explicacion para JOIN que ehe encontrado, grafica, explicita, de lujo, gracias
pcvm Dice:
Mayo 13th, 2011 a las 9:54 am
muchisimas gracias fue lo mejor q encontre
Shani Dice:
Mayo 19th, 2011 a las 5:17 pm
Excelente Material
Cual seria el equivalente al Natural Join
y al Semi Join de antemano gracias
Diego Martinez Dice:
Julio 13th, 2011 a las 12:40 pm
Muchisimas gracias amigo, super explicado
camilo Dice:
Agosto 1st, 2011 a las 12:59 pm
este recurso también puede servir mucho de ayuda http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html
reyvolsam Dice:
Septiembre 27th, 2011 a las 10:44 am
coincido con los demas es la mejor explicacion que he encontrado sobre el JOIN…..

Garcias, muy buena la explicacion, al fin ya le pude entender….Saludos!!
andresecuador Dice:
Octubre 8th, 2011 a las 9:49 am
comentar es agradecer men!! muchas gracias excelente explicación
Mauricio Pastrana Dice:
Febrero 1st, 2012 a las 10:59 pm
Justo de ahí viene (con permiso de Jeff mismo)

Mauricio Pastrana – Since 1982