SQL Capitulo 6 : Visualización de Datos de Varias Tablas Utilizando Uniones en SQL Developer

Sean bienvenidos a esta sección donde se revisa de como realizar consultas de datos de varias tablas utilizando uniones, recuerden estas sentencias estan siendo ejecutadas en SQL developer administrador de la Base de datos Oracle.

Objetivos de la publicación:
Al finalizar esta lección, debería estar capacitado para lo siguiente:

• Escribir sentencias SELECT para acceder a datos de más de una tabla mediante uniones igualitarias y no igualitarias
• Unir una tabla consigo misma mediante autounión
• Ver datos que normalmente no cumplen una condición de unión mediante uniones OUTER
• Generar un producto cartesiano de todas las filas de una o más tablas

Esta lección explica cómo obtener datos de más de una tabla. Una unión se utiliza para ver información de varias tablas. Por lo tanto, puede unir tablas para ver información de más de una tabla.

Nota: para obtener más información sobre las uniones, consulte la sección sobre consultas y subconsultas SQL: uniones en Oracle Database SQL Language Reference (Referencia sobre Lenguaje SQL de Oracle Database).

Tipos de JOINS y sintaxis

Obtención de Datos de Varias Tablas

A veces necesita utilizar datos de más de una tabla. En el ejemplo, el informe muestra datos de dos tablas independientes:

La tabla EMPLOYEES contiene los ID de empleado.

Las tablas EMPLOYEES y DEPARTMENTS contienen los ID de departamento.

La tabla DEPARTMENTS contiene los nombres de departamento.

Para producir el informe, necesita enlazar las tablas EMPLOYEES y DEPARTMENTS y acceder a los datos de ambas.

Tipos de Uniones

Las uniones compatibles con el estándar SQL:1999 incluyen los siguientes elementos:

Uniones naturales:

Cláusula NATURAL JOIN

Cláusula USING

Cláusula ON

UnionesOUTER:

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

Uniones cruzadas

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

Nota : En versiones anteriores a Oracle9i  la sintaxis de unión era diferente a los estándares American National Standards Institute (ANSI). La sintaxis de unión compatible con SQL:1999 no ofrece ninguna ventaja en el rendimiento respecto a la sintaxis de unión propietaria de Oracle que existía en las versiones anteriores. Para obtener más información sobre la sintaxis de unión propiedad de Oracle, consulte el apéndice F: Sintaxis de Unión en Oracle.

Unión de Tablas mediante la Sintaxis SQL:1999

Utilizar una unión para consultar datos de más de una tabla:

En la sintaxis:

table1.column indica la tabla y la columna desde las que se recuperan los datos

NATURAL JOIN une dos tablas basándose en el mismo nombre de la columna

JOIN table2 USING column_name realiza una unión igualitaria basándose en el nombre de la columna

JOIN table2 ON table1.column_name = table2.column_name performs realiza una unión igualitaria basándose en la condición de la cláusula ON

LEFT/RIGHT/FULL OUTER se utiliza para realizar uniones OUTER

CROSS JOIN devuelve un producto cartesiano de las dos tablas

Para obtener más información, consulte la sección sobre SELECT en Oracle Database SQL Language Reference (Referencia sobre Lenguaje SQL de Oracle Database).

Cualificación de Nombres de Columna Ambiguos

Utilizar prefijos de tabla para cualificar los nombres de columna que están en varias tablas.

Utilizar prefijos de tabla para mejorar el rendimiento.

En lugar de prefijos de nombre de tabla completos, utilizar alias de tabla.

Los alias de tablas proporciona un nombre más corto de una tabla:

Mantiene el código SQL más pequeño, utiliza menos memoria

Utilizar alias de columna para distinguir columnas que tienen nombres idénticos, pero que residen en diferentes tablas.

Al unir dos o más tablas, debe cualificar los nombres de las columnas con el nombre de la tabla para evitar ambigüedad. Sin los prefijos de tabla, la columna DEPARTMENT_ID de la lista SELECT puede provenir de la tabla DEPARTMENTS o de la tabla EMPLOYEES. Es necesario agregar el prefijo de tabla para ejecutar la consulta. Si no existen nombres de columna comunes entre las dos tablas, no es necesario cualificar las columnas. Sin embargo, el uso del prefijo de tabla mejora el rendimiento, ya que indica al servidor de Oracle dónde encontrar exactamente las columnas. 

Sin embargo, la cualificación de nombres de columna con nombres de tabla puede llevar bastante tiempo, especialmente si los nombres de tabla son largos. En su lugar, puede utilizar alias de tabla. Igual que un alias de columna proporciona otro nombre a una columna, un alias de tabla proporciona otro nombre a una tabla. Los alias de tabla ayudan a mantener el código SQL más pequeño y, por lo tanto, menos uso de memoria

El nombre de tabla se especifica por completo, seguido de un espacio y del alias de tabla. Por ejemplo, a la tabla EMPLOYEES se le puede proporcionar el alias e y, a la tabla DEPARTMENTS el alias d.

Instrucciones

Los alias de tabla pueden tener hasta 30 caracteres de longitud, pero los alias más cortos son mejores que los largos.

Si se utiliza un alias de tabla para un nombre de tabla determinado en la cláusula FROM, el alias de tabla se deberá sustituir por el nombre de tabla mediante la sentencia SELECT.

Los alias de tabla deben ser significativos.

El alias de tabla es válido sólo para la sentencia actual SELECT.

Unión natural:

Cláusula USING

Cláusula ON

Creación de Uniones Naturales

La cláusula NATURAL JOIN está basada 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 coincidentes.
Si las columnas que tienen el mismo nombre tienen tipos de dato diferentes, se devolverá un error.

Puede unir tablas automáticamente basándose en las columnas de las dos tablas que tienen el mismo nombre y los mismos tipos de dato. Puede realizar esta acción utilizando las palabras clave NATURAL JOIN.

Nota: la unión se puede producir únicamente en las columnas que tienen los mismos nombres y los mismos tipos de dato en ambas tablas. Si las columnas tienen el mismo nombre pero tipos de dato diferentes, la sintaxis NATURAL JOIN produce un error.

Recuperación de Registros con Uniones Naturales

En el ejemplo, la tabla LOCATIONS está unida a la tabla DEPARTMENT mediante la columna LOCATION_ID, que es la única con el mismo nombre en ambas. Si están presentes otras columnas comunes, la unión debe haberlas utilizado todas.

Uniones Naturales con la Cláusula WHERE

Las restricciones adicionales en una unión natural se implantan utilizando una cláusula WHERE. El siguiente ejemplo limita las filas de salida a las que tienen un ID de departamento igual a 20 o a 50:

   SELECT  department_id, department_name, location_id, city
   FROM    departments
   NATURAL JOIN locations
   WHERE   department_id IN (20, 50);

Creación de Uniones con la Cláusula USING

Si varias columnas tienen el mismo nombre pero los tipos de dato no coinciden, utilizar la cláusula USING para especificar las columnas para la unión igualitaria.

Utilizar USING para que sólo coincida una columna en caso de que coincida más de una.

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

Las uniones naturales utilizan todas las columnas con tipos de dato y nombres coincidentes para unir las tablas. La cláusula USING se puede utilizar para especificar sólo las columnas que se deben utilizar para una unión igualitaria. 

Unión de Nombres de Columna

Para determinar el nombre de departamento de un empleado, compare el valor de la columna DEPARTMENT_ID en la tabla EMPLOYEES con los valores de DEPARTMENT_ID de la tabla DEPARTMENTS. La relación entre las tablas EMPLOYEES y DEPARTMENTS es una unión igualitaria; es decir, los valores de la columna DEPARTMENT_ID de ambas tablas deben ser iguales. Normalmente, este tipo de unión implica complementos de clave primaria y ajena.

Nota: las uniones igualitarias también se denominan uniones simples o uniones internas.

 Recuperación de Registros con la Cláusula USING

En el ejemplo, las columnas DEPARTMENT_ID de las tablas EMPLOYEES y DEPARTMENTS se unen y, por lo tanto, se muestra el LOCATION_ID del departamento en el que trabaja el empleado.

Uso de Alias de Tabla con la Cláusula USING

No cualificar una columna que se utilice en la cláusula USING. 

Si la misma columna se utiliza en otro lugar de la sentencia SQL, no se le puede agregar un alias.

Al realizar una unión con la cláusula USING, no puede cualificar una columna que se utiliza en la cláusula USING en sí. Además, si esa columna se utiliza en cualquier ubicación de la sentencia SQL, no se le puede agregar ningún alias. Por ejemplo, en la consulta mencionada, no debe agregar un alias a la columna location_id en la cláusula WHERE porque la columna se utiliza en la cláusula USING.

Las columnas a las que se hace referencia en la cláusula USING no deben tener un cualificador (nombre o alias de la tabla) en cualquier ubicación de la sentencia SQL. Por ejemplo, la siguiente sentencia es válida:

SELECT l.city,  d.department_name

FROM   locations l JOIN departments d USING (location_id)

WHERE  location_id = 1400;

Las columnas comunes en ambas tablas, pero que no se utilizan en la cláusula USING, se deben prefijar con un alias de tabla, de lo contrario aparecerá el error “column ambiguously defined”.

En la siguiente sentencia , manager_id  está presente tanto la tabla  employees como en departments ; si no se prefija   manager_id  con un alias de tabla, se produce el error “column ambiguously defined”.

El siguiente ejemplo es válido:

SELECT first_name, d.department_name, d.manager_id

FROM   employees e JOIN departments d USING (department_id)

WHERE  department_id = 50;

Creación de Uniones con la Cláusula ON

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

Utilizar la cláusula ON para especificar condiciones arbitrarias o columnas que se van a unir.

La condición de unión está separada de otras condiciones de búsqueda.

La clausula ON facilita la comprensión del código.

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

Recuperación de Registros con la Cláusula ON

En este ejemplo, las columnas DEPARTMENT_ID en la tabla EMPLOYEES y DEPARTMENTS se unen con la cláusula ON. Cuando un ID de departamento de la tabla EMPLOYEES sea igual al ID de departamento de la tabla DEPARTMENTS, se devolverá una fila. El alias de tabla es necesario para cualificar los column_names coincidentes.

También puede utilizar la cláusula ON para unir columnas que tienen nombres diferentes. Los paréntesis de las columnas unidas, como se muestra en el ejemplo, (e.department_id = d.department_id) son opcionales. Incluso, ON e.department_id = d.department_id funcionará.

Nota: al utilizar el icono Execute Statement para ejecutar la consulta, SQL Developer agrega el sufijo “_1” para diferenciar entre los dos department_ids. 

Creación de Uniones en 3 Direcciones con la Cláusula ON

Una unión en tres direcciones es una unión de tres tablas. En la sintaxis compatible con SQL:1999, las uniones se realizan de izquierda a derecha. Por lo tanto, la primera unión que se realiza es EMPLOYEES JOIN DEPARTMENTS. La primera condición de unión puede hacer referencia a las columnas de EMPLOYEES y DEPARTMENTS, pero no puede hacer referencia a las columnas de LOCATIONS. La segunda condición de unión puede hacer referencia a las columnas de las tres tablas.

Nota: el ejemplo de código también se puede realizar con la cláusula USING:

SELECT e.employee_id, l.city, d.department_name

FROM employees e

JOIN   departments d

USING (department_id)

JOIN   locations l

USING (location_id);

Aplicación de Condiciones Adicionales a una Unión

Uso de la cláusula AND o la cláusula WHERE para aplicar condiciones adicionales:

Puede aplicar condiciones adicionales a la unión. 

El ejemplo realiza una unión en las tablas EMPLOYEES y DEPARTMENTS y, además, muestra sólo los empleados que tienen un ID de gestor de 149. Para agregar condiciones adicionales a la cláusula ON, puede agregar cláusulas AND. Por otro lado, puede utilizar una cláusula WHERE para aplicar condiciones adicionales.

Autounión

Unión de una Tabla consigo Misma

Puede que a veces necesite unir una tabla consigo misma. Para buscar el nombre de cada gestor del empleado, necesita unir la tabla EMPLOYEES consigo misma o realizar una autounión. 

Por ejemplo, para buscar el nombre del gestor de Lorentz, necesita: 

Buscar a Lorentz en la tabla EMPLOYEES buscando en la columna LAST_NAME.

Busque el número de gestor de Lorentz consultando la columna MANAGER_ID. El número de gestor de Lorentz es 103.

Busque el nombre del gestor con un EMPLOYEE_ID de 103 consultando la columna LAST_NAME. El número de empleado de Hunold es 103, por lo que Hunold en el gestor de Lorentz. 

Durante este proceso, busca dos veces en la tabla. La primera vez, cuando consulta la tabla para buscar a Lorentz en la columna LAST_NAME y el valor de MANAGER_ID de 103. La segunda vez, cuando busca en la columna EMPLOYEE_ID para buscar 103 y en la columna LAST_NAME para buscar a Hunold.

Autouniones que Utilizan la Cláusula ON

La cláusula ON también se puede utilizar para unir columnas con nombres diferentes, ya sea en la misma tabla o en otra diferente. 

En el ejemplo se muestra una autounión de la tabla EMPLOYEES basada en las columnas EMPLOYEE_ID y MANAGER_ID.

Nota: los paréntesis de las columnas unidas, como se muestra (e.manager_id = m.employee_id) son opcionales. Incluso ON e.manager_id = m.employee_id funcionará.

Uniones no igualitarias

Una unión no igualitaria es una condición de unión que contiene algún operador diferente del operador de igualdad.

La relación entre la tabla EMPLOYEES y JOB_GRADES es un ejemplo de unión no igualitaria. Los rangos de la columna SALARY en la tabla EMPLOYEES entre los valores en las columnas LOWEST_SAL y HIGHEST_SAL de la tabla JOB_GRADES. Por lo tanto, se pueden agregar grados a cada empleado según el salario. La relación se obtiene mediante un operador distinto del de igualdad (=).

Recuperación de Registros con Uniones no Igualitarias

El ejemplo crea una unión no igualitaria para evaluar el grado de salario de un empleado.  El salario debe estar entre cualquier par de los rangos de salario bajos y altos.

Es importante tener en cuenta que todos los empleados aparecerán sólo una vez al ejecutar esta consulta. No se repite ningún empleado en la lista. Existen dos motivos por los que se produce este hecho:

Ninguna de las filas de la tabla JOB_GRADES contiene grados que se solapen. Es decir, el valor de salario de un empleado sólo puede oscilar entre los valores de salario bajo y alto de una de las filas de la tabla de grados de salario.

Todos los salarios de los empleados oscilan entre los límites proporcionados por la tabla de grados de cargo. Es decir, ningún empleado gana menos que el valor más bajo de la columna LOWEST_SAL o más que el valor más alto de la columna HIGHEST_SAL.

Nota: se pueden utilizar otras condiciones (como <= y >=), pero BETWEEN es la más simple. Recuerde especificar primero el valor bajo y, a continuación, el alto al utilizar BETWEEN. El servidor Oracle convierte la condición BETWEEN en un par de condiciones AND. Por lo tanto, el uso de BETWEEN no proporciona ninguna ventaja,  se recomienda sólo para la simplicidad lógica.

En el ejemplo se han especificado los alias de tabla por motivos de rendimiento, no por una posible ambigüedad.

Unión OUTER:

Cláusula LEFT OUTER

Unión RIGHT OUTER

Unión FULL OUTER

Devolución de Registros sin Coincidencia Directa con las uniones OUTER

Si una fila no cumple una condición de unión, la fila no aparece en el resultado de consultas.

En el ejemplo, se utiliza una condición de unión igualitaria en las tablas EMPLOYEES y DEPARTMENTS para devolver el resultado a la derecha. El juego de resultados no contiene lo siguiente:

El ID de departamento 190, porque no hay ningún empleado con dicho ID registrado en la tabla EMPLOYEES.

El empleado con apellido Grant, porque a este empleado no se ha asignado un  ID departamento.

Para volver al registro de departamento que no tiene ningún empleado o empleados que no tienen un departamento asignado, puede utilizar la unión OUTER.

Uniones INNER frente a Uniones OUTER

En SQL:1999, la unión de dos tablas que devuelven sólo filas coincidentes se denomina unión INNER.

Una unión entre dos tablas que devuelve los resultados de la unión INNER y las filas no coincidentes de las tablas izquierda (o derecha) se denomina una unión OUTER.

Una unión entre dos tablas que devuelve los resultados de una unión INNER y los resultados de una unión izquierda y derecha da como resultado una unión OUTER completa.

La unión de las tablas con cláusulas NATURAL JOIN, USING u ON da como resultado una unión INNER. Cualquier fila no coincidente no aparece en la salida. Para devolver las filas no coincidentes, puede utilizar una unión OUTER. Una unión OUTER devuelve todas las filas que cumplen la condición de unión y también algunas o todas las filas de una tabla para la que ninguna fila de la otra tabla cumple la condición de unión. 

Hay tres tipos de uniones OUTER:

LEFT OUTER

RIGHT OUTER

FULL OUTER

LEFT OUTER JOIN

Esta consulta recupera todas las filas de la tabla EMPLOYEES que es la tabla de la izquierda, incluso si no hay ninguna coincidencia en la tabla DEPARTMENTS.

RIGHT OUTER JOIN

Esta consulta recupera todas las filas de la tabla DEPARTMENTS que es la tabla de la izquierda, incluso si no hay ninguna coincidencia en la tabla EMPLOYEES.

FULL OUTER JOIN

Esta consulta recupera todas las filas de la tabla EMPLOYEES, incluso si no hay ninguna coincidencia en la tabla DEPARTMENTS. También  recupera todas las filas de la tabla DEPARTMENTS, incluso si no hay ninguna coincidencia en la tabla EMPLOYEES.

Producto cartesiano

    Unión cruzada

Productos Cartesianos

• Un producto cartesiano se forma cuando:
• Se omite una condición de unión
• Una condición de unión no es válida
• Todas las filas de la primera tabla se unen a todas las filas de la segunda tabla
• Se incluye siempre una condición de unión válida si desea evitar un producto cartesiano.

Cuando una condición de unión no es válida o se omite completamente, 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 un gran número de filas y el resultado es poco útil. Debe incluir siempre una condición de unión válida a menos que tenga necesidades específicas de combinar todas las filas de todas las tablas.

Los productos cartesianos son útiles para algunas pruebas para las que necesite generar un gran número de filas para simular una cantidad razonable de datos.

Generación de un Producto Cartesiano

Se genera un producto cartesiano si se omite una condición de unión. En el ejemplo se muestra el apellido del empleado y el nombre del departamento de las tablas EMPLOYEES y DEPARTMENTS. Ya que no se ha especificado ninguna condición de unión, todas las filas (20 filas) de la tabla EMPLOYEES se unen con todas las filas (8 filas) de la tabla DEPARTMENTS por lo que se generan 160 filas en la salida.

Creación de Uniones Cruzadas

La cláusula CROSS JOIN produce el producto combinado de dos tablas.

Esto también se denomina un producto cartesiano entre dos tablas.

El ejemplo produce un producto cartesiano de las tablas EMPLOYEES y DEPARTMENTS.

La técnica CROSS JOIN se puede aplicar a muchas situaciones de forma útil. Por ejemplo, para devolver el costo de mano de obra total por oficina por mes, incluso si el mes X no tiene costo de mano de obra, puede realizar un unión cruzada de oficinas con la tabla de todos los meses.

Es una práctica aconsejable declarar de forma explícita  CROSS JOIN en SELECT si desea crear un producto cartesiano. Por lo tanto, queda muy claro que desea que esto ocurra y que no se trata del resultado de las uniones que faltan 

Resumen

En esta lección debe haber aprendido a utilizar uniones para mostrar los datos de varias tablas utilizando:

• Uniones igualitarias
• Uniones no igualitarias
• Uniones OUTER
• Autouniones
• Uniones cruzadas
• Uniones naturales
• Unión OUTER completa (o de dos lados)

Hay varios modos de unir tablas. 

Tipos de Uniones

• Uniones igualitarias
• Uniones no igualitarias
• Uniones OUTER
• Autouniones
• Uniones cruzadas
• Uniones naturales
• Unión OUTER completa (o de dos lados)

Productos Cartesianos

Un producto cartesiano da como resultado una visualización de todas las combinaciones de filas. Para ello debe omitir la cláusula WHERE o especificar la cláusula CROSS JOIN.

Alias de Tabla

Los alias de tabla aceleran el acceso a la base de datos.

Los alias pueden ayudar a mantener el código SQL más pequeño utilizando de esta forma menos memoria.

A veces los alias de tabla son obligatorios para evitar la ambigüedad de columna.