Consultas combinadas en SQL y Ms Access (join queries)

En esta entrada explicaremos las consultas combinadas (join queries). Se denominan así porque combinan filas de dos o más tablas. También explicaremos cómo realizar estas consultas en Ms Access, que utiliza un dialecto de SQL conocido como Jet SQL que presenta algunas diferencias en la sintaxis y expresiones con la versión estándar de SQL.

Nota importante:

Como un abundante número de entradas en la red, explicamos las consultas combinadas mediante diagramas de Venn (usados en teoría de conjuntos). Estos diagramas sirven solamente como analogía y no representan con exactitud el resultado de las consultas combinadas cuando no hay una relación de uno a uno entre las tablas. Cuando la relación entre las tablas es de uno a varios, o de varios a varios, la representación es inadecuada. En estos casos tenemos un nuevo conjunto de filas que satisface las condiciones de la combinación, que no están en ninguna de las tablas, pero que contiene la combinación de columnas de ambas tablas.

Como ejemplo he usado dos tablas empleadas en la wikipedia para explicar estas consultas: tabla employee (empleado) y la tabla department (departamento).

Todos las columnas: * = employee.LastName, employee.DepartmentID, department.DepartmentName

Cross join

Es un producto cartesiano de las dos tablas. Es decir, todas las combinaciones posibles entre las filas de las dos tablas.

SELECT *
FROM department 
CROSS JOIN employee;

Access no permite el comando CROSS JOIN. Por tanto el código anterior generaría un error. Para evitar el error en Access usamos una cross join implícita:

SELECT *
FROM employee, department;

En general, su uso está fuertemente desaconsejado por su capacidad para generar un número enorme de filas. Mil filas en dos tablas generarían un millón de registros. Sin embargo, controladamente son útiles para obtener todas las combinaciones posibles o para crear bases de datos de prueba rápidamente.

Inner join

Compara cada fila de la tabla A (employee) con cada fila de la tabla B (department) para encontrar todos los pares de filas que satisfacen dichas condiciones especificadas.

ON o WHERE preceden la condiciones especificadas.

El diagrama de Venn no ilustra adecuadamente el resultado de la consulta y nos puede conducir a equívoco. El gráfico indica que nuestro conjunto incluirá la intersección de elementos de ambas tablas. Sin embargo, el resultado de la tabla inferior incluye un nuevo conjunto de filas que no pertenecen ni a la tabla A ni a la B. Este nuevo conjunto incluye las columnas de A y B con aquellas filas de ambas tablas que cumplen la condición especificada, que tengan un DepartmentID idéntico. Para cada empleado con el employee.DepartmentID igual al department.DepartmentID las columnas de ambas tablas. Los departamentos Engineering y Clerical aparecen dos veces pues hay dos empleados en la tabla A (employee) que pertenecen a dichos departamentos.

SELECT *
FROM employee 
INNER JOIN department
ON department.DepartmentID = employee.DepartmentID;

Inner join implícita

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;

Left outer join

Devuelve todas las filas de la tabla A (employee) incluso si no encuentra ninguna fila en la tabla B (department) que satisfaga la condiciones especificadas. Dicho de otra manera, incluye los resultados de la inner join más aquellas filas de la tabla A (employee) que no coinciden con las de la tabla B (department).

SELECT *
FROM employee 
LEFT JOIN department 
ON employee.DepartmentID = department.DepartmentID;

Left outer join excluyendo la inner join

SELECT *
FROM employee 
LEFT JOIN department 
ON employee.DepartmentID = department.DepartmentID
WHERE department.DepartmentID Is Null;

Right y left outer joins son funcionalmente equivalentes. Ambas proporcionan la misma funcionalidad, de manera que cualquiera de ellas puedes ser reemplazada con la otra con tal de que se invierta el orden de las tablas.

Consulta anterior planteada como una right join (su inversa)

SELECT *
FROM department 
RIGHT JOIN employee 
ON department.DepartmentID = employee.DepartmentID;

Right outer join

Es la consulta inversa de la left outer join. Tan sólo se invierte el orden de las tablas. Devuelve todas las filas de la tabla B (department) incluso si no encuentra ninguna fila en la tabla A (employee) que satisfaga la condiciones especificadas. Dicho de otra manera, incluye los resultados de la inner join más aquellas filas de la tabla B (department) que no coinciden con las de la tabla A (employee).

El diagrama de Venn no representa adecuadamente el resultado de la consulta y nos puede conducir a equívoco. El gráfico nos dice que nuestro conjunto incluirá solamente las filas de la tabla B. Sin embargo, el resultado de la tabla inferior no incluye solamente las 4 filas de la tabla B (department), sino aquellas filas de ambas tablas que cumplen la condición especificada. Muestra cada departamento de la tabla B tantas veces como empleados en la tabla A pertenezcan al mismo. Los departamentos Engineering y Clerical aparecen dos veces pues hay dos empleados en la tabla A (employee) que pertenecen a dichos departamentos.

SELECT *
FROM employee 
RIGHT JOIN department 
ON employee.DepartmentID = department.DepartmentID;

Consulta anterior planteada como una left join (su inversa)

SELECT *
FROM department 
LEFT JOIN employee 
ON employee.DepartmentID = department.DepartmentID;

Right outer join excluyendo la inner join

SELECT *
FROM employee 
RIGHT JOIN department 
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID Is Null;

Full outer join

Devolverá todas filas de ambas tablas A (employee) y B (department), satisfagan o no las condiciones especificadas. Para aquellos registros que cumplan las condiciones especificadas devolverá una sola fila con los valores correspondientes de cada tabla.

SELECT *
FROM employee 
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;

Ms Access no permite el uso del comando FULL OUTER JOIN. Para obtener el mismo resultado empleamos UNION:

Full outer join = left outer join + right inner join

SELECT *
FROM employee 
LEFT JOIN department 
ON employee.DepartmentID = department.DepartmentID

UNION 

SELECT *
FROM employee 
RIGHT JOIN department 
ON employee.DepartmentID = department.DepartmentID;

Otra alternativa es:

Full outer join = left outer join excluyendo la inner join + inner join + right inner join excluyendo la inner join

SELECT *
FROM employee 
LEFT JOIN department 
ON employee.DepartmentID = department.DepartmentID
WHERE department.DepartmentID Is Null

UNION 

SELECT *
FROM employee 
INNER JOIN department 
ON department.DepartmentID = employee.DepartmentID

UNION

SELECT *
FROM employee 
RIGHT JOIN department 
ON employee.DepartmentID = department.DepartmentID
WHERE (((employee.DepartmentID) Is Null));

Full outer join excluyendo la inner join

SELECT *
FROM employee 
FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID
WHERE employee.ID IS null
OR department.ID IS null;

Como Ms Access no permite el uso del comando FULL OUTER JOIN:

Full outer join = left outer join excluyendo la inner join + right inner join excluyendo la inner join

SELECT *
FROM employee 
LEFT JOIN department 
ON employee.DepartmentID = department.DepartmentID
WHERE department.DepartmentID Is Null

UNION 

SELECT *
FROM employee 
RIGHT JOIN department 
ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID Is Null;

Referencias

• SQL joins
• Join (SQL)