Visualización de Datos de Varias Tablas

Objetivos: 

•Escribir sentencias SELECT para acceder a datos de más de una tabla mediante las uniones igualitarias y no igualitarias.

•Unir una tabla a sí misma mediante una auto-unión.

•Ver datos que generalmente no cumplen una condición de unión utilizando uniones externas.

•Generar un producto cartesiano de todas las filas de dos o más tablas.

NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

_____________________________________________________________________________________

Obtención de Datos de Mas de una Tabla.

En ocasiones, tienes que utilizar datos de más de una tabla. Una unión se utiliza para ver información de varias tablas. De esta forma, puede unir diferentes tablas y así ver información en ellas.

Tipos de Uniones.
Las uniones compatibles con el estándar SQL:1999 son:
•Uniones cruzadas.
•Uniones naturales.
•Cláusula USING.
•Uniones externas completas (o de dos lados).
•Condiciones de unión arbitrarias para uniones externas.

Para unir tablas, puede utilizar la sintaxis de unión compatible con el estándar SQL:1999. 

Nota: antes de la versión Oracle9i, la sintaxis de unión era diferente a los estándares ANSI. La sintaxis de unión compatible con SQL:1999 no ofrece ventajas de rendimiento con respecto a la sintaxis de unión propietaria de Oracle que existía en las versiones anteriores.

Sintaxis
SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON (table1.column_name = table2.column_name)]|
[LEFT | RIGHT | FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)]|
[CROSS JOIN table2];

Descripción:

•Es bueno recordar que  '|'  significa 'o', de modo que la sintaxis anterior te dice que puedes usar: [NATURAL JOIN table2]  o [JOIN table2 USING (column_name)] o [JOIN table2 ON  (table1.column_name = table2.column_name)], etc...
•SELECT table1.column, especifica la tabla y la columna de las que se recuperan los datos.
•NATURAL JOIN, une dos tablas basándose en el mismo nombre de columna.
•JOIN table USING column_name, realiza una unión igualitaria basándose en el nombre de columna.
•JOIN table ON, realiza una unión igualitaria basándose en la condición de la cláusula ON, (table1.column_name = table2.column_name)
•LEFT/RIGHT/FULL OUTER, se utiliza para realizar uniones externas.

•CROSS JOIN, devuelve un producto cartesiano de las dos tablas.
_____________________________________________________________________________________
Uniones Naturales - Natural Joins.

Puede unir tablas automáticamente basándose en columnas de las dos tablas que tengan tipos de datos y nombres correspondientes. Hágalo mediante las palabras clave NATURAL JOIN. 

Nota: la unión sólo se puede realizar en las columnas que tengan los nombres y los tipos de datos iguales en ambas tablas. Si las columnas tienen el mismo nombre pero diferentes tipos de datos, la sintaxis de NATURAL JOIN producirá un error.

•La cláusula NATURAL JOIN se basa en todas las columnas de las dos tablas que tienen el mismo nombre.

•Selecciona filas de las dos tablas que tienen valores iguales en todas las columnas correspondientes.
•Si las columnas que tienen los mismos nombres tienen tipos de datos diferentes, se devuelve un error.

Ejemplos:
SELECT department_id, department_name, location_id, city
FROM departments
NATURAL JOIN locations;

/*El anterior ejemplo muestra los departamentos y sus respectivas locaciones (usando NATURAL JOIN); los campos department_id y department_name pertenecen solo a departments, el campo city esta en locations y location_id es de ambas tablas. */

---

SELECT
     first_name||' '||
    last_name AS "Empleado",
    department_name AS "Departamento"

FROM departments 

NATURAL JOIN employees

WHERE department_id in (30,100);

/*El anterior ejemplo muestra los Empleados y sus respectivos departamentos( usando NATURAL JOIN) de todos los empleados de los departamentos 30 y 100; los campos first_name y last_name pertenecen a employees, el campo department_name esta en departments y department_id es de ambas tablas. */

_____________________________________________________________________________________

Uniones con USING.

Se puede utilizar la cláusula USING para especificar únicamente las columnas que se deben utilizar para una unión igualitaria. Las columnas a las que se hace referencia en la cláusula USING no deben tener cualificador (nombre de tabla o alias) en ninguna parte de la sentencia SQL.

•Si hay varias columnas que tienen los mismos nombres pero los tipos de datos no se corresponden, la cláusula NATURAL JOIN se puede modificar mediante la cláusula USING para especificar las columnas que se deben utilizar para una unión igualitaria.

•Utilice la cláusula USING para asignar sólo una columna cuando corresponde a más de una columna.

•No utilice un alias o un nombre de tabla en las columnas a las que se hace referencia.

•Las cláusulas NATURAL JOIN y USING se excluyen mutuamente.

Ejemplos:

SELECT department_id, department_name, location_id, city 

FROM departments JOIN locations

USING(location_id);

---

SELECT

    first_name||' '||

    last_name AS "Empleado",

    department_name AS "Departamento"

FROM departments JOIN employees

USING(department_id)

WHERE department_id in (30,100);

/*Estos son los mismos ejemplos de la cláusula  NATURAL JOIN, pero ahora usando USING...*/

_____________________________________________________________________________________
Cualificando Nombres de Columna Ambiguos.
Para evitar ambigüedades es recomendable cualificar los nombres de las columnas con el nombre de la tabla a la cual pertenece.

Si no existen nombres de columna comunes en las dos tablas, no es necesario cualificar las columnas. Sin embargo, utilizar el prefijo de tabla mejora el rendimiento, ya que le indica a Oracle Server exactamente dónde puede encontrar las columnas.

•Utilice prefijos de tabla para cualificar nombres de columna que estén en varias tablas. 

•Utilice prefijos de tabla para mejorar el rendimiento. 

•Utilice alias de columna para distinguir columnas que tengan nombres idénticos pero que residan en tablas diferentes. 

•No utilice alias en columnas que estén identificadas en la cláusula USING y que se muestren en cualquier otra parte de la sentencia SQL.

Ejemplo:

SELECT employees.employee_id, employees.last_name,  

       departments.department_id, departments.department_name,

       locations.location_id, locations.city

FROM   employees, departments, locations

WHERE employees.department_id = departments.department_id

AND departments.location_id = locations.location_id;

/*Notan que en este ejemplo uso los nombres de las tablas como cualificadores antes de cada columna, también ven que muestro campos de tres tablas sin el uso de algún JOIN, mas bien uso la cláusula WHERE para establecer la relación. Esto es posible con cualquier JOIN que explico aquí, pero quise mostrar este método ya que es muy común ver este tipo de Queries en areas de desarrollo.*/

_____________________________________________________________________________________
Alias de Tabla.
Cualificar nombres de columna con nombres de tabla puede llevar mucho tiempo, especialmente si los nombres de tabla son largos. Puede utilizar alias de tabla en lugar de nombres de tabla. Al igual que los alias de columna cambian el nombre de las columnas, los alias de tabla hacen lo propio con las tablas. Los alias de tabla ayudan a reducir el código SQL, con lo que se consume menos memoria.

•Los alias de tabla pueden contener hasta 30 caracteres, pero son mejores los cortos que los largos.

•Si se utiliza un alias de tabla para un nombre de tabla concreto en la cláusula FROM, se debe sustituir el nombre de tabla por alias de tabla en toda la sentencia SELECT. 

•Los alias de tabla deben ser significativos. 

•Los alias de tabla son válidos únicamente para la sentencia SELECT actual.

Ejemplos:

SELECT e.employee_id, e.last_name,  

       d.department_id, d.department_name,

       l.location_id, l.city

FROM   employees e, departments d, locations l

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id;

---

SELECT e.employee_id, e.last_name,  

       d.location_id, department_id 

FROM   employees e JOIN departments d 

USING (department_id); 

---

SELECT l.location_id, l.city, country_id, c.country_name

FROM locations l NATURAL JOIN countries c;

/*Ejemplos ya explicados, pero ahora con Alias de Tabla.*/

_____________________________________________________________________________________

Uniones con la Cláusula ON.

Utilice la cláusula ON para especificar una condición de unión. Esto le permite especificar condiciones de unión aparte de cualquier condición de búsqueda o de filtro en la cláusula WHERE.

•La condición de unión para la unión natural es básicamente una unión igualitaria de todas las columnas con el mismo nombre. 

•Utilice la cláusula ON para especificar condiciones arbitrarias o para especificar las columnas que se unirán. 

•La condición de unión se separa de otras condiciones de búsqueda. 

•La cláusula ON facilita la comprensión del código.

Ejemplos:

SELECT e.employee_id AS E_ID, e.last_name AS E_NAME, e.department_id AS E_DEP,  

       d.department_id AS D_DEP, d.department_name AS DEP_NAME

FROM   employees e JOIN departments d 

ON     (e.department_id = d.department_id); 

/*Otro ejemplo similar a los anteriores, ahora usando la cláusula  ON.*/

---

SELECT d.department_id, d.department_name,
         l.location_id, l.city

FROM   departments d JOIN locations l 

ON     (d.location_id = l.location_id) 

WHERE LOWER(l.country_id) IN ('ca','de','uk');

/*Aquí combinamos JOIN ON con una condición adicional en la cláusula WHERE. Mostramos informaciones de los departamentos que se encuentran en algunas ciudades de CANADA,  GERMANY y UNITED KINGDOM, y de paso usamos la función LOWER, vista en publicaciones anteriores.*/
---
SELECT e.last_name AS empleado, m.last_name AS manager
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id);

/*Este ejemplo muestra como usar la cláusula ON para unir columnas con nombres diferentes dentro de la misma tabla. Esta técnica es conocida como auto-union. La tabla employees se enlaza consigo misma mediante los campos employee_id y manager_id para así mostrar los nombre de supervisor de cada empleado.*/

---
SELECT E.last_name, D.department_name, L.city, C.country_name
FROM employees E 
JOIN departments D
ON E.department_id = D.department_id 
JOIN locations L
ON D.location_id= L.location_id
JOIN countries C
ON L.country_id= C.country_id;
/*Este ejemplo muestra como mostrar campos de 4 tablas distintas usando la cláusula JOIN ON; Si es necesario puede agregar restricciones adicionales, para ello usamos la cláusula WHERE.*/

Nota: Una unión mas de 2 sentidos es una unión de mas de 2 tablas. En la sintaxis compatible con SQL:1999, las uniones se realizan de izquierda a derecha. De esta forma, la primera unión que se debe realizar es employees JOIN departments . La primera condición de unión puede hacer referencia a columnas de employees y departments, pero no puede hacer referencia a columnas de locations o countries . La segunda condición de unión puede hacer referencia a columnas de las tres primeras tablas mientras que la 3ra condición de union puede referenciar las 4 ultimas tablas... Y así sucesivamente.
_____________________________________________________________________________________

Uniones No Igualitarias.

Una unión no igualitaria es aquélla que contiene algo distinto a un operador de igualdad. Aunque se pueden utilizar condiciones como (<= y >=), es aconsejable usar BETWEEN, ya que es la más sencilla. Recuerde especificar el valor inferior en primer lugar y el mas alto al final cuando utilice BETWEEN.

Ejemplos:

SELECT e.last_name, 

       e.salary, 

       CASE WHEN j.gra = 1 THEN 'Mal Salario'

            WHEN j.gra = 2 THEN 'Salario Normal'

            WHEN j.gra = 3 THEN 'Salario Regular'

            WHEN j.gra = 4 THEN 'Salario Bueno'

            ELSE 'Excelente'

       END AS "Sal Level"

FROM employees e JOIN job_grades j 

ON e.salary BETWEEN j.lowest_sal AND j.highest_sal

ORDER BY salary ASC;

/*En este ejemplo creamos una unión no igualitaria para evaluar el grado del salario de un empleado. El salario debe estar entre cualquier par de los rangos de salario inferior y superior. De paso uso la Expresión CASE para dar un significado mas abstracto a la consulta.*/

_____________________________________________________________________________________

Uniones Externas(OUTER JOIN).

Si una fila no satisface una condición de unión, no aparecerá en el resultado de la consulta. Osea, No aparecen en una consulta de UNION Interna(INNER JOIN)los empleados que no tienen ningún departamento asignado(department_id IS NULL) o los departamentos que no están asignados a ningún empleado.

Para devolver los registros de departamentos que no tiene empleados o de empleados que no tienen un departamento, puede utilizar una unión externa(OUTER JOIN).

•En SQL:1999, la unión de dos tablas que devuelve sólo filas con correspondencia se denomina unión interna(INNER JOIN).
•Una unión entre dos tablas que devuelve los resultados de la unión interna(INNER JOIN) y las filas sin correspondencia de las tablas a la izquierda o derecha se denomina unión externa izquierda(LEFT OUTER JOIN) o derecha(RIGHT OUTER ). 

•Una unión entre dos tablas que devuelve los resultados de la unión interna y los resultados de una unión izquierda y derecha es una unión externa completa(FULL OUTER JOIN).

Ejemplos:

SELECT e.last_name, e.department_id, d.department_name 

FROM employees e LEFT OUTER JOIN departments d 

ON (e.department_id = d.department_id)

ORDER BY d.department_name DESC;

/*Este es un ejemplo de un LEFT OUTER JOIN, en el cual se muestran todos los empleados y sus respectivos departamentos, también se muestran los empleados que no tienen departamento asignado.*/

---

SELECT e.last_name, e.department_id, d.department_name 

FROM employees e RIGHT OUTER JOIN departments d 

ON (e.department_id = d.department_id)

ORDER BY d.department_name;

/*Este es un ejemplo de un RIGHT OUTER JOIN, en el cual se muestran todos los departamentos y sus respectivos empleados, también se muestran los departamentos sin empleados.*/

---

SELECT e.last_name, d.department_id, d.department_name 

FROM   employees e FULL OUTER JOIN departments d 

ON (e.department_id = d.department_id)

ORDER BY d.department_name DESC;

/*Este es un ejemplo de un FULL OUTER JOIN, en el cual se muestran todos los empleados y departamentos de la empresa, independientemente de que haya o no correspondencia entre ellos.*/

---

SELECT e.last_name, e.department_id, d.department_name 

FROM employees e, departments d

WHERE e.department_id = d.department_id(+)

ORDER BY d.department_name DESC;

/*Este ejemplo es el mismo del LEFT OUTER JOIN, pero sin la cláusula.*/

---

SELECT e.last_name, e.department_id, d.department_name 

FROM employees e, departments d 

WHERE e.department_id(+) = d.department_id

ORDER BY d.department_name;

/*Este ejemplo es el mismo del RIGHT OUTER JOIN, pero sin la cláusula.*/

_____________________________________________________________________________________

Uniones Cruzadas(CROSS JOIN) Y Productos Cartesianos.

Cuando una condición de unión no es válida o se omite por completo, el resultado es un producto cartesiano, en el que se muestran todas las combinaciones de filas. Todas las filas de la primera tabla se unen a todas las filas de la segunda tabla.

Un producto cartesiano tiende a generar gran cantidad de filas, con lo que el resultado no suele ser de utilidad. Debería incluir siempre una condición de unión válida a menos que tenga la necesidad específica de combinar todas las filas de todas las tablas.

Los productos cartesianos resultan útiles si necesita generar gran número de filas para simular una cantidad aceptable de datos.

Aunque para generar uno solo es necesario omitir una condición de union, existe la cláusula CROSS JOIN que hace lo mismo.

Ejemplo: 

SELECT last_name, department_name 

FROM   employees 

CROSS JOIN departments;

/*El ejemplo de la diapositiva muestra el apellido del empleado y el nombre del departamento de las tablas employees y departments. Como no se ha usado CROSS JOIN para unir las tablas, todas las filas de la tabla employees se unen a todas las filas de la tabla departments*/

Link con un Archivo de ejercicios de práctica!!!

_____________________________________________________________________________________

Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1