Inner, Outer, Natural & Cross JOINs

Publicado por Luciano el 31 de mayo de 2008 en Bases de Datos, SQL · 4 comentarios

La intención de esta publicación es hacer un breve resumen de los distintos tipos de JOINs disponibles en SQL y para finalizar, un ejemplo práctico de cada uno de ellos.

INNER JOIN: es el caso de unión interna clásico, combina dos o más tablas descartando todas las filas resultados que no se correspondan en ambas.
LEFT OUTER JOIN (o LEFT JOIN): combina dos tablas con la diferencia que selecciona todas las filas de la primer tabla sin importar tengan o no coincidencia en la segunda. RIGHT OUTER JOIN (o RIGHT OUTER): es la opción inversa a la anterior. En la mayoría de las bases de datos actuales se puede omitir OUTER.
FULL OUTER JOIN: combina los resultados de dos o más tablas, tengan o no coincidencia entre sí.
CROSS JOIN: retorna el producto cartesiano de dos o más tablas, es decir, combina cada fila de una tabla con cada fila de otra tabla. CROSS JOIN no debería llevar la clausula ON.
NATURAL JOIN: Es un caso especial de INNER JOIN que compara por cuenta propia la equivalencia de columnas con el mismo nombre y tipo de dato entre dos o más tablas para hacer la combinación de ambas. Hay que tener especial cuidado con su uso, pues puede producir resultados ambiguos o generar problemas si se añaden, quitan, o renombran las columnas.

A continuación veremos un ejemplo bien comentado, donde se ponen en práctica algunos de los distintos tipos de JOIN. El ejemplo fue escrito para MySQL, pero no deberían ser muchas las líneas a modificar para portar el código a otra base de datos.

Esquema de la base de datos y datos de prueba:




CREATE DATABASE administracion;

USE administracion;
CREATE TABLE empleados (

    id_empleado SMALLINT PRIMARY KEY,

    nombre VARCHAR(64),

    id_funcion INT,

    id_sector INT,

    id_localidad INT

);
CREATE TABLE funciones (

    id_funcion INT PRIMARY KEY,

    nombre VARCHAR(128)

);
CREATE TABLE sectores (

    id_sector INT PRIMARY KEY,

    nombre VARCHAR(128)

);

       

CREATE TABLE localidades (

    id_localidad INT PRIMARY KEY,

    nombre VARCHAR(128)

);
INSERT INTO empleados VALUES ( 1, 'Marcos Aguirre', 1, 1 , 3);

INSERT INTO empleados VALUES ( 2, 'Esteban Quito', 2, 4 , 2);

INSERT INTO empleados VALUES ( 3, 'Daniel Perez', 1, 2 , 1);

INSERT INTO empleados VALUES ( 4, 'Carlos Molina', 1, 1 , 3);

INSERT INTO empleados VALUES ( 5, 'Marcos Aguirre', 4, 1 , 1);
INSERT INTO sectores VALUES ( 1, 'Mantenimiento' );

INSERT INTO sectores VALUES ( 2, 'Ingenieria' );

INSERT INTO sectores VALUES ( 3, 'Costos' );
INSERT INTO funciones VALUES ( 1, 'Programador' );

INSERT INTO funciones VALUES ( 2, 'Analista funcional' );

INSERT INTO funciones VALUES ( 3, 'Administración' );
INSERT INTO localidades VALUES ( 1, 'Maldonado' );

INSERT INTO localidades VALUES ( 2, 'Salto' );

INSERT INTO localidades VALUES ( 3, 'Federación' );

Ejemplos JOIN:




-- INNER JOIN

SELECT   e.nombre, f.nombre, s.nombre, l.nombre

FROM       empleados e

        INNER JOIN funciones    f USING (id_funcion)

        INNER JOIN sectores     s USING (id_sector)

        INNER JOIN localidades  l USING (id_localidad);
-- nombre          nombre       nombre         nombre     

-- --------------  -----------  -------------  -----------

-- Marcos Aguirre  Programador  Mantenimiento  Federación

-- Daniel Perez    Programador  Ingenieria     Maldonado  

-- Carlos Molina   Programador  Mantenimiento  Federación

-- CROSS JOIN:

SELECT   empleados.id_empleado, funciones.id_funcion, sectores.id_sector, localidades.id_localidad

FROM       empleados 

        CROSS JOIN funciones

        CROSS JOIN sectores

        CROSS JOIN localidades
-- No voy a mostrar el resultado pues son bastantes filas (135 filas)

-- empleados CROSS JOIN funciones = 15 filas

-- empleados CROSS JOIN funciones CROSS JOIN sectores = 45 filas

-- empleados CROSS JOIN funciones CROSS JOIN sectores CROSS JOIN localidades= 135 filas

-- LEFT OUTER JOIN (o LEFT JOIN)

SELECT   e.nombre, f.nombre, s.nombre, l.nombre

FROM       empleados e

        LEFT JOIN funciones     f USING (id_funcion)

        LEFT JOIN sectores  s USING (id_sector)

        LEFT JOIN localidades   l USING (id_localidad);
-- nombre          nombre              nombre         nombre     

-- --------------  ------------------  -------------  -----------

-- Marcos Aguirre  Programador         Mantenimiento  Federación

-- Esteban Quito   Analista funcional  (NULL)         Salto      

-- Daniel Perez    Programador         Ingenieria     Maldonado  

-- Carlos Molina   Programador         Mantenimiento  Federación

-- Marcos Aguirre  (NULL)              Mantenimiento  Maldonado  

-- NATURAL JOIN

-- ------------

SELECT   e.nombre,l.nombre

FROM       empleados e

        NATURAL JOIN localidades l;

-- No tenemos resultados, pues la base de datos intenta hacer la combinación utilizando los campos id_localidad y nombre 

-- Les avisé que era peligroso el uso de NATURAL JOIN
-- Para evitar esto, deberiamos cambiar el nombre del campo 'nombre' de la tabla 'localidades'

ALTER TABLE localidades CHANGE nombre nombre_localidad VARCHAR(128);
SELECT   e.nombre,l.nombre_localidad

FROM       empleados e

        NATURAL JOIN localidades l;
-- nombre          nombre_localidad

-- --------------  ----------------

-- Marcos Aguirre  Federación     

-- Esteban Quito   Salto           

-- Daniel Perez    Maldonado       

-- Carlos Molina   Federación     

-- Marcos Aguirre  Maldonado