OUTER JOIN: definición, tipos y ejemplos
Equipo editorial de IONOS
Una sentencia JOIN de SQL es una operación de consulta que enlaza varias tablas de una base de datos relacional y emite como respuesta los registros o tuplas filtrados según la condición de selección definida por el usuario.
- Certificado SSL Wildcard
- Registro privado
- 1 cuenta de correo electrónico por contrato
El tipo de JOIN más común del modelo de base de datos relacional es el INNER JOIN de SQL, que, en la práctica, se utiliza cuando se quieren conectar dos tablas de una base de datos a partir de una columna en común. Cada registro de una tabla se fusiona con el registro correspondiente de la otra y quedan ocultos aquellos para los que el Sistema Gestor de Bases de Datos (SGBD) no puede encontrar una coincidencia.
Frente a esta sentencia, el OUTER JOIN no solo vuelca los registros de datos que cumplen la condición de selección en las dos tablas (por ejemplo, valores iguales en dos columnas), sino también las tuplas restantes de una u otra tabla.
Siguiendo la dirección de lectura de la sintaxis de SQL se habla de una tabla izquierda y una derecha. Las operaciones respectivas se denominan en consecuencia LEFT OUTER JOIN y RIGHT OUTER JOIN. Si, en una consulta, además de los registros que cumplen la condición de selección, también deseas obtener todos los registros de datos de la tabla izquierda y de la derecha, entonces se habla de un FULL OUTER JOIN.
Los diagramas de conjuntos permiten explicar el principio que hay tras las diferentes sentencias de JOIN:
Tipos de OUTER JOIN de SQL
Cada OUTER JOIN se ejecuta como LEFT, RIGHT o FULL OUTER JOIN.
OUTER es opcional en la sintaxis SQL. Es común que los usuarios utilicen la fórmula abreviada LEFT JOIN, RIGHT JOIN y FULL JOIN
Para mostrar cómo funciona la sentencia OUTER JOIN de SQL se utilizan las tablas “empleados” y “vhc” (vehículos).
Tabla: empleados
| e_id | Apellidos | Nombre | vhc_id |
|---|---|---|---|
| 1 | García Hurtado | Macarena | 3 |
| 2 | Ocaña Martínez | Francisco | 1 |
| 3 | Gutiérrez Doblado | Elena | 1 |
| 4 | Hernández Soria | Manuela | 2 |
| 5 | Oliva Cansino | Andrea | Nulo |
La tabla “empleados” contiene los nombres y apellidos de los empleados de una empresa ficticia, así como el número de identificación de vehículo (vhc_id) que la empresa les ha asignado. La clave primaria de la tabla es un número de identificación para el empleado (e_id). Al empleado con e_id 5 (Oliva Cansino) todavía no se le ha asignado un coche de empresa. Por lo tanto, la celda de la columna correspondiente contiene un valor nulo.
El valor nulo o null se utiliza en computación para representar la ausencia de un valor. Es por eso que no se corresponde con el valor numérico 0.
Tabla: vehículos
| vhc_id | Marca | Modelo | Matrícula | Año de fabricación |
|---|---|---|---|---|
| 1 | VW | Caddy | C 0000 YZ | 2016 |
| 2 | Opel | Astra | C 0001 YZ | 2010 |
| 3 | BMW | X6 | C 0002 YZ | 2017 |
| 4 | Porsche | Boxster | C 0003 YZ | 2018 |
La tabla “vehículos” contiene la información de los automóviles de la empresa: marca, modelo, número de matrícula y año de fabricación. Además, a cada vehículo de la empresa se le asigna un número de identificación (vhc_id) que funciona como clave primaria para la tabla.
Las dos tablas están enlazadas mediante una relación de clave externa. La clave primaria de la tabla “vehículos” (vhc_id) se ha integrado como una clave externa en la tabla “empleados”. Esto permite conectar ambas tablas a partir de una columna común.
Mientras que las claves primarias válidas no pueden incluir ningún valor nulo, los valores nulos en las claves externas no dañan la integridad de un conjunto de datos.
LEFT OUTER JOIN
En un LEFT OUTER JOIN, la tabla en la parte izquierda del operador JOIN se considera la tabla dominante. En álgebra relacional, los LEFT OUTER JOIN se anotan con el siguiente operador: ⟕
Para combinar las tablas “empleados” y “vehículos” en un LEFT OUTER JOIN, se puede utilizar la siguiente operación:
empleados ⟕ vhc_id=vhc_idvhc
La interacción con el SGBD se realiza en el lenguaje de la base de datos SQL. La fórmula anterior corresponde a la siguiente declaración SQL:
SELECT * FROM empleados LEFT JOIN vhc ON empleado.vhc_id = vhc.vhc_id;La tabla “empleados” se sitúa a la izquierda del operador JOIN, y la tabla “vehículos”, a la derecha. Como condición de selección se establece empleado.vhc_id = vhc.vhc_id. El conjunto de resultados de un LEFT OUTER JOIN incluye solo los registros de la tabla derecha que cumplen la condición del JOIN y todos los resultados de la tabla de la izquierda. Es decir, de la tabla “vehículos” solo se seleccionarán aquellos registros de datos que en la columna vhc_id contengan un valor para el cual el SGBD encuentra un valor en la tabla “empleados”.
Los valores que faltan en la tabla de resultados se muestran como valores nulos.
A diferencia de INNER JOIN, en OUTER JOIN hay que prestar atención al orden de las tablas. Mientras que con LEFT JOIN son los registros de datos de la tabla a la izquierda del operador JOIN los que se muestran en su totalidad, con RIGHT JOIN ocurre con los de la derecha.
Como resultado de un LEFT OUTER JOIN, se obtiene la siguiente tabla:
Tabla: LEFT JOIN con las tablas “empleados” y “vehículos”
| e_id | Apellidos | Nombre | empleados.vhc_id | vehículos.vhc_id | Marca | Modelo | Matrícula | Año de fabricación |
|---|---|---|---|---|---|---|---|---|
| 1 | García Hurtado | Macarena | 3 | 3 | BMW | X6 | C 0002 YZ | 2017 |
| 2 | Ocaña Martínez | Francisco | 1 | 1 | VW | Caddy | C 0000 YZ | 2016 |
| 3 | Gutiérrez Doblado | Elena | 1 | 1 | VW | Caddy | C 0000 YZ | 2016 |
| 4 | Hernández Soria | Manuela | 2 | 2 | Opel | Astra | C 0001 YZ | 2010 |
| 5 | Oliva Cansino | Andrea | Nulo | Nulo | Nulo | Nulo | Nulo | Nulo |
La tabla de resultados muestra dos particularidades:
El registro de datos de la tabla “vehículos” con vhc_id 4 (Porsche Boxster) no aparece en la tabla de resultados. Esto se debe a que en la tabla “empleados” no existe una clave externa para una clave primaria con el valor 4, o lo que es lo mismo, no se cumple la condición de selección. El registro procede de la tabla derecha y, por lo tanto, con esta operación permanece oculto.
La clave externa vhc_id de la tabla “empleados” contiene un valor nulo para el registro de la empleada Oliva Cansino, motivo que hace imposible encontrar la clave primaria correspondiente en la tabla “vehículos”. Tampoco en este caso se cumple la condición de selección. Sin embargo, dado que el registro procede de la tabla de la izquierda, se vuelcan todos sus registros en la tabla de resultados de la sentencia LEFT JOIN. Los valores que faltan en la tupla de la tabla de resultados se sustituyen por Nulo.
RIGHT OUTER JOIN
El RIGHT OUTER JOIN sigue el mismo principio que el LEFT OUTER JOIN, pero la tabla dominante aquí no es la de la izquierda, sino la de la derecha.
El conjunto de resultados de un RIGHT OUTER JOIN incluye todas las tuplas de la tabla del lado derecho del operador JOIN y las tuplas de la tabla izquierda que cumplen la condición JOIN. Como operador se muestra el símbolo ⟖.
Se parte de nuevo de las tablas “empleados” y “vehículos”, utilizando la misma condición de selección que para el caso anterior.
En álgebra relacional:
empleados ⟖ vhc_id=vhc_idvhc
Declaración SQL:
SELECT * FROM empleados RIGHT JOIN vhc ON empleado.vhc_id = vhc.vhc_id;Como se puede observar, existe una gran diferencia entre la tabla de resultados del RIGHT JOIN y la de LEFT JOIN.
Tabla: RIGHT OUTER JOIN de las tablas “empleados” y “vehículos”
| e_id | Apellidos | Nombre | empleados.vhc_id | vehículos.vhc_id | Marca | Modelo | Matrícula | Año de fabricación |
|---|---|---|---|---|---|---|---|---|
| 1 | García Hurtado | Macarena | 3 | 3 | BMW | X6 | C 0002 YZ | 2017 |
| 2 | Ocaña Martínez | Francisco | 1 | 1 | VW | Caddy | C 0000 YZ | 2016 |
| 3 | Gutiérrez Doblado | Elena | 1 | 1 | VW | Caddy | C 0000 YZ | 2016 |
| 4 | Hernández Soria | Manuela | 2 | 2 | Opel | Astra | C 0001 YZ | 2010 |
| Nulo | Nulo | Nulo | 4 | 4 | Porsche | Boxster | C 0003 YZ | 2018 |
El registro de datos de la empleada Oliva Cansino no se muestra en la tabla de resultados, pues el vhc_id de la columna exterior tiene valor nulo y por lo tanto no puede asignarse a ningún registro de datos en la tabla de la derecha.
Como resultado del RIGHT JOIN obtenemos, por un lado, todos los registros de la tabla “vehículos”, incluido también el registro con el vhc_id 4, al que no se le ha podido asignar ninguna tupla de la tabla “empleados”. Los valores que faltan también se muestran aquí como valores nulos.
FULL OUTER JOIN
Un FULL OUTER JOIN es una combinación entre un LEFT OUTER JOIN y un RIGHT OUTER JOIN. Para la operación en álgebra relacional se utiliza el mismo operador que hasta ahora: ⟗
También aclaramos el FULL JOIN a partir de las tablas “empleados” y “vehículos” y asumimos la misma condición de selección que antes.
Álgebra relacional:
empleados ⟗ vhc_id=vhc_idvhc
Declaración SQL:
SELECT * FROM empleados FULL JOIN vhc ON empleados.vhc_id = vhc.vhc_id;El resultado se muestra en la siguiente tabla:
Tabla: FULL OUTER JOIN para las tablas “empleados” y “vehículos”
| e_id | Apellidos | Nombre | empleados.vhc_id | vehículos.vhc_id | Marca | Modelo | Matrícula | Año de fabricación |
|---|---|---|---|---|---|---|---|---|
| 1 | García Hurtado | Macarena | 3 | 3 | BMW | X6 | C 0002 YZ | 2017 |
| 2 | Ocaña Martínez | Francisco | 1 | 1 | VW | Caddy | C 0000 YZ | 2016 |
| 3 | Gutiérrez Doblado | Elena | 1 | 1 | VW | Caddy | C 0000 YZ | 2016 |
| 4 | Hernández Soria | Manuela | 2 | 2 | Opel | Astra | C 0001 YZ | 2010 |
| 5 | Oliva Cansino | Andrea | NULO | NULO | NULO | NULO | NULO | NULO |
| Nulo | Nulo | Nulo | 4 | 4 | Porsche | Boxster | C 0003 YZ | 2018 |
El FULL JOIN combina los registros de datos de las tablas “empleados” y “vehículos” de acuerdo con la condición de selección, aunque enumera no solo los registros de datos conectados en la tabla de resultados, sino también los registros de datos de ambas tablas que no cumplen la condición de selección.
Los valores que faltan en FULL JOIN también se indican con el valor nulo.
La sentencia FULL OUTER JOIN no se aplica muy asiduamente, motivo por el que los sistemas de gestión de bases de datos líderes en el mercado como MySQL y MariaDB no lo soportan.
NATURAL OUTER JOIN
Al igual que con los INNER JOIN, los OUTER JOIN también se pueden implementar como NATURAL JOIN con los siguientes operadores:
LEFT/RIGHT JOIN ... USINGo:
NATURAL LEFT/RIGHT JOINLos NATURAL OUTER JOIN conectan las tablas por las columnas que tienen la misma denominación. Las columnas seleccionadas se pueden definir de forma explícita con la palabra clave USING:
SELECT * FROM empleados LEFT JOIN vehículos USING(vhc_id);Como alternativa puedes usar una fórmula abreviada con la que el SGBD busca automáticamente columnas con la misma denominación y combina las tablas de la lista con ellas:
SELECT * FROM empleados NATURAL LEFT JOIN vehículos;De acuerdo con las tablas de ejemplo, ambas sentencias SQL conducen al mismo resultado:
| e_id | Apellidos | Nombre | vhc_id | Marca | Modelo | Matrícula | Año de fabricación |
|---|---|---|---|---|---|---|---|
| 1 | García Hurtado | Macarena | 3 | BMW | X6 | C 0002 YZ | 2017 |
| 2 | Ocaña Martínez | Francisco | 1 | VW | Caddy | C 0000 YZ | 2016 |
| 3 | Gutiérrez Doblado | Elena | 1 | VW | Caddy | C 0000 YZ | 2016 |
| 4 | Hernández Soria | Manuela | 2 | Opel | Astra | C 0001 YZ | 2010 |
| 5 | Oliva Cansino | Andrea | Nulo | Nulo | Nulo | Nulo | Nulo |
El NATURAL LEFT JOIN combina las columnas empleados.vhc_id y vehículos.vhc_id para formar la columna común vhc_id.
OUTER JOIN en la práctica
Las sentencias OUTER JOIN suelen dar como resultado tablas que incluyen valores nulos. Esto es útil especialmente cuando se quiere conocer la existencia de dichos valores. Utilizando nuestros ejemplos, de la combinación de ambas tablas se puede saber que el Porsche Boxster todavía no se ha asignado a ningún empleado. Este dato, sin embargo, no se podría inferir solo con la tabla “vehículos”. Por su parte, aunque en la tabla “empleados” se observa que Oliva Cansino no cuenta con coche de empresa, es imposible saber si hay alguno disponible para asignarle. Después de utilizar FULL JOIN en las dos tablas se puede concluir de un solo vistazo que Andrea podría conducir el Porsche en el futuro.
