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 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.

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.

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.

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.

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.

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.

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.