Voy a resumir rápidamente las diferentes formas de realizar una unión —JOIN, OUTER JOIN— de tablas en una sentencia SELECT. Aunque lo especificado en esta entrada se realiza utilizando Oracle, el lenguaje SQL es un estándar y por ello puede ser utilizado (suele haber pequeñas diferencias, por lo que hay que probar) en diferentes sistemas de gestión de bases de datos. Añado que, a lo largo de la entrada me refiero a «tabla» pero según el contexto puede ser en su lugar: vistas, subconsultas; y cuando me refiero a «campo» es el nombre de la columna; «fila» es una tupla; esto por familiarizar con diferentes formas de expresar para quienes se inician.
Utilizaré dos tipos de tablas para poner los ejemplos, es decir, unas inventadas por mí para ilustrar rápidamente y, las del esquema de ejemplo HR de Oracle que viene instalado o se puede crear desde Oracle, o buscando en internet los scripts.
Las tablas inventadas son las siguientes:
CROSS JOIN
Es el producto cartesiano, es decir, la relación de todas las filas de la tabla Emp con todas las filas de la tabla Dep y a su vez con todas las filas de la tabla Loc. Esto hay que conocerlo porque nos puede dar una pista de cuando nos salen más filas de las esperadas ya que no hemos especificado la condición de unión de las tablas según su relación.
En este pequeño ejemplo en el que en Emp hay 4 filas, Dep 3 filas y Loc 2 filas, obtenemos como resultado 24 filas = 4x3x2.
La sentencia escrita en dos formas:
SELECT emp.nombre, emp.depid, dep.depid, dep.nombre, loc.nombre
FROM emp, dep, loc;
SELECT emp.nombre, emp.depid, dep.depid, dep.nombre, loc.nombre
FROM emp CROSS JOIN dep CROSS JOIN loc;
Si realizamos similar prueba con las tablas del esquema HR la sentencia sería:
SELECT first_name, department_name, city
FROM employees, departments, locations;
Dando como resultado 66447 filas = 107(Employees) x 27(Departments) x 23(Locations)
NATURAL JOIN
Lo que realiza es una unión por los campos de igual nombre, así que, hay que tener cuidado con esto pues hay quien lo utiliza para escribir menos y confía en que se realiza la unión por los campos clave. Pero, además puede estar realizando la unión por otros campos en los que coincide el nombre de las columnas.
Empecemos con un ejemplo de las tablas de HR en el que deseamos el nombre de los empleados y el de su departamento.
SELECT first_name, department_name
FROM employees NATURAL JOIN departments;
Puede que pensemos que lo que se está haciendo es la unión por el campo con el que se relacionan las tablas, que es el número de departamento, la sentencia siguiente:
SELECT first_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
Pero, en realidad no lo es y nos da como resultado 32 filas, ya que en realidad la sentencia que está realizando sería:
SELECT first_name, department_name, department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id
AND employees.manager_id = departments.manager_id;
En realidad ésta sentencia nos da los empleados que tienen el mismo jefe, tanto como jefe de empleado como jefe de departamento. Debido a que en las tablas Employees y Departments existen dos campos (nombre de columna) en los que coincide el nombre: manager_id y department_id. Si intentamos especificar en la Select uno de estos campos dará error si le anteponemos un nombre de tabla pues esta sentencia es específicamente para tratarlos como igual, así que no hay por qué diferenciar.
En el caso de las tablas que he inventado no obtendremos ninguna fila pues en las tres hay un campo que se llama: nombre. Y claro está que ningún valor coincide.
JOIN o simple join o inner join
Es la unión típica en la que se especifica el campo o campos que relacionan las tablas involucradas. Esto es, un campo primary key con uno foreign key. Los cuales surgen de una correcta modelización y paso al modelo relacional.
En el ejemplo se puede ver que salen únicamente las tuplas (filas) relacionadas. Es decir, Ana como no tiene un departamento no se muestra en el listado. Supongamos que aunque María sí esté en el departamento 20, el departamento 20 no tenga asignada una localidad, entonces tendremos como resultado solo dos filas en donde María no se listará.
Otra forma de escribir la misma sentencia es con la palabra JOIN y la cláusula USING:
SELECT emp.nombre, depid, dep.nombre, loc.nombre
FROM emp JOIN dep USING(depid)
JOIN loc USING(locid);
Es una forma en la que debemos tener en cuenta que: los nombres de los campos utilizados en la cláusula USING deben ser iguales en ambas tablas, y no puede anteponerse el nombre de ninguna tabla como podemos ver al utilizarlo en la parte de SELECT con el campo: depid, de igual modo si lo utilizamos en otra parte de la sentencia. Como podemos ver la especificación se hace escribiendo todo en el FROM; en el WHERE irán todas las condiciones restantes que no tienen que ver con la relación de las tablas.
Lo que estamos diciendo es que Emp se une con Dep usando el campo depid y, a esa unión la unimos con la tabla Loc usando el campo locid. Repito: los nombres tienen que ser iguales pues de caso contrario no se puede realizar esta sintaxis. También, hay que tener en cuenta que si realizamos la unión por más de un campo se especifican separados por comas dentro del USING. Por ejemplo en el caso visto antes de los empleados que su jefe es el mismo que el jefe del departamento en el que trabajan se podría escribir de la siguiente forma.
SELECT first_name, department_name
FROM employees JOIN departments USING(department_id, manager_id);
Y obtendríamos las 32 filas mencionadas. Pero, debemos tomar en cuenta que aunque obtenemos los mismos resultados, por buenas prácticas debemos separar las condiciones de relación de las tablas (campos clave con campos ajenos) del resto de condiciones, en sentencias más grandes facilita el mantenimiento y la comprensión. Es decir, la sentencia sería:
SELECT first_name, department_name
FROM employees JOIN departments USING(department_id)
WHERE employees.manager_id = departments.manager_id;
Continuando con las tablas de HR la sentencia en la que se listan los empleados con su nombre, el nombre del departamento y la ciudad, tenemos:
SELECT first_name, department_name, city
FROM employees JOIN departments USING(department_id)
JOIN locations USING(location_id);
El total de empleados es 107, pero se listan 106 pues hay uno que no tiene departamento. Y hay que recordad que solo se listan las filas relacionadas.
Además, del JOIN y la cláusula USING, tenemos a JOIN con la cláusula ON en la que escribimos la condición de unión similar a como se haría en el WHERE. Ésta forma nos permite trabajar con campos en los que no coincidan el nombre en ambas tablas, porque igual se debe especificar y en caso de hacer referencia a uno de ellos sí hay que anteponer el nombre de la tabla; además, tiene la misma ventaja de separar las condiciones de unión del resto de condiciones del WHERE haciendo más legible la sentencia, por lo que es la opción que yo recomendaría. A continuación las sentencias que listan a los empleados de ambos ejemplos que utilizo.
SELECT emp.nombre, emp.depid, dep.nombre, loc.nombre
FROM emp JOIN dep ON(emp.depid = dep.depid)
JOIN loc ON(dep.locid = loc.locid);
SELECT first_name, department_name, city
FROM employees JOIN departments ON(employees.department_id = departments.department_id)
JOIN locations ON(departments.location_id = locations.location_id);
OUTER JOIN
Son útiles para mostrar filas que no están relacionadas, además de las relacionadas. En el ejemplo sencillo en el que Ana no se muestra, supongamos que queremos listar todos los empleados independientemente de si están o no relacionados.
Con LEFT JOIN (LEFT OUTER JOIN) lo que indicamos es que la tabla a la izquierda es de la que quiero todas las filas independientemente de que sus filas se relacionen. El segundo LEFT JOIN es necesario pues a la primera unión vamos a relacionarla con la tercera tabla (Loc) y si es a la izquierda donde se encuentra la información que queremos listar se relacione o no. Así que, el que sea LEFT JOIN es porque tal y como se ha escrito está a la izquierda, pues si lo ponemos en otro orden y queremos los mismos resultados hay que tener en cuenta hacia qué lado está lo que queremos listar aunque no esté relacionado. Supongamos que queremos lo mismo pero escribimos las tablas en el orden inverso. Entonces en éste caso la tabla de empleados estará a la derecha por lo que la sentencia sería con RIGHT JOIN (RIGHT OUTER JOIN):
Ahora queremos todos los departamentos, que tal como podemos comprobar con los datos existe el 30 que no se relaciona. Y vamos a escribir la sentencia dejando a la tabla dep en el centro simplemente para ilustrar que hay que escribir los JOIN en función del orden en el que escribimos.
Como se puede ver se lista el departamento 30 que no tiene localidad y Ana no se muestra. Antes de que exista el estándar de 1999, que es el que utiliza la palabra JOIN, utilizando la forma antigua con WHERE se escribiría de la siguiente forma:
En éste caso se coloca un signo más entre paréntesis al campo de la tabla que queremos rellene o no tome en cuenta. Como queríamos todas las filas de Dep colocamos el símbolo a Emp y Loc. Para el caso de las tablas de HR, manteniendo el supuesto de que queremos todos los departamentos además de los datos relacionados, que son 106 filas, la sentencia en la que obtendríamos 122 filas sería:
SELECT first_name, department_name, cityFROM employees RIGHT JOIN departments ON(employees.department_id = departments.department_id)LEFT JOIN locations ON(departments.location_id = locations.location_id);
SELECT first_name, department_nameFROM employees, departments, locationsWHERE employees.department_id(+) = departments.department_id AND departments.location_id = locations.location_id(+);
Por último, ahora suponemos que queremos listar todos los empleados, departamentos y localidades estén o no relacionados. Entonces se nos puede ocurrir poner el símbolo (+) a ambos lados del igual. Pero, esto no se permite. Con esta sintaxis anterior al estándar del 99, se tenía que idear otra forma. Así que, sumamos otra ventaja al estándar del 99 que lo permite con FULL JOIN (FULL OUTER JOIN).
SELECT first_name, department_name, cityFROM employees FULL JOIN departments ON(employees.department_id = departments.department_id)FULL JOIN locations ON(departments.location_id = locations.location_id);
En el caso de las tablas de HR obtenemos 139 filas. Y en nuestro pequeño ejemplo:
Como se ve en la imagen están las 3 filas que se relacionan, la empleada que no tiene departamento, el departamento que no tiene localidad y la localidad que no se relaciona, teniendo así las 6 filas que se listan. A partir de aquí, podemos mezclar por ejemplo al suponer que queremos todos los empleados y departamentos pero las ciudades nos basta con las que se relacionan, entonces tendríamos:
SELECT emp.nombre, emp.depid, dep.nombre, loc.nombre
FROM emp FULL JOIN dep ON(emp.depid = dep.depid)
LEFT JOIN loc ON(dep.locid = loc.locid);
Realizado con:—Oracle Database 10g
—Aplicación desde terminal SQLPLUS
