Explicación de Joins Con Diagramas de Venn

A continuación voy a explicar a cerca de los inner join de una manera gráfica con Diagramas de Venn. En respuesta a todos los informáticos que critican porque me pasan tanta algebra, calculo, física, si no sirve de nada, claro que sirve y bastante. Para todos los ejemplos vamos a tener dos tablas, la tabla cliente y la tabla paciente, utilizando el Motor de datos MySQL. Por nomenclatura siempre uso nombres singulares para las base de datos y para las tablas, para los campos de las tablas utilizo CamelCase
A continuación se muestra el diagrama de las tablas utilizadas para el ejemplo:

Se agregaron los siguientes datos para realizar el tutorial:
Tabla Cliente:

 

Tabla Paciente:

Descargar Script para generar tablas del tutorial.

INNER JOIN

El inner join viene siendo la intersección de los registros que se encuentran en ambas tablas, esto también se puede lograr con un where, incluyendo el campo de intersección entre ambas tablas, en este ejemplo queremos saber a que cliente corresponden los pacientes, a continuación el diagrama y el sql de ambos casos.

SELECT paciente.id as idPaciente, paciente.nombrePaciente, paciente.idCliente, cliente.nombreCliente 
FROM paciente 
INNER JOIN cliente 
ON (cliente.id = paciente.idCliente); -- Intersección con la tabla cliente y condiciones que delimitarán los campos resultantes en la intersección de pacientes con clientes.

Se Creo un alias al id de la tabla paciente, para que sea más clara la diferencia de los id de ambas tablas.

El Resultado es el siguiente

Como se puede apreciar, Juan no aparece, porque no tiene pacientes asociados, es decir no esta dentro de la intersección de ambas tablas. Ahora veamos como es esto con un simple where, pero es más eficiente utilizar el inner join, además de más simple a la vista, generalmente algunas personas cometen el error de hacer muchos where o añadir demasiadas condiciones y generar SELECT ANIDADOS, generando querys recursivas, cuando esto puede ser totalmente evitable con la utilización inners. En otra sección haré mayor referencia a la clausula where

SELECT paciente.id as idPaciente, paciente.nombrePaciente, paciente.idCliente, cliente.nombreCliente 
FROM paciente, cliente
WHERE paciente.idCliente =  cliente.id -- Producto punto entre ambas tablas, con los resultados filtrados con la clausula where

LEFT OUTER JOIN

Corresponde a todos los elementos de la tabla cliente, incluyendo los que intersectan con la tabla paciente, pero excluyendo los pacientes que no están asociados a clientes. En caso de querer sacar los elementos del conjunto de la derecha aplicamos RIGHT OUTER JOIN, esto lo utlizaremos más abajo para simular el FULL OUTER JOIN

SELECT paciente.id as idPaciente, paciente.nombrePaciente, paciente.idCliente, cliente.nombreCliente 
FROM cliente 
LEFT OUTER JOIN paciente
ON cliente.id = paciente.idCliente -- Corresponde a todos los clientes, con el detalle de los pacientes asociados, en caso de no tener paciente aparecerá nulo (Juan).

 

El resultado es el siguiente


LEFT OUTER JOIN WHERE

Corresponde a todos los clientes que no tienen pacientes asociados, es decir, todos los elementos de la tabla clientes, excluyendo la intersección entre clientes y pacientes y los elementos de paciente.

La consulta es la siguiente:

SELECT paciente.id as idPaciente, paciente.nombrePaciente, paciente.idCliente, cliente.nombreCliente 
FROM cliente 
LEFT OUTER JOIN paciente 
ON (cliente.id = paciente.idCliente) 
WHERE paciente.idCliente IS NULL

Y el resultado Obtenido es:

FULL OUTER JOIN

Corresponde a la totalidad de los elementos y cuando no encuentre semejanzas, como el caso de juan, completará con null los campos sin datos.

En MySQL no existe dicha función, pero enseñare como simularla. Si utilizamos PostgreSQL (motor que recomiendo), no debiésemos tener problema.

SELECT paciente.id as idPaciente, paciente.nombrePaciente, paciente.idCliente, cliente.nombreCliente 
FROM cliente 
FULL OUTER JOIN paciente 
ON cliente.id = paciente.idCliente -- Conjunto total de elementos, completando con null campos no encontrado en el cruce.

Como ya vimos en el LEFT OUTER JOIN, obtenemos todos los elementos del conjunto cliente, ahora bien si queremos Obtener todos los elementos del conjunto Paciente, hacemos un RIGHT OUTER JOIN, una manera de simular el FULL OUTER JOIN es haciendo una unión entre EL LEFT OUTER JOIN y el RIGHT OUTER JOIN, el ejemplo es como el que sigue:

SELECT paciente.id as idPaciente, paciente.nombrePaciente, paciente.idCliente, cliente.nombreCliente 
FROM cliente 
LEFT OUTER JOIN paciente ON cliente.id = paciente.idCliente
UNION -- unión del resultado entre el RIGHT y LEFT OUTER JOIN
SELECT paciente.id as idPaciente, paciente.nombrePaciente, paciente.idCliente, cliente.nombreCliente 
FROM cliente 
RIGHT OUTER JOIN paciente ON cliente.id = paciente.idCliente

Acá el resultado:

Como vemos Juan no tiene pacientes asociados, pero de todas formas lo muestra y señala que no tiene datos de paciente (null)

FULL OUTER JOIN WHERE

Corresponde a todos los elementos de la tabla cliente y tabla paciente, excluyendo la intersección entre ambas tablas. En este caso obtendremos el mismo resultado que el FULL OUTER JOIN, ya que todos los pacientes tienen cliente asociado y solo Juan no tiene pacientes asociados.

SELECT paciente.id as idPaciente, paciente.nombrePaciente, paciente.idCliente, cliente.nombreCliente FROM cliente 
FULL OUTER JOIN paciente ON cliente.id = paciente.idCliente
WHERE cliente.id IS null OR paciente.id IS null

Y como en MySQL no existe el FULL OUTER JOIN, lo simularemos de la siguiente manera:

SELECT paciente.id as idPaciente, paciente.nombrePaciente, paciente.idCliente, cliente.nombreCliente FROM cliente 
LEFT OUTER JOIN paciente ON cliente.id = paciente.idCliente
WHERE cliente.id IS null OR paciente.id IS null
UNION
SELECT paciente.id as idPaciente, paciente.nombrePaciente, paciente.idCliente, cliente.nombreCliente FROM cliente 
RIGHT OUTER JOIN paciente ON cliente.id = paciente.idCliente
WHERE cliente.id IS null OR paciente.id IS null

Obteniendo el siguiente Resultado:

CROSS JOIN

Finalmente tenemos el Cross Join, que consiste en el producto cruz entre ambas tablas, es decir obtendremos todas las combinaciones posibles entre clientes y pacientes.

La consulta es la siguiente:

SELECT paciente.id as idPaciente, paciente.nombrePaciente, paciente.idCliente, cliente.nombreCliente 
FROM cliente 
CROSS JOIN paciente

Y el resultado:

Y eso ha sido todo, espero que les sea de ayuda. Cualquier sugerencia es bienvenida, si quieren que ponga ciertos temas solo avisan y en medida al tiempo iré investigando y aportando

Saludos!

Volver a sección SQL