4.- Obteniendo datos de múltiples tablas Objetivos del capítulo

• Escribir sentencias SQL para obtener datos de más de una tabla utilizando asociaciones de igualdad y desigualdad.

• Ver datos que generalmente no se encuentran en una condición de asociación con el uso de asociaciones externas (outer join).

• Asociación de una tabla a si misma con el uso del self join.

Datos de múltiples tablas En algunas ocasiones se requieren datos de más de una tabla. En la imagen anterior el reporte muestra datos de dos tablas separadas.

• El identificador del empleado existe en la tabla EMPLOYEES • El identificador del departamento existe las tablas de EMPLOYEES y

DEPARTMENTS • El identificador de localización existe en la tabla DEPARTMENTS

Para producir el reporte, se necesita asociar las tablas EMPLOYEES y DEPARTMENTS y acceder a los datos de ambas.

Producto cartesiano Cuando una condición join (asociación) es inválida u omitida completamente, el resultado es un producto cartesiano, en el que todas las combinaciones de filas son mostrada. Todas las filas de la primera tabla son asociadas con las filas de la segunda tabla. Un producto cartesiano tiende a generar un largo número de filas, y el resultado es raramente utilizado. Siempre se debe incluir una condición join válida en una

cláusula WHERE, a menos que se tenga una necesidad específica para combinar todas las filas de todas las tablas. El producto cartesiano es útil para algunas pruebas cuando se necesita generar un largo número de filas simulando un razonable número de datos.

Un Producto cartesiano es generado si una condición JOIN (asociación) es omitida. En la imagen anterior se muestra el apellido y nombre del departamento de las tablas EMPLOYEES y DEPARTMENTS. Puesto que no se ha especificado una cláusula WHERE, todas las filas (20) de la tabla EMPLOYEES son asociadas con todas las filas (8) de la tabla DEPARTMENTS, en consecuencia 160 filas son mostradas.

Tipos de JOINS La base de datos Oracle9i ofrece la sintaxis JOIN de SQL:1999. Antes de la puesta en marcha de los productos 9i, la sintaxis del JOIN era diferente al estándar de ANSI. En el nuevo SQL: 1999 la sintaxis JOIN no ofrece ningún funcionamiento benéfico con respecto a la sintaxis JOIN propiedad de Oracle.

Definiendo JOINS

Cuando los datos de más de una tabla de una base de datos son requeridos, una condición join es utilizada. Las filas en una tabla pueden ser asociadas con las filas de otra tabla de acuerdo a los valores comunes existentes en sus correspondientes columnas, esto es usualmente una llave primaria (primary key) y una llave foránea (foreing key). Para desplegar datos de dos o más tablas relacionadas. Se escribe una simple condición join en la cláusula WHERE. En la sintaxis:

table1.column denota la tabla y columna donde el dato será recuperado

table1.column1 = table2.column2 es la condición join (o relación) del conjunto de

tablas

Normas a seguir • Cuando escribes una sentencia SELECT que asocia tablas, se precede

al nombre de la columna con el nombre de la tabla para una mejor claridad y resaltar el acceso a la base de datos.

• Si el mismo nombre de columna aparece en más de una tabla, el nombre de la columna debe ser antecedido con el nombre de la tabla.

• La asociación con n tablas en su conjunto, requiere un mínimo de n-1 condiciones de asociación. Por ejemplo, un join o asociación con 4 tablas, requiere de al menos 3 asociaciones. Esta regla puede no aplicarse si la tabla tiene una llave primaria concatenada, en estos casos más de una columna es requerida para identificar de forma única a cada columna.

Equijoins Para determinar el nombre del departamento de un empleado, se compara el valor de la columna DEPARTMENT_ID de la tabla EMPLOYEES con el valor de la columna DEPARTMENT_ID de la tabla DEPARTMENTS. La relación entre las tablas EMPLOYEES y DEPARTMENTS es un equijoin- esto es, los valores en la columna DEPARTMENT_ID en ambas tablas deben ser iguales. Frecuentemente este tipo de asociaciones involucra una llave primaria y una llave foránea. Nota: Los equijoins también son llamados simple joins o inner joins.

Recuperando registros con equijoins En el ejemplo anterior:

• La cláusula SELECT especifica el nombre de las columnas a recuperar o employees.employee_id, employees.last_name y

employees.department_id son columnas de la tabla EMPLOYEES o departments.department_id y departments.location_id son

columnas de la tabla DEPARTMENTS • La cláusula FROM especifica las tablas de la base de datos que se

deben acceder: o Tabla EMPLOYEES o Tabla DEPARTMENTS

• La cláusula WHERE como las tablas se relacionaran o asociaran: o EMPLOYEES.DEPARTMENT_ID =

DEPARTMENTS.DEPARTMENT_ID Puesto que la columna DEPARTMENT_ID es común en ambas tablas, esta debe ser antecedida del nombre de la tabla para evitar ambigüedades.

Condiciones adicionales de búsqueda De forma adicional al join, se puede tener un criterio en la cláusula WHERE para restringir las filas bajo las consideraciones de una o más tablas. Por ejemplo, para desplegar el número de departamento y nombre de departamento del empleado Matos, se necesita de una condición adicional en la cláusula WHERE.

Cualificando columnas ambiguas Se necesitan cualificar el nombre de las columnas en la cláusula WHERE con el nombre de la tabla para evitar ambigüedad. Sin el prefijo de la tabla, la columna DEPARTMENT_ID podría ser de la tabla DEPARTMENTS o EMPLOYEES. Es necesario agregar el prefijo de la tabla para ejecutar su consulta. Si no existen columnas con nombres comunes entre las tablas, no es necesario cualificar las columnas. Sin embargo, el uso del prefijo mejora el desempeño, puesto que se le indica al servidor Oracle exactamente la columna a buscar. El requerimiento para cualificar nombres de columnas ambiguas es también aplicable para columnas que pueden ser ambiguas en otras cláusulas, como la cláusula SELECT o la cláusula ORDER BY.

Alias de tablas Cualificar el nombre de columnas con el nombre de la tabla puede ser muy tardado, particularmente si los nombres de las tablas son largos. Se puede hacer uso de alias para tablas en lugar del nombre de la tabla. De la misma forma que se da un alias a una columna con otro nombre, un alias de tabla le otorga otro nombre a la misma. Los alias de tablas ayudan a mantener el código SQL más pequeño y usar menos memoria. Note como en el ejemplo, el alias de una tabla es identificado en la cláusula FROM. El nombre de la tabla es especificado en su totalidad, seguido por un espacio y el nombre del alias. La tabla EMPLOYEES tiene el alias e y la tabla DEPARTMENTS el alias d.

Normas • Los alias de tablas pueden tener más de 30 caracteres, pero es mejor

que sean cortos • Si un alias de tabla es usado para una tabla en particular en la cláusula

FROM, entonces este alias debe ser substituido en por toda la sentencia SELECT

• El alias debe ser significativo • El alias es válido solamente para la sentencia SELECT actual

Condiciones adicionales de búsqueda En algunas ocasiones se requiere más de dos tablas. Por ejemplo, mostrar el apellido, nombre de departamento y ciudad para cada empleado, se necesita asociar las tablas EMPLOYEES, DEPARTMENTS y LOCATIONS.

Non-Equijoins Un non-equijoin es un join conteniendo alguna otra condición diferente a un operador de igualdad. La relación entre las tablas EMPLOYEES y JOB_GRADES es un ejemplo de un non-equijoin. Una relación entre estas dos tablas es que la columna SALARY de la tabla EMPLOYEES puede encontrarse entre las columnas LOWEST_SALARY y HIGHEST_SALARY de la tabla JOB_GRADES. La relación es obtenida usando otro operador diferente al (=).

En el ejemplo anterior se crea un no-equijoin para evaluar la clasificación de salario de los empleados. El salario debe estar entre algún par de rangos altos y bajos. Es importante notar que todos los empleados aparecen exactamente desde que la consulta es ejecutada. Los empleados no se repiten en la lista. Existen dos razones para esto:

• Note que las filas de la tabla JOB_GRADE contienen clasificaciones que se superponen. Esto es, el salario para un empleado puede estar

solamente entre los valores altos y bajos de una fila en la tabla SALARY_GRADE.

• Todos los salarios de los empleados se encuentran en los límites provistos por la tabla JOB_GRADE. Esto es, no se tienen empleados que ganen menos que el valor contenido en la columna LOWEST_SAL o más que el valor contenido en la columna HIGHEST_SAL.

Nota: Otras condiciones como <= y >= pueden ser utilizadas, pero es mas sencillo el BETWEEN. Recuerde especificar primeramente el rango menor que el mayor en la cláusula BETWEEN. Los alias de tablas han sido utilizados en el ejemplo por razones de desempeño, no por un posible caso de ambigüedad.

Outer Joins

Obteniendo registros con comparaciones no directas por medio de Outer Joins Si una fila no cumple una condición Join, esta fila no aparecerá en el resultado de la consulta. Por ejemplo en la condición equijoin de las tablas EMPLOYEES y DEPARTMENTS, El empleado Grant no aparece porque no existe un valor de departamento para él en la tabla EMPLOYEES. Por lo que en lugar de ver 20 empleados en el resultado, sólo se muestran 19.

Usando Outer Joins para obtener registros sin una comparación directa La ausencia de filas puede ser obtenida si un operador outer join es utilizado en la condición join. El operador es una símbolo positivo encerrado entre paréntesis (+), y es colocado a un lado del join que es deficiente en información. Este operador tiene el efecto de crear una o más filas nulas, para el cual una o más filas de una tabla no deficiente pueda ser asociada. En la sintaxis:

table1.column = Es la condición que asocia (o relaciona) las tablas.

table2.column (+) Es el símbolo outer join, que puede ser colocado a un lado de la condición de la cláusula WHERE, pero no en ambos lados. (Coloque el símbolo outer join seguido del nombre de la columna de la tabla con deficiencia de información)

En el ejemplo anterior se muestran los apellidos, departamentos y nombres. El departamento de contratación no tiene ningún empleado. El valor mostrado es vació.

Restricciones del Outer Join • El operador outer join puede aparecer en solo un lado de la expresión –

en el lado que tenga ausencia de información. Esto regresa aquellas filas de una tabla que no tenga una correspondencia con la otra tabla.

• Una condición que involucra un outer join no puede utilizar el operador IN o ser vinculado con otra condición por el operador OR.

Self Joins

Asociando una tabla a si mismo En algunas ocasiones es necesario asociar una tabla a si mismo. Para encontrar el nombre del jefe de cada empleado, se requiere asociar la tabla EMPLOYEES con si misma o ejecutar un self join. Por ejemplo para encontrar el nombre del jefe de Whalen, se necesita:

• Encontrar a Whalen en la tabla EMPLOYEES viendo la columna LAST_NAME.

• Encontrar el número del jefe de Whalen viendo la columna MANAGER_ID. El número del jefe de Whalen es 101.

• Encontrar el nombre del jefe con EMPLOYEE_ID 101 viendo la columna LAST_NAME. El empleado Kochhar es el número 101. entonces Kochhar es jefe de Whalen.

En este proceso, necesitamos ver la tabla dos veces. La primera ocasión para encontrar las columnas LAST_NAME y MANAGER_ID de Whalen. La segunda

vez para buscar el valor 101 en la columna EMPLOYEE_ID y encontrar a Kochhar en la columna LAST_NAME.

En el ejemplo anterior el join de la tabla EMPLOYEES se realiza a sí mismo. Para simular dos tablas en la cláusula FROM, hay dos alias llamados w y m, de la misma tabla EMPLOYEES. En este ejemplo, la cláusula WHERE contiene el join de esta manera “donde el número del jefe del empleado es igual al número del empleado asignado al jefe”.

Joins a tablas utilizando la sintaxis SQL 1999

Definiendo joins Utilizando la sintaxis SQL 1999, se puede obtener los mismos resultados que hemos visto en los temas anteriores. En la sintaxis: table1.column Denota la tabla y columna donde el dato es

recuperado CROSS JOIN Regresa un producto cartesiano de dos

tablas NATURAL JOIN Joins de dos tablas basado en el mismo

nombre de columna JOIN table USING column_name

Ejecuta un equijoin basado en el nombre de la columna

JOIN table ON table1.column_name = table2.column_name LEFT/RIGHT/FULL OUTER

Ejecuta un equijoin basado en la condición de la cláusula ON

Creando Cross Joins

El ejemplo anterior obtiene los mismos resultados que el siguiente:

Creando Natural Joins No era posible hacer un join sin especificar explícitamente las columnas de las tablas en versiones previas de Oracle. En Oracle9i esto es posible, permitiendo que el join sea automáticamente realizado basándose en las columnas de las dos tablas que tienen el mismo nombre y tipo de dato, usando la palabra reservada NATURAL JOIN. Nota: El join puede ocurrir en columnas que tengan el mismo nombre y tipo de dato en ambas tablas. Si las columnas tienen el mismo nombre, pero diferente tipo de datos, entonces la sintaxis NATURAL JOIN devuelve un error.

Recuperando registros con Natural Joins

En el ejemplo anterior, la tabla LOCATIONS es asociada con la tabla DEPARTMENT por la columna LOCATION_ID, que es la única columna con el mismo nombre en ambas tablas. Si otra columna común estuviese presente, el join puede usarla.

Equijoins El natural join puede tambien ser escrito como un equijoin: SELECT department_id, department_name, departments.location_id, city FROM departments, locations WHERE departments.location_id = locations.location_id; Natural Joins con una cláusula WHERE Las restricciones adicionales en un natural join son implementadas por el uso de la cláusula WHERE. En el ejemplo siguiente se limitan las filas resultantes con un número de departamento igual a 20 o 50. SELECT department_id, department_name, location_id, city FROM departments

NATURAL JOIN locations WHERE department_id IN (20,50);

La cláusula USING El natural joins utiliza todas las columnas cuyos nombres y tipos de datos sean iguales para asociar las tablas. Las columnas referenciadas en la cláusula USING no tienen que ser cualificadas (con el nombre de la tabla o alias) en cualquier lugar de la sentencia SQL. Por ejemplo, esta sentencia es válida: SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE location_id = 1400; Esta sentencia es inválida porque LOCATION_ID es cualificado en la cláusula WHERE: SELECT l.city, d.department_name FROM locations l JOIN departments d USING (location_id) WHERE d.location_id = 1400; ORA-25154: column part of USING clause cannot have qualifier

La misma restricción aplica para el NATURAL JOINS. Por consiguiente las columnas deben tener el mismo nombre en ambas tablas teniendo que ser usadas sin ningún calificador.

En el ejemplo se muestra una asociación de la columna DEPARTMENT_ID de las tablas EMPLOYEES y DEPARTMENTS, para así ver la localización donde el empleado trabaja. También puede ser escrito como un equijoin:

La condición ON Utilice la cláusula ON para especificar una condición join. Esto permite especificar una condición join por separado de alguna condición de búsqueda o filtrado en la cláusula WHERE.

Creando joins con la cláusula ON La cláusula ON puede ser utilizada para asociar columnas que tienen diferentes nombres:

El ejemplo anterior es un selfjoin de la tabla EMPLOYEE con si misma, basándose en las columnas EMPLOYEE_ID y MANAGER_ID.

Three-Way Joins

Un Three-Way Join es un join de tres tablas. En la sintaxis SQL: 1999, los joins son ejecutados de izquierda a derecha así que el primer join que es ejecutado es EMPLOYEES JOIN DEPARTMENTS. La primera condición join puede referenciar columnas en EMPLOYEES y DEPARTMENTS pero no puede referenciar columnas en LOCATIONS. La segunda condición join puede referenciar columnas de las tres tablas. También puede ser escrito como un Three-way equijoin:

INNER Vs OUTER Joins • En SQL: 1999, el join de dos tablas regresa solamente las filas iguales

esto es un INNER Join. • Un join entre dos tablas que obtiene el resultado de un inner join como

también las filas de tablas izquierdas o derechas no comparables es un left o right outer join.

• Un join entre dos tablas que obtiene el resultado de un inner join, como también el resultado de un left y right join es llamado un full outer join.

Comparando joins SQL:1999 con Oracle

Ejemplo de un LEFT OUTER JOIN

Esta consulta recupera todas las filas de la tabla EMPLOYEES, que es la tabla izquierda aún sin no existen comparaciones con la tabla DEPARTMENTS. Esta consulta también puede escribirse de la siguiente forma:

Ejemplo de un RIGHT OUTER JOIN

Esta consulta recupera todas las filas de la tabla DEPARTMENTS, que es la tabla derecha aún si no existen comparaciones con la tabla EMPLOYEES. Esta consulta también puede ser escrita de la siguiente manera:

Ejemplo de un FULL OUTER JOIN

Esta consulta todas las columnas de la tabla EMPLOYEES, aún si no existen comparaciones con la tabla DEPARTMENTS. También recupera todas las filas de la tabla DEPARTMENTS, aunque no se tengan comparaciones con la tabla EMPLOYEES.

Esta consulta no es posible realizarse utilizando simplemente condiciones outer joins, debido a la complejidad del resultado, sólo se puede obtener por medio del operador UNION, como se muestra a continuación:

Aplicando condiciones adicionales

Se pueden aplicar condiciones adicionales en la cláusula WHERE. En el ejemplo se ejecuta un join de las tablas EMPLOYEES y DEPARTMENTS, y se agrega una condición para que solo sean mostrados los empleados cuyo jefe sea igual a 149.

Resumen Existen múltiples caminos para asociar tablas.

Tipos de joins • Equijoins • Non-equijoins • Outer joins • Self joins • Cross joins • Natural joins • Full u Outer joins

Práctica 4 Esta práctica pretende aportar experiencia en la extracción de datos de más de una tabla.

1. Escriba una consulta que despliegue el apellido, numero de departamento y nombre de departamento para todos los empleados.

2. Genere un único listado de todos los puestos que hay en el departamento 80. incluya la localización del departamento en la consulta.

3. Escriba una consulta que muestre el apellido del empleado, nombre del departamento, identificador de la localización y ciudad de todos los empleados que tengan una comisión.

4. Despliegue el apellido y nombre del departamento para todos los empleados que tengan una a (minúscula) en su apellido. Guarde la sentencia SQL en un archivo con el nombre lab4_4.sql

5. Escribe una consulta que despliegue el apellido, puesto y numero de departamento para todos los empleados que trabajan en Toronto

6. Despliegue el apellido y número de empleado junto con el apellido y numero de empleado de su jefe. Las columnas deberán llamarse Employee, Emp#, Manager y Mgr# respectivamente. Guarde su sentencia en un archivo llamado lab4_6.sql

7. Modifica el lab4_6.sql para mostrar a todos los empleados incluyendo a King, el cuál no tiene jefe. Ordena los resultados por el número de empleado y guarde la sentencia en el archivo lab4_7.sql

Si tiene tiempo, complete los siguientes ejercicios:

8. Cree una consulta que despliegue el apellido del empleado, número de departamento y todos los empleados quienes trabajen en el mismo departamento con cada determinado empleado. Agregue a cada columna un etiqueta apropiada

9. Muestre la estructura de la tabla JOB_GRADES. Elabore una consulta que despliegue el nombre, puesto, nombre de departamento, salario y categoría de todos los empleados

Si tienes tiempo extra, realiza los siguientes ejercicios

10. Elabora una consulta que despliegue el nombre y fecha de contratación de los empleados contratados después del empleado Davies

11. Despliegue los nombres y fecha de contratación de todos los empleados que fueron contratados antes que sus jefes, junto con el nombre y fecha de contratación de sus jefes. Etiquete las columnas como Employee, Emp, Hired, Manager y Mgr Hired respectivamente