Too Cool for Internet Explorer

JOINs para seres humanos 22

Hora y Fecha: April 22, 2007 @ 10:50 pm Autor: Moisés Maciá
Categories:
2,449 views

En este artículo voy a explicar cómo hacer uso del operador JOIN del álgebra relacional en el mundo real de las bases de datos y las ventajas que reporta en la selección de datos. No pongáis caras que no voy a hablar ni de álgebra relacional ni de teoría de conjuntos. Esto son JOINs para seres humanos :)

Qué es una JOIN

Es una operación que combina registros de dos tablas en una base de datos relacional que resulta en una nueva tabla (temporal) llamada tabla de JOIN. En el lenguaje de consulta SQL hay dos tipos de JOIN: INNER y OUTER, si bien cada vendedor añade a sus productos modificaciones y atajos para hacer más versátiles estas operaciones.

Como caso especial, una tabla (tabla base, vista o una tabla JOIN) puede realizar la operación JOIN sobre ella misma otra vez. Esto se conoce como self-JOIN.

Matemáticamente, un JOIN es una relación de composición. Estas son las operaciones fundamentales en el álgebra relacional.

Supongamos que tenemos dos tablas: una de películas y otra de directores relacionadas entre sí:

movies
id title year director
1 Four Rooms 1995 3
2 Die Hard 1988 1
3 The Hunt for Red October 1990 1
4 Psycho 1960 2
directors
id name
1 John McTiernan
2 Alfred Hitchcock
3 Quentin Tarantino

Si quiero sacar todas las peliculas y el nombre de su director haría lo siguiente:

  1. SELECT title, name
  2. FROM movies m, directors d
  3. WHERE m.director = d.id

Internamente la base de datos crearía una tabla temporal con todas las filas de la tabla movies cruzadas a su vez con todas las filas de la tabla directors, para después seleccionar las filas que cumplen la condición m.id = d.id. En total maneja 4×3 = 12 filas para obtener un resultado final de 4 registros.

En este caso no es problemático, pero cuando tenemos una tabla de un millón de registros y otra de diez millones, la cosa se vuelve muy fea: la base de datos tiene que manejar 10.000.000.000.000 filas cuando el resultado final quizá este formado por unas pocas decenas.

Aqui es donde entran en juego los JOINs: cuando aplicamos esa operación, la base de datos sólo devuelve el conjunto de filas afectadas por el JOIN, descartando todas las demás.

NOTA: esto no siempre es cierto, ya que las bases de datos modernas disponen de un optimizador de consultas que en determinados casos convertirá la sentencia SQL con el tradicional WHERE en una JOIN. Como he dicho en determinados casos funcionará bien, en otros no.

La sentencia reescrita como una JOIN quedaría de la siguiente manera:

  1. SELECT title, name
  2. FROM movies m
  3. INNER JOIN directors d ON (m.director = d.id);

INNER, OUTER, LEFT, … ¿Cuál utilizo en cada momento?

El circulo T1 representa todos los registros de nuestra primera tabla mientras que el circulo T2 representa todos los registros de la segunda tabla. Hay una intersección entre los dos circulos, eso representa los registros de ambas tablas que están relacionados entre sí por sus claves ajenas y primarias. ¿Fácil, no?

El color azul representará los datos que devuelve cada tipo de JOIN.

empty join

INNER JOIN

Una INNER JOIN sólo devuelve aquellos registros que coinciden en ambas tablas. Así cada registro que devuelva T1 debe tener su pareja en T2 enlazada por una clave ajena. En términos de lógica de primer orden sería equivalente al operador AND.

inner join

OUTER JOIN

Una OUTER JOIN es la operación complementaria a una INNER JOIN. Sólo devuelve aquellos registros que no estén emparejados en T1 y en T2. En términos de lógica de primer orden sería equivalente a la operación NOT AND.

outer join

LEFT JOIN

Una LEFT JOIN devuelve los registros que están en la tabla de la izquierda (T1) tanto si tienen pareja en T2 como si no.

Si tienen pareja, devuelve el dato relacionado. Si no, rellena los huecos con NULL.

left join

Es posible hacer la misma operación con la tabla de la derecha, en ese caso estaríamos hablando de una RIGHT JOIN pero lo habitual es utilizar como pivote siempre la izquierda.

LEFT OUTER JOIN

Una LEFT OUTER JOIN combina las ideas de la LEFT JOIN y la OUTER JOIN. Basicamente si utilizas una LEFT OUTER JOIN obendrás los registros de la tabla izquierda que no emparejan con ninguno de los de la tabla de la derecha.

left outer

De nuevo, se puede realizar la operación equivalente en la tabla de la derecha aunque no suele ser lo habitual.

θ JOIN

La composición Theta es el producto cartesiano de dos tablas. Existe como operación matemática pero normalmente no es una consulta que la gente utilice, porque devuelve todos los registros de todas las tablas.

theta join

Más sobre JOINs

Dependiendo del RDBMS que utilicéis, os será posible utilizar más tipos de composiciones (por ejemplo MySQL soporta las NATURAL JOIN y STRAIGHT JOIN) así como definir composiciones que afecten a mas de dos tablas.

Estas que he comentado están disponibles en la mayoría de las bases de datos modernas.





« Anterior post: Piratas de Silicon Valley | Próximo post: Emacs is Superman »

22 Comentarios para “JOINs para seres humanos”

jgaliana
23 de April de 2007 a las 12:40 am    

Muy bien explicado e interesante!

Un Saludo

Antonio
23 de April de 2007 a las 9:06 am    

Genial la explicación, por fin me quedó completamente claro. Gracias!

meneame.net
23 de April de 2007 a las 11:22 am    

JOINs para seres humanos…

En este artículo voy a explicar cómo hacer uso del operador JOIN del álgebra relacional en el mundo real de las bases de datos y las ventajas que reporta en la selección de datos. No pongáis caras que no voy a hablar ni de álgebra relacional ni d…

PaToRoCo
23 de April de 2007 a las 1:09 pm    

Llevo bastantes años enredando con PHP & MySQL, y la verdad que suelo utilizar WHERE siempre, y si me fuerzas LIKE, más que nada porque siempre me había valido, y mis consultas no suelen ser muy gordas, pero vamos, iba siendo hora de hacer un “reciclaje”, y creo que este puede ser un primer paso :)

www.programame.net
23 de April de 2007 a las 8:49 pm    

JOINs para seres humanos…

En este artículo voy a explicar cómo hacer uso del operador JOIN del álgebra relacional en el mundo real de las bases de datos y las ventajas que reporta en la selección de datos. No pongáis caras que no voy a hablar ni de álgebra relacional ni d…

nulleando.com.ar » joins para humanos
24 de April de 2007 a las 5:16 am    

[...] link [...]

Joins para seres humanos
24 de April de 2007 a las 4:55 pm    

[...] leído un interesante post en quarkblog, sobre el operador JOIN del álgebra [...]

Martin
26 de April de 2007 a las 1:46 pm    

La explicación con los circulos y conjuntos es una muy buena idea !

caminante
2 de May de 2007 a las 5:08 pm    

Genial explicacion! Estuve buscando en la red info sobre los JOINs, pero solo encontraba explicaciones dificiles. Lograste explicarlo de una manera muy sencilla. Muchas gracias! =)

Jaume Teixi
6 de May de 2007 a las 10:30 pm    

Buen y digerible artículo, aunque hay que corregir un pequeño despiste en el código:

El enlace entre movies y directors no es m.id = d.id
sino m.director = d.id

Así que el código debería quedar:
SELECT title, name
FROM movies m, directors d
WHERE m.director = d.id

y el código de la JOIN:
SELECT title, name
FROM movies m
INNER JOIN directors d ON (m.director = d.id);

Moisés Maciá
7 de May de 2007 a las 2:39 pm    

Cierto, gracias por el aviso.

[...] de utilizar los JOINS en SQL todo dependia de cual es el resultado que queriamos obtener,  hoy via programame me encuentro con un artículo bastante interesante en el que nos muestran el uso de los diferentes [...]

Salva
27 de June de 2007 a las 8:55 pm    

Enhorabuena, me ha gustado mucho :)

Sin duda visitaré más tu blog.

Saludos

Picando Código » Blog Archive » JOINS en SQL
29 de September de 2007 a las 10:46 pm    

[...] que aprendí fue a hacer los JOINS. Y de esos tiempos me acuerdo del siguiente artículo: JOINS para seres humanos. Ahí están los diagramas de conjuntos para cada tipo de Join y es recomendable leer el post para [...]

JOINS para humanos « Jamoro’s Weblog
8 de March de 2008 a las 10:46 pm    

[...] algo de informacion sobre base de datos en MySQL, me encontre con este interesante post sobre la union de tablas relacionales (JOINS), algo que es muy inportante saber cuando se tienen varias tablas dentro de una base de datos y se [...]

Guillermo
10 de April de 2008 a las 9:33 pm    

Muy bueno… me sirvió!.. gracias..
Decime como escribo theta con alguna combinación de teclas.
Saludos!
———————————————————–
mmm
>-[o_o]-<
_/ \_
————————————————————

Guille…

Guillermo
10 de April de 2008 a las 9:35 pm    

…mmm…
>-[o_o]-<
.._/ \_..

Ahora si…

Moisés Maciá
10 de April de 2008 a las 10:46 pm    

Hola, me alegro de que te sirviera de ayuda.
En la siguiente web hay un montón de caracteres y sus códigos para representarlos en HTML o donde quieras:

http://www.digitalmediaminute.com/reference/entity/index.php

[...] JOINs para seres humanos [...]

JOINS en SQL | Picando Código
25 de July de 2008 a las 2:07 am    

[...] que aprendí fue a hacer los JOINS. Y de esos tiempos me acuerdo del siguiente artículo: JOINS para seres humanos. Ahí están los diagramas de conjuntos para cada tipo de Join y es recomendable leer el post para [...]


Bad Behavior has blocked 318 access attempts in the last 7 days.