2.4. Joins

Las consultas multitabla o JOINS también denominadas combinaciones o composiciones, permiten recuperar datos de dos tablas o más según las relaciones lógicas entre ellas. Las combinaciones indican cómo debería utilizar el SGBD los datos de una tabla para seleccionar los datos de otra tabla.

Una condición de combinación (o composición) define la forma en la que dos tablas se relacionan en una consulta al:

Una combinación es una operación que combina registros de dos tablas en una base de datos relacional que resulta en una nueva tabla (temporal) llamada tabla de JOIN. En el lenguaje de consulta SQL hay dos tipos de JOIN: INNER y OUTER.

Como caso especial, una tabla (tabla base, vista o una tabla JOIN) puede realizar la operación JOIN sobre ella misma. Esto se conoce como SELF-JOIN.

Oracle

INNER JOIN

Es un tipo de composición de tablas, que permite emparejar filas de distintas tablas de forma más eficiente que con el producto cartesiano cuando una de las columnas de emparejamiento está indexada. Ya que en vez de hacer el producto cartesiano completo y luego seleccionar la filas que cumplen la condición de emparejamiento, para cada fila de una de las tablas busca directamente en la otra tabla las filas que cumplen la condición, con lo cual se emparejan sólo las filas que luego aparecen en el resultado.

Ejemplo: El siguiente DER de códigos postales de la República Mexicana. Observe que los datos son reales y contine más de 140 mil registros.

La consulta muestra todos municipios del estado de Campeche

SELECT M.municipio 
FROM municipios M INNER JOIN estados E
ON M.idEstado = E.idEstado 
WHERE E.estado = 'Campeche'
ORDER BY M.municipio;

Es posible hacer la unión (JOIN) de más de dos tablas. Por ejemplo, la consuLta que resgresa aquellas zonas urbanas - asentamientos - de tipo Industrial que pertenecen al municipio de Veracruz.

SELECT C.codigoPostal, C.colonia 
FROM tipoAsentamientos T INNER JOIN (colonia C INNER JOIN municipios M 
ON C.idMunicipio = M.idMunicipio AND M.municipio = 'Veracruz')
ON ( C.idTipo = T.idTipo ) AND ( T.tipo = 'Zona industrial' )
ORDER BY C.colonia;

Notese que una combinación interna (JOIN) encuentra registros de la primera tabla que se correspondan con los registros de la segunda, es decir, que cumplan la condición del 'ON' y si un valor de la primera tabla no se encuentra en la segunda tabla, el registro no aparece.

Si queremos saber qué registros de una tabla NO encuentran correspondencia en la otra, es decir, no existe valor coincidente en la segunda, necesitamos otro tipo de combinación, 'OUTER JOIN' (combinación externa).

Las combinaciones externas combinan registros de dos tablas que cumplen la condición, más los registros de la segunda tabla que no la cumplen; es decir, muestran todos los registros de las tablas relacionadas, aún cuando no haya valores coincidentes entre ellas.

Este tipo de combinación se emplea cuando se necesita una lista completa de los datos de una de las tablas y la información que cumple con la condición. Las combinaciones externas se realizan solamente entre 2 tablas.

Hay tres tipos de combinaciones externas: 'LEFT OUTER JOIN', 'RIGHT OUTER JOIN' y 'FULL OUTER JOIN'; se pueden abreviar como 'LEFT JOIN', 'RIGHT JOIN' y 'FULL JOIN' respectivamente.

LEFT JOIN

Produce el conjunto completo de registros de la tabla A, con los registros coincidentes (si están disponibles) en la tabla B. Si no hay coincidencia, el lado derecho contendrá NULL. La sintaxis es

.
SELECT columnas
FROM tablaA
LEFT [OUTER] JOIN tablaB
ON tablaA.columna = tablaB.columna
[ WHERE condicion ];

Su representación visual es la siguiente:

Ejemplo: Considere la relación ejecutivos - inmuebles en una inmobiliaria ficticia. Se presenta a continuación el código DDL.

CREATE TABLE ejecutivo (
 idEjecutivo     NUMBER(2) PRIMARY KEY,
 nEjecutivo      CHAR(15)
);

CREATE TABLE inmueble (
  referencia     NUMBER(5)PRIMARY KEY,
  fechaAlta      DATE NOT NULL,
  tipo           CHAR(15) NOT NULL,
  operacion      CHAR(20) NOT NULL,
  municipio      CHAR(85) NOT NULL,
  superficie     NUMBER(5) NOT NULL,
  valorOperacion NUMBER(10) NOT NULL,
  fechaOperacion DATE,
  idEjecutivo    NUMBER(2) CONSTRAINT fk_Ejecutivo
                           REFERENCES ejecutivo
                           ON DELETE SET NULL
);

Necesitamos obtener aquellos ejecutivos que no han realizado ninguna operación

SELECT nEjecutivo
  FROM ejecutivo e
  LEFT JOIN  inmueble i
  ON e.idEjecutivo = i.idEjecutivo
  WHERE i.idEjecutivo IS NULL;


RIGHT JOIN

SELECT T.tipo
 FROM colonia C
 RIGHT JOIN tipoAsentamientos T
 ON C.idTipo = T.idTipo  
 WHERE C.idMunicipio = 2275;

O mejor aún

SELECT DISTINCT T.tipo	
FROM tipoAsentamientos T 
RIGHT JOIN colonia C
   RIGHT JOIN municipios M ON C.idMunicipio = M.idMunicipio
   ON C.idTipo = T.idTipo
WHERE M.municipio = 'Veracruz'
ORDER BY T.tipo;

Full join

Produce el conjunto de todos los registros en las tablas Departamento y Empleado, con registros coincidentes en ambos lados cuando sea posible. Si no hay coincidencia, el lado que falta contendrá null.

SELECT * FROM departamento
FULL OUTER JOIN empleado e
ON d.idDepartamento = e.idDepartamento