jueves, 17 de marzo de 2016

Tipos de JOIN en SQL

En este artículo veremos diferentes formas de realizar JOINs en tablas utilizando SQL (Structured Query Language).

Las bases de datos relacional están normalizadas para eliminar la información duplicada. Con la finalidad de poder combinar información de dos o más tablas, es necesario recurrir a los JOINs (uniones).

Requisitos:
Para este artículo haré uso de la base de datos BD_FACTURACION y pueda entenderse de la mejor manera, para iniciar haremos uso sólo de 2 tablas: FAC_PRODUCTO y FAC_CATEGORIA.

Consulta - relación producto con categoría:
SELECT PR.COD_PRODUCTO,
  PR.DES_PRODUCTO,
  PR.PRE_UNIDAD,
  PR.COD_CATEGORIA
FROM FAC_PRODUCTO PR


Como podemos observar cada producto tiene asociada una categoría, es decir la tabla FAC_PRODUCTO contiene el campo COD_CATEGORIA es decir con este campo se unirán datos de amblas tablas. 
Para analizar con mayor profundidad los tipos de JOIN modificaré el campo COD_CATEGORIA para que dicho campo soporte valores nulos (null) de la tabla FAC_PRODUCTO e ingresamos dos nuevos registros; con este cambio no se perderá de ninguna manera la relación entre ambas tablas.

Consulta - validar cambios realizados:
SELECT PR.COD_PRODUCTO,
  PR.DES_PRODUCTO,
  PR.PRE_UNIDAD,
  PR.COD_CATEGORIA
FROM FAC_PRODUCTO PR
GO
SELECT CA.COD_CATEGORIA,
  CA.DES_CATEGORIA

FROM FAC_CATEGORIA CA


Explicación del cambio realizado:
Con los cambios realizados ahora observamos que hay dos productos (Batería de 6 y 3 cedas) que no tiene asignada ninguna categoría aún y existe categorías (MEMORIA, LAPTOP, MONITOR Y TARJETA) la cual no se relaciona con ningún PRODUCTO.

INNER JOIN:
La forma más utilizada de combinación entre tablas se llama INNER JOIN, y el resultado de las uniones es el cálculo cruzado de todos los registros de las tablas. Es decir se combinaremos los registros de la tabla FAC_PRODUCTO con la tabla FAC_CATEGORIA, al aplicar el INNER JOIN sólo van a permanecer los registros que cumplan la condición especificada.

Para todos los ejemplos aplicaremos el diagrama de Venn:
La consulta a utilizar es la siguiente:
/* APLICANDO INNER JOIN */
SELECT PR.COD_PRODUCTO,
  PR.DES_PRODUCTO,
  PR.PRE_UNIDAD,
  CA.COD_CATEGORIA,
  CA.DES_CATEGORIA
FROM FAC_PRODUCTO PR
  INNER JOIN FAC_CATEGORIA CA
ON  PR.COD_CATEGORIA = CA.COD_CATEGORIA

Como resultado obtenemos los siguientes datos:
Explicación del resultado - Inner Join:
Como notamos en el resultado no aparecen el PRODUCTO  Batería de 6 y 3 cedas, ni las categorías MEMORIA, LAPTOP, MONITOR Y TARJETA. Es importante tener especial consideración con los valores nulos (null), ya que estos no se combinan ni siquiera con otros valores nulos (a menos que se utilicen los predicados IS NULL o IS NOT NULL).

LEFT JOIN:
El resultado que devuelve este tipo de JOIN son todas las filas de la tabla que se encuentre a la izquierda, es decir la tabla FAC_PRODUCTO sin importar si coinciden con las filas de la derecha, es decir la tabla FAC_CATEGORIA. Para este caso necesitamos listar a todos los productos, sin importar si pertenecen o no a una categoría, para este caso se aplica LEFT JOIN.

Diagrama de Venn:
La consulta a utilizar es la siguiente:
SELECT PR.COD_PRODUCTO,
  PR.DES_PRODUCTO,
  PR.PRE_UNIDAD,
  CA.COD_CATEGORIA,
  CA.DES_CATEGORIA
FROM FAC_PRODUCTO PR
  LEFT JOIN FAC_CATEGORIA CA
ON  PR.COD_CATEGORIA = CA.COD_CATEGORIA

Como resultado obtenemos los siguientes datos:
Consideración - Left Join:
Además, si lo que buscamos es obtener los productos que no poseen ninguna categoría asignada, tenemos que agregar la cláusula WHERE y el predicado IS NULL a nuestra consulta creada.

Diagrama de Venn:
La consulta a utilizar es la siguiente:
SELECT PR.COD_PRODUCTO,
  PR.DES_PRODUCTO,
  PR.PRE_UNIDAD,
  CA.COD_CATEGORIA,
  CA.DES_CATEGORIA
FROM FAC_PRODUCTO PR
  LEFT JOIN FAC_CATEGORIA CA
ON  PR.COD_CATEGORIA = CA.COD_CATEGORI

WHERE CA.COD_CATEGORIA IS NULL

De acuerdo al agregado del is null como resultado obtenemos los siguientes datos:

RIGHT JOIN:
De la misma forma que podemos obtener todas las filas de la tabla de la izquierda con LEFT JOIN, con RIGHT JOIN obtenemos todas las filas de la tabla derecha, sin importar si coinciden con la data de la tabla de lado izquierdo.

Diagrama de Venn:
La consulta a utilizar es la siguiente:
SELECT PR.COD_PRODUCTO,
  PR.DES_PRODUCTO,
  PR.PRE_UNIDAD,
  CA.COD_CATEGORIA,
  CA.DES_CATEGORIA
FROM FAC_PRODUCTO PR
  RIGHT JOIN FAC_CATEGORIA CA
ON  PR.COD_CATEGORIA = CA.COD_CATEGORIA

Como resultado obtenemos los siguientes datos:

Consideración - Right Join:
Ahora, si queremos las categorías que no posean ningún producto, deberemos modificar nuestra consulta de la siguiente manera:
/* APLICANDO RIGHT JOIN PRODUCTO SIN CATEGORIA */
SELECT PR.COD_PRODUCTO,
  PR.DES_PRODUCTO,
  PR.PRE_UNIDAD,
  CA.COD_CATEGORIA,
  CA.DES_CATEGORIA
FROM FAC_PRODUCTO PR
  RIGHT JOIN FAC_CATEGORIA CA
ON  PR.COD_CATEGORIA = CA.COD_CATEGORIA
WHERE PR.COD_CATEGORIA IS NULL

Y el diagrama de venn se representa del siguiente modo:
FULL JOIN:
Se utiliza cuando queremos mostrar todas las filas de las dos tablas, sin importarnos que tengan coincidencias entre sí. MySQL no soporta este tipo de JOIN, por lo tanto para lograr el mismo resultado, hay que hacer LEFT JOIN + RIGHT JOIN (el signo más sería reemplazado por el UNION ALL).

Diagrama de Venn:

La consulta a utilizar es la siguiente:
/* APLICANDO FULL JOIN */
SELECT PR.COD_PRODUCTO,
  PR.DES_PRODUCTO,
  PR.PRE_UNIDAD,
  CA.COD_CATEGORIA,
  CA.DES_CATEGORIA
FROM FAC_PRODUCTO PR
  FULL JOIN FAC_CATEGORIA CA
ON  PR.COD_CATEGORIA = CA.COD_CATEGORIA

Como resultado obtenemos los siguientes datos:

Consideración - Full Join:
Para optimizar la consulta y obtener tanto los productos que no tienen categoría o como las categorías que no tienen productos, podemos hacer uso del FULL JOIN con una cláusula (WHERE), un predicado (IS NULL) y un operador (OR).

Diagrama de venn:
La consulta a utilizar es la siguiente:
/* APLICANDO FULL JOIN - LAS QUE NO TIENE RELACION */
SELECT PR.COD_PRODUCTO,
  PR.DES_PRODUCTO,
  PR.PRE_UNIDAD,
  CA.COD_CATEGORIA,
  CA.DES_CATEGORIA
FROM FAC_PRODUCTO PR
  FULL JOIN FAC_CATEGORIA CA
ON  PR.COD_CATEGORIA = CA.COD_CATEGORIA
WHERE PR.COD_CATEGORIA IS NULL OR CA.COD_CATEGORIA IS NULL

Como resultado obtenemos los siguientes datos:

CROSS JOIN:
Para finalizar existe el CROSS JOIN, el cual no puede ser explicado con un diagrama de Venn. Lo que hace, es unir todo con todo, es decir realiza el producto cartesiano. Como tenemos 12 productos y 8 categorías (12 x 8 = 96), obtendremos 96 registros en el resultado final. Este tipo de JOIN puede resultar pesado en el nivel de rendimiento de la base de datos, mucho aun si lo aplicamos a tablas con demasiada data. Una de las principales características es que no tiene una relación entre sí (en nuestro caso se descarta los campos PR.COD_CATEGORIA = CA.COD_CATEGORIA).

La consulta a utilizar es la siguiente:
/* APLICANDO CROSS JOIN */
SELECT PR.COD_PRODUCTO,
  PR.DES_PRODUCTO,
  PR.PRE_UNIDAD,
  CA.COD_CATEGORIA,
  CA.DES_CATEGORIA
FROM FAC_PRODUCTO PR
  CROSS JOIN FAC_CATEGORIA CA

Como resultado obtenemos los siguientes datos:

RESUMEN:
Existen 3 tipos de JOINS:

1. INNER.
2. OUTER (en SQL los OUTER son las combinaciones externas especificadas en la cláusula FROM con los LEFT JOIN o LEFT OUTER JOIN, RIGHT JOIN o RIGHT OUTER JOIN, FULL JOIN y FULL OUTER JOIN).
3. CROSS

Cada uno de los JOINS nos permite hacer combinaciones de tablas, para obtener el resultado que necesitamos mostrar como producto final de las consultas construidos.