Viernes, 12 Junio 2020 03:40

Visualización de Datos de Varias Tablas

Rate this item
(1 Vote)

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 Más 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)] [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.
  • JOINtableUSING column_name, realiza una unión igualitaria basándose en el nombre de columna.
  • JOINtableON, 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áusulaNATURAL JOINse puede modificar mediante la cláusula USING para especificar las columnas que se deben utilizar para una unión igualitaria.
  • Utilice la cláusulaUSINGpara 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áusulasNATURAL JOINUSING 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áusulaUSINGy 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áusulaFROM, se debe sustituir el nombre de tabla por alias de tabla en toda la sentenciaSELECT
  • Los alias de tabla deben ser significativos. 
  • Los alias de tabla son válidos únicamente para la sentenciaSELECTactual.

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áusulaONpara 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 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 más de 2 sentidos es una unión de más 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 departments, pero no puede hacer referencia a columnas de locations 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 más 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 más 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 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

Read 308 times Last modified on Viernes, 21 Agosto 2020 18:05

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL/SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis.