Entendiendo los JOIN en SQL y MySQL

CONSULTAS versus JOIN

Hasta este momento la forma en la que estamos construyendo las consultas puede que nos devuelvan los resultados deseados, pero no de forma óptima.
Si ejecutamos una consulta que recorre varias tablas y luego sobre ellas aplicamos varias condiciones para filtrar los datos y mostrar lo que nos interesa, podemos considerar que nuestra construcción de SELECT son poco prácticas en términos de velocidad y eficiencia.

Para mejorar la eficiencia en las búsquedas existen y se usan los JOIN.

En la Wikipedia podemos encontrar una explicación bastante clara de como funcionan los diferentes tipos de JOIN:

La sentencia JOIN (unir, combinar) de SQL permite combinar registros de una o más tablas en una base de datos relacional.

En el Lenguaje de Consultas Estructurado SQL hay tres tipos de JOIN:

• Interno
• Externo
• Cruzado

El estándar ANSI del SQL especifica cinco tipos de JOIN:

• INNER (JOIN Interno)
• LEFT OUTER (JOIN Externo)
• RIGHT OUTER (JOIN Externo)
• FULL OUTER (JOIN Externo)
• CROSS (JOIN Cruzado)

Además na tabla puede unirse a sí misma, produciendo lo que se denomina una auto-combinación, SELF-JOIN.

EXPLICACIÓN GRAFICA DE LOS JOIN

COMBINACIÓN INTERNA

• INNER JOIN

Con esta operación cada registro en la tabla 1 es combinado con los correspondientes de la tabla 2 que satisfagan las condiciones que se especifiquen en el predicado del JOIN.

Es necesario tener especial cuidado cuando se combinan columnas con valores nulos NULL, ya que el valor nulo no se combina con otro valor o con otro nulo, excepto cuando se le agregan predicados tales como: IS NULL o IS NOT NULL.

COMBINACIONES EXTERNAS

Mediante esta operación no se requiere que un registro en una tabla tenga un registro relacionado en la otra tabla.

El registro es mantenido en la tabla combinada aunque no exista el correspondiente en la otra tabla.

• LEFT OUTER JOIN (ó simplificado como LEFT JOIN)

El resultado de esta operación siempre contiene todos los registros de la tabla de la izquierda (la primera tabla que se menciona en la consulta), independientemente de si existe un registro correspondiente en la tabla de la derecha.

La sentencia LEFT JOIN retorna la pareja de todos los valores de la tabla izquierda con los valores de la tabla de la derecha correspondientes, si los hay, o retorna un valor nulo NULL en los campos de la tabla derecha cuando no haya correspondencia.

• RIGHT OUTER JOIN (ó simplificada como RIGHT JOIN)

Esta operación es una imagen refleja de la anterior; el resultado de esta operación siempre contiene todos los registros de la tabla de la derecha (la segunda tabla que se menciona en la consulta), independientemente de si existe o no un registro correspondiente en la tabla de la izquierda.

• FULL OUTER JOIN (ó simplificada como FULL JOIN)

Esta operación presenta los resultados de tabla izquierda y tabla derecha aunque alguna no tengan correspondencia en la otra tabla. La tabla combinada contendrá, entonces, todos los registros de ambas tablas y presentará valores nulos NULLs para registros sin pareja.

COMBINACIÓN CRUZADA

• CROSS JOIN

El CROSS JOIN presenta el producto cartesiano de los registros de las dos tablas.

La tabla resultante tendrá todos los registros de la tabla izquierda combinados con cada uno de los registros de la tabla derecha.

El código SQL para realizar este producto cartesiano enuncia las tablas que serán combinadas, pero no incluye algún predicado que filtre el resultado.

EJEMPLO DE USO DE LOS DISTINTOS JOIN

INNER JOIN

El INNER JOIN, es un tipo de combinación interna que permite hallar el grupo de registros que coinciden entre las tablas que se están consultando, esto reduce directamente el número de registros con los que trabajar.

Vamos a transformar una consulta con INNER JOIN.

Ejemplo de consulta: "Mostrar todas los titulos de las publicaciones con los datos del autor y el hashtag de la categoría"

Si usamos un formato de SELECT MULTI-TABLA escribiríamos una consulta más o menos así:

SELECT autor.nombre, autor.apellidos, publicacion.titulo, categoria.hashtag
FROM publicacion, categoria, autorWHERE autor.id=publicion.autor_id AND categoria.id = publicacion.hashtag_id;

Para transformar esta consulta en una combinacion INNER JOIN hay que plantearse y varias cosas.

La parte de la SELECT va a ser la misma, ya que los datos que queremos obtener son exactamente los mismos:

SELECT autor.nombre, autor.apellidos, publicacion.titulo, categoria.hashtag

Ahora hay que tener claro la parte del FROM que si que va a cambiar. Es importante pensar cual es la TABLA PRINCIPAL.

En nuestro caso la tabla principal será la tabla PUBLICACION, pues ahí es donde están todos los datos que queremos mostrar, ya que "queremos mostrar los titulos publicados". Uniremos esta tabla a las otras (autor y categoria) para obtener el resto de datos.

Para realizar la unión de tablas usamos la sintaxis:

INNER JOIN tabla_a_unir ON campos_de_union

En nuestro caso el FROM quedará definido de la siguiente manera:

FROM publicacion
INNER JOIN autorON publicacion.autor_id = autor.id

Con esto tenemos la parte de los datos de los autores, ahora nos falta incluir los datos de los hashtag. Para ello incluimos otro INNER JOIN de forma similar a como realizamos el anterior:

INNER JOIN categoria
ON publicacion.categoria_id = categoria.id

De esta forma la SELECT final queda de la siguiente forma:

SELECT autor.nombre, 
   autor.apellidos, 
   publicacion.titulo, 
   categoria.hashtag
FROM publicacion
INNER JOIN autor
	ON publicacion.autor_id = autor.id
INNER JOIN categoria
	ON publicacion.categoria_id = categoria.id;

Fíjate que la sintaxis sigue una lógica sencilla:

FROM TP               // Siempre indicamos aqui la Tabla Principal
INNER JOIN TS         // TablaS - Es la tabla que vamos a unir
ON TP.campo_id_ts = TS.id

El truco es que siempre en el ON pongas primero la tabla principal (TP) y como campo el que se relaciona con la tabla secundaria (campo_id_ts). Luego la coincidencia (=). Y por ultimo la tabla secundaria (TS) con su campo clave de esa tabla secundaria.

Recuerda que puedes usar más de un INNER JOIN en una consulta. De forma general una consulta que utilice INNER JOIN será mucho más rápida y eficaz que un "multi-select". Puedes comprobar las diferencias de velocidad mediante el comando EXPLAIN.

LEFT OUTER JOIN / LEFT JOIN

Vamos coger otro ejemplo de consulta para transformarla y optimizarlo mediante JOIN.

Ejemplo de consulta: "Mostrar el nombre de todas las categorías y cuantas publicaciones hay de cada una"

Si usásemos una SELECT normal escribiríamos:

SELECT c.categoria, COUNT(p.id) AS 'Total publicaciones'
FROM categoria as c, publicacion p
WHERE c.id = p.categoria_id
GROUP BY c.id;

Podemos mejorar y acelerar la búsqueda de los datos transformado esta consulta mediante un INNER JOIN (como vimos antes), y quedaría así:

SELECT c.categoria, COUNT(p.id) AS 'Total publicaciones'
FROM categoria as c  		// Nuestra tabla principal
INNER JOIN publicacion p    // Tabla secundaria de la que sacar datos
ON c.id = p.categoria_id
GROUP BY c.id;

Sin embargo ambas select son "casi correctas", ya que muestran todos las categorías excepto la categoría en las que no se ha publicado nada, lo cual es incorrecto. Para arreglar este vamos a cambiar la búsqueda y usaremos LEFT OUTER JOIN (LEFT JOIN).

El cambio que vamos a realizar va a ser simplemente cambiar INNER JOIN por LEFT OUTER JOIN:

SELECT c.categoria, COUNT(p.id) AS 'Total publicaciones'
FROM categoria as c
LEFT OUTER JOIN publicacion p
ON c.id = p.categoria_id
GROUP BY c.id;

¿Por qué funciona ahora la consulta?

La clausula LEFT JOIN, selecciona TODOS los registros de la tabla de la izquierda (tabla principal) incluso si no hay coincidencia con la tabla con la que se une, por eso nos permite obtener esos registros revoltosos que nos faltaban. Si un registro de la izquierda no tiene coincidencia con la tabla de cruce devolverá un valor CERO ó NULL.

Como resumen de su funcionamiento:

LEFT JOIN → Mantiene en el resultado todos los registros de la tabla Izquierda (tabla principal).

RIGHT OUTER JOIN / RIGHT JOIN

El funcionamiento de RIGHT JOIN es justo al contrario de LEFT.

En este caso los valores de los que se obtienen todos los registros es de la tabla de la derecha (con la que realizamos la unión, no de la tabla principal).

Es fácil transformar una consulta LEFT en RIGHT con pocos cambios en la consulta:

Como resumen de su funcionamiento:

RIGHT JOIN → Mantiene en el resultado todos los registros de la tabla Derecha (tabla de unión).