¿Cómo funcionan los principales SQL JOINS?

¿QUÉ SON LOS SQL JOINS?

La sentencia JOIN (unir, combinar) de SQL permite combinar registros de una o más tablas en una base de datos. En el Lenguaje de Consultas Estructurado (SQL) hay tres tipos de JOIN: interno, externo y cruzado. El estándar ANSI del SQL especifica cinco tipos de JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER y CROSS. Una tabla puede unirse a sí misma, produciendo una auto-combinación, SELF-JOIN.

En este tutorial veremos los siguientes tipos de JOINS:

• (INNER) JOIN: Devuelve registros que tienen valores coincidentes en ambas tablas
• LEFT (OUTER) JOIN: Devuelve todos los registros de la tabla izquierda y los registros coincidentes de la tabla derecha
• RIGHT (OUTER) JOIN: Devuelve todos los registros de la tabla derecha y los registros coincidentes de la tabla izquierda
• FULL (OUTER) JOIN: Devuelve todos los registros cuando hay una coincidencia en la tabla izquierda o derecha

Diferentes tipos de SQL JOINS

*En la imagen anterior falta el CROSS Join, aunque no es muy habitual su uso.

Los SQL Joins hacen posible la unión y búsqueda entre tablas de SQL

1.- ELEMENTOS NECESARIOS PARA ESTE TUTORIAL

• PC con un servidor MySQL instalado
• PhpMyAdmin instalado
• Internet (recomendado)

2.- IMPORTAR LA BASE DE DATOS

Lo primero que debemos hacer para seguir este tutorial es importar la base de datos copiando y ejecutando el siguiente código en PHPMyAdmin ó bien importar desde consola con mysqladmin el fichero oficina.sql adjuntado al final de este tutorial. Si no sabéis hacer esta parte, podéis revisar el siguiente tutorial Cómo crear y restaurar backups de MySQL desde comandos.

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

CREATE DATABASE IF NOT EXISTS `oficina` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
USE `oficina`;

CREATE TABLE `departamentos` (
 `IdDepartamento` int(11) NOT NULL,
 `Nombre` varchar(128) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

INSERT INTO `departamentos` (`IdDepartamento`, `Nombre`) VALUES
(1, 'Diseño Gráfico'),
(2, 'Desarrollo Frontend'),
(3, 'Desarrollo Backend'),
(4, 'Administración'),
(5, 'Administración de Sistemas');

CREATE TABLE `empleados` (
 `IdEmpleado` int(11) NOT NULL,
 `Nombre` varchar(128) NOT NULL,
 `Apellidos` varchar(255) NOT NULL,
 `IdDepartamento` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

INSERT INTO `empleados` (`IdEmpleado`, `Nombre`, `Apellidos`, `IdDepartamento`) VALUES
(1, 'Manuel', 'García Sánchez', 1),
(2, 'María', 'Perez Sil', 3),
(3, 'Francisco', 'Martín Martín', 2),
(4, 'Raúl', 'Gil Suárez', 1),
(5, 'Clara', 'Mar Pinal', 2),
(6, 'María', 'Gil Ruíz', NULL),
(7, 'Luis', 'Manuel Pérez', 4),
(8, 'Mary', 'Carmen Martín', NULL);


ALTER TABLE `departamentos`
 ADD PRIMARY KEY (`IdDepartamento`);

ALTER TABLE `empleados`
 ADD PRIMARY KEY (`IdEmpleado`);


ALTER TABLE `departamentos`
 MODIFY `IdDepartamento` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

ALTER TABLE `empleados`
 MODIFY `IdEmpleado` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Para este ejercicio, vamos a partir de una base de datos con 2 tablas (empleados y departamentos) relacionadas a través de un campo llamado "IdDepartamento":

Tabla Empleados

A continuación se muestra la tabla de departamentos:

Tabla Departamentos

3.- INNER JOIN SQL

El INNER JOIN (ó simplemente JOIN) es el mas habitual, y lo que se consigue con esta opción es buscar coincidencias entre 2 tablas, en función a una columna que tienen en común. De esta forma, conseguimos que se muestra la intersección entre ambas tablas con los mismos resultados relacionados por su columna dada. En el siguiente ejemplo se muestra un INNER JOIN entre las 2 tablas relacionándolas entre ellas por el campo IdDepartamento.

INNER JOIN

La sintaxis para hacer el INNER JOIN entre las dos tablas relacionándolas a través del campo IdDepartamento es la siguiente:

SELECT * 
FROM empleados E
JOIN departamentos D
ON E.IdDepartamento = D.IdDepartamento;

El resultado de la anterior consulta de SQL es el siguiente y se puede ver que salen todas las columnas de ambas tablas. Como puede verse, solamente salen los resultados cuando coinciden el IdDepartamento de ambas tablas. Las 4 primeras columnas corresponden a la primera tabla (empleados) y las dos columnas finales corresponden con la segunda tabla:

Resultado de INNER JOIN

Si queremos mostrar solamente determinadas columnas, ejecutaremos algo como lo siguiente (mostramos solo Nombre, Apellidos y columna Departamento de la tabla departamentos):

SELECT
    E.Nombre as 'Nombre',
    E.Apellidos as 'Apellidos',
    D.Nombre as 'Departamento'
FROM empleados E
JOIN departamentos D
ON E.IdDepartamento = D.IdDepartamento;

El resultado de la consulta SQL anterior donde solo mostramos 3 columnas es el siguiente:

Resultad INNER JOIN con 3 columnas

4.- LEFT JOIN SQL

LEFT JOIN a diferencia de INNER JOIN se da prioridad a la tabla de la izquierda y buscamos en la tabla derecha. Al igual que en INNER JOIN, si no existe ninguna coincidencia para alguna de las filas de la tabla de la izquierda en la tabla de la derecha, se mostrarán como NULL:

LEFT JOIN

Un ejemplo de INNER JOIN para las dos tablas del ejemplo es el siguiente:

SELECT
    E.Nombre as 'Nombre',
    E.Apellidos as 'Apellidos',
    D.Nombre as 'Departamento'
FROM empleados E
LEFT JOIN departamentos D
ON E.IdDepartamento = D.IdDepartamento;

El resultado de la consulta SQL anterior, donde solo mostramos 3 columnas con LEFT JOIN es el siguiente y puede verse que las columnas de la segunda tabla sin coincidencias, aparecen como NULL:

Resultado de LEFT JOIN

5.- RIGHT JOIN SQL

RIGHT JOIN a diferencia de INNER JOIN se da prioridad a la tabla de la derecha y buscamos en la tabla izquierda. Al igual que en INNER JOIN, si no existe ninguna coincidencia para alguna de las filas de la tabla de la derecha en la tabla de la izquierda, se mostrarán como NULL:

RIGHT JOIN

Un ejemplo de INNER JOIN para las dos tablas del ejemplo es el siguiente:

SELECT
    E.Nombre as 'Nombre',
    E.Apellidos as 'Apellidos',
    D.Nombre as 'Departamento'
FROM empleados E
RIGHT JOIN departamentos D
ON E.IdDepartamento = D.IdDepartamento;

El resultado de la consulta SQL anterior, donde solo mostramos 3 columnas con RIGHT JOIN es el siguiente y puede verse que las columnas de la primera tabla sin coincidencias, aparecen como NULL:

Resultado de RIGHT JOIN

6.- FULL JOIN SQL

MySQL no soporta FULL JOIN, por lo tanto, debemos combinar LEFT JOIN y RIGHT JOIN para obtener el equivalente a FULL JOIN. FULL OUTER JOIN (o simplemente FULL JOIN) muestra los datos de todas las filas y todas las tablas. No importa que no existen coincidencias, ya que mostrará dichos campos como NULL.

FULL OUTTER JOIN

Un ejemplo de "FULL OUTER JOIN" para las dos tablas del ejemplo es el siguiente:

SELECT * FROM empleados E
LEFT JOIN departamentos D ON E.IdDepartamento = D.IdDepartamento
UNION
SELECT * FROM empleados E
RIGHT JOIN departamentos D ON E.IdDepartamento = D.IdDepartamento;

El resultado de la consulta SQL anterior, se muestran todas las columnas de todas las tablas y puede verse que las columnas sin coincidencias aparecen como NULL:

Resultado "FULL OUTER JOIN" 

ENJOY!