Sub consultas avanzadas Objetivo Después de completar este capítulo conocerá lo siguiente: Escribir una consulta de múltiples columnas Describir y explicar el comportamiento de las sub consultas cuando valores nulos son recuperados Escribir una sub consulta en la cláusula FROM Utilizar sub consultas escalares en SQL Describir los tipos de problemas que pueden ser resueltos con sub consultas correlacionadas Escribir sub consultas correlacionadas Actualizar y eliminar filas utilizando sub consultas correlacionadas Usar los operadores EXISTS y NOT EXISTS Usar la cláusula WITH • •
• • •
• • • •
¿Qué es una sub consulta? Una sub consulta es una sentencia SELECT que es incrustada en una cláusula de otra sentencia SQL, llamada llam ada sentencia padre. La sub consulta (consulta interna) obtiene un valor que es usado por la sentencia padre. Usar una sub consulta anidada es equivalente a ejecutar dos consultas secuenciales y utilizar el resultado de la consulta interna como valor de búsqueda en la consulta externa (consulta principal). Las sub consultas pueden ser usadas para los siguientes propósitos: Proveer valores para condiciones en cláusulas WHERE, HAVING y START WITH de sentencias SELECT Definir el conjunto de filas a ser insertadas en una tabla de una sentencia INSERT o CREATE TABLE •
•
1
•
•
•
Definir el conjunto de filas a ser incluidas en una vista o snapshot en una sentencia CREATE VIEW o CREATE SNAPSHOT Definir uno o más valores para ser asignados a filas existentes en una sentencia UPDATE Definir una tabla para ser operada por el contenido de una consulta. (Esto se hace colocando la sub consulta en la cláusula FROM. Esto puede hacerse también en sentencias INSERT, UPDATE y DELETE.
Nota: Una sub consulta es evaluada en una ocasión para toda la sentencia padre.
Sub consultas Se pueden construir sentencias poderosas utilizando sub consultas. Las sub consultas pueden ser muy útiles cuando necesites seleccionar filas de una tabla con una condición que dependa de los datos de la misma u otra tabla. Las sub consultas son muy útiles para escribir sentencias SQL que necesiten valores de un o más valores condicionales desconocidos. Donde: operator
incluye un operador de comparación como >, = o IN
Nota: los operadores de comparación se encuentran en dos clases: operadores de fila única (>, =, >=, <, <>, <=) y operadores de múltiples filas (IN, ANY, ALL) Las sub consultas son a menudo referidas como sentencias SELECT anidadas, anidadas, sub-SELECT, o SELECT internos. Las consultas internas y externas pueden recuperar datos de la misma tablao de tablas diferentes.
2
Usando una sub consulta En el ejemplo anterior, la consulta interna obtiene el salario del empleado con número 149. La consulta externa utiliza el resultado de la consulta interna para desplegar los nombres de todos los empleados ganen mas que el empleado 149. Ejemplo Despliegue los nombres de todos los empleados que ganen menos que el salario promedio de la compañía.
3
Sub consultas de múltiples columnas Hasta ahora hemos escrito sub consultas de filas únicas y sub consultas de múltiples filas donde solo una columna es obtenida por la sentencia SELECT interna y estas son usadas para evaluar la expresión en la sentencia SELECT padre. Si quieres comparar dos o más columnas, debemos escribir una cláusula WHERE compuesta usando operadores lógicos. El uso de sub consultas de múltiples columnas, puede combinar condiciones WHERE duplicadas en una simple cláusula WHERE. Sintaxis:
La gráfica anterior ilustra como los valores de MANAGER_ID y DEPARTMEN_ID de la consulta principal son inicialmente comparadas con los valores de MANAGER_ID y DEPARTMENT_ID recuperados por la subconsulta. Desde que el número de columnas que son inicialmente comparadas son mas de una, el ejemplo se califica como una sub consulta de múltiples columnas.
Comparaciones en pares contra comparaciones no pares La comparación de columnas en una sub consulta de múltiples columnas puede ser una comparación par o una comparación no par.
4
En el ejemplo siguiente, una comparación par es ejecutada en la cláusula WHERE. Cada fila candidata en la sentencia SELECT debe tener ambas las mismas columnas MANAGER_ID y DEPARTMENT_ID como el empleado con el EMPLOYEE_ID 178 o 174. Una sub consulta de múltiples columnas puede también ser una comparación no par. En una comparación no par, cada columna de la cláusula WHERE de la sentencia SELECT padre es individualmente comparada con múltiples valores recuperados por la sentencia SELECT interna. Las columnas individuales pueden corresponder con algunos de los valores recuperados por la sentencia SELECT interna. Pero en conjunto, todas las condiciones múltiples de la sentencia SELECT principal deben ser satisfechas para las filas a ser desplegadas. En los siguientes ejemplos se ilustra una comparación no par.
Sub consulta con comparación par El ejemplo anterior es una sub consulta de múltiples columnas puesto que la sub consulta obtiene más de una columna. Este compara los valores en las columnas MANAGER_ID y DEPARTMENT_ID de cada fila en la tabla EMPLOYEES con los valores en las columnas MANAGER_ID y DEPARTMENT_ID para los empleados con EMPLOYEE_ID 178 o 174. Primero, la sub consulta que recupera los valores de MANAGER_ID y DEPARTMENT_ID para los empleados con EMPLOYEE_ID 178 o 174 es ejecutada. Estos valores son comparados con las columnas MANAGER_ID y DEPARTMENT_ID de cada fila en la tabla EMPLOYEES. Si los valores corresponden, la fila es desplegada. En el resultado, los registros de los empleados con EMPLOYEE_ID 178 o 174 no serán desplegados. El resultado de la consulta anterior es:
5
Sub consulta con comparación no par El ejemplo muestra una comparación de columnas no par. Esta despliega EMPLOYEE_ID, MANAGER_ID y DEPARTMENT_ID de todos los empleados cuyo MANAGER_ID corresponda con alguno de los MANAGER_ID´s de los empleados cuyo EMPLOYEE_ID sea 174 o 141 y DEPARTMENT_ID corresponda con alguno de los DEPARTMENT_ID´s de los empleados cuyo EMPLOYEE_ID sea 174 o 141. Primero, la sub consulta se ejecuta para recuperar los valores de MANAGER_ID para aquellos empleados con el EMPLOYEE_ID 174 o 141. De forma similar, la segunda sub consulta se ejecuta para recuperar los valores de DEPARTMENT_ID para los empleados con EMPLOYEE_ID 174 o 141. Los valores recuperados de las columnas MANAGER_ID y DEPARTMENT_ID son comparados con las columnas MANAGER_ID y DEPARTMENT_ID para cada fila en la tabla EMPLOYEES. Si la columna MANAGER_ID de la fila en la tabla EMPLOYEES corresponde con alguno de los valores de MANAGER_ID recuperados por la sub consulta interna y la columna DEPARTMENT_ID de la fila en la tabla EMPLOYEES corresponde con alguno de los valores de DEPARTMENT_ID recuperado por la segunda sub consulta, el registro es desplegado. El resultado de la consulta anterior se muestra a continuación.
6
Usando una sub consulta en la cláusula FROM Se puede usar una sub consulta en la cláusula FROM de una sentencia SELECT, el cuál es muy similar al manejo de las vistas que hemos usado. Una sub consulta en la cláusula FROM de una sentencia SELECT es también llamada una vista en línea . Una sub consulta en una cláusula FROM de una sentencia SELECT define un origen de datos para esa sentencia SELECT en particular, y solo esa sentencia SELECT. El ejemplo anterior despliega el nombre de los empleados, salarios, número de departamentos y salario promedio para todos los empleados que ganen mas que el salario promedio de su departamento. La sub consulta en la cláusula FROM es llamada b , y la consulta exterior hace referencia a la columna SALAVG usando este alias. Sub consultas escalares en SQL Una sub consulta que obtiene exactamente un valor de una columna de una fila es también llamada sub consulta escalar. Sub consultas de múltiples columnas escritas para comparar dos o más columnas, usando una cláusula WHERE compuesta y operadores lógicos, no pueden ser calificados como sub consultas escalares. El valor de una expresión en una sub consulta escalar es el valor del elemento de la lista seleccionado de la sub consulta. Si la sub consulta obtiene 0 filas, el valor de la expresión de la sub consulta escalar es nulo. Si la sub consulta obtiene más de una fila, el servidor de Oracle muestra un error. El servidor de Oracle siempre tiene el apoyo para usar una sub consulta escalar en una sentencia SELECT. El uso de una sub consulta escalar ha sido mejorado en Oracle9i . Ahora se pueden usar sub consultas escalares en: Condiciones y parte de expresiones de funciones DECODE y CASE Todas las cláusulas del SELECT excepto GROUP BY En el lado izquierdo del operador en una cláusula SET y WHERE de una sentencia UPDATE • • •
7
Sin embargo, las sub consultas escalares no son expresiones válidas en los siguientes lugares: Como valor por defecto para columnas y expresiones para clusters En la cláusula RETURNING de sentencias DML Como base de una función base indexada En la cláusula GROUP BY, constraints CHECK, condiciones WHEN Cláusulas HAVING En cláusulas START WITH y CONNECT BY En sentencias que no son relacionados con consultas, como CREATE PROFILE • • • • • • •
Ejemplo de sub consultas escalares En la primer consulta del ejemplo anterior se demuestra que las sub consultas escalares pueden ser usadas en expresiones CASE. La consulta interna obtiene el valor de 20, el cuál el DEPARTMENT_ID del departamento cuya LOCATION_ID es 1800. La expresión CASE en la consulta externa usa el resultado de la consulta interna para desplegar el número de empleado, nombre y valor de “Canada” o “USA”, dependiendo de si DEPARTMENT_ID del registro recuperado por la consulta externa es 20 o no. El resultado de esta consulta es:
8
En el segundo ejemplo se demuestra como la sub consulta escalar puede ser usada en la cláusula ORDER BY. El ejemplo ordena el resultado con base en el DEPARTMENT_NAME por la correspondencia del DEPARTMENT_ID de la tabla EMPLOYEES con el DEPARTMENT_ID de la tabla DEPARTMENTS. Esta comparación es hecha por una sub consulta escalar en la cláusula ORDER BY. El resultado se muestra a coninuación:
El segundo ejemplo usa una sub consulta correlacionada. En una sub consulta correlacionada, la sub consulta hace referencia a una columna de una tabla referida en una sentencia padre. Las sub consultas correlacionadas son explicadas en el siguiente tema.
9
Sub consulta correlacionada El servidor de Oracle ejecuta sub consultas correlacionadas cuando la sub consulta se relaciona con una columna de una tabla referida en la sentencia padre. Una sub consulta correlacionada es evaluada una vez para cada fila procesada por la sentencia padre. La sentencia padre puede ser una sentencia SELECT, UPDATE o DELETE. Sub consultas anidadas contra sub consultas correlacionadas
Con una sub consulta anidada normal, la consulta SELECT interna corre primero y se ejecuta una sola vez, obteniendo valores para ser usados en la consulta principal. Una sub consulta correlacionada, sin embargo, se ejecuta una vez para cada fila candidata considerada por la consulta externa. En otras palabras, la consulta interna es la guía para la consulta externa. Ejecución de sub consultas anidadas • •
La consulta interna se ejecuta primero y encuentra un valor La consulta externa se ejecuta una vez, usando el valor de la consulta interna
Ejecución de sub consultas correlacionadas • • •
•
Recibe una fila candidata (obtenida por la consulta externa) Ejecuta la consulta interna usando el valor de la fila candidata Usa los valores resultantes de la consulta interna para calificar o descalificar la fila candidata Se repite hasta terminar con las filas candidatas
10
Una sub consulta correlacionada es un camino de lectura de cada fila en una tabla y la comparación de valores en cada fila contra los datos relacionados. Es usado cuantas veces una sub consulta deba retornar un resultado diferente o conjunto de resultados para cada fila candidata considerada por la consulta principal. En otras palabras, utilice una consulta correlacionada para resolver una pregunta de múltiples partes cuya respuesta dependa del valor de cada fila procesada por la sentencia padre. El servidor de Oracle ejecuta una sub consulta correlacionada cuando la sub consulta hace referencia a una columna de la consulta padre. Nota: Puedes usar los operadores ANY y ALL en una sub consulta correlacionada.
11
Usando sub consultas correlacionadas En el ejemplo anterior se determina que empleados ganan más del salario promedio de su departamento. En este caso, la sub consulta correlacionada específicamente calcula el salario promedio para cada departamento. Puesto que ambas consultas, la interna y la externa utilizan la tabla EMPLOYEES en la cláusula FROM, un alias es utilizado para la tabla EMPLOYEES en la sentencia SELECT externa, para mayor claridad. No solamente el uso del alias hace que la sentencia SELECT sea de mejor lectura, pero sin ese alias la consulta puede no trabajar apropiadamente, debido a que la sentencia interna puede no ser capaz de distinguir la columna de la tabla interna de la columna de la tabla externa.
12
En el ejemplo se despliegan los detalles de aquellos empleados que hayan cambiado de puesto mínimo dos veces. El servidor de Oracle evalúa la consulta correlacionada como sigue: 1. Se selecciona una fila de la tabla especificada en la consulta externa. Esta puede ser denominada la fila candidata actual. 2. Se almacena el valor de la columna referenciada en la sub consulta de la fila candidata. (En el ejemplo, la columna referenciada en la sub consulta es E. EMPLOYEE_ID) 3. Se ejecuta la sub consulta con el valor de la condición referenciada de la fila candidata de la consulta externa. (En el ejemplo, la función de grupo COUNT(*) es evaluado con base en el valor de la columna E.EMPLOYEE_ID obtenida en el paso 2.) 4. Se evalúa la cláusula WHERE de la consulta externa en base al resultado de la sub consulta ejecutada en el paso 3. Esto determina si la fila candidata es desplegada. (En el ejemplo, el número de veces que un empleado ha sido cambiado de puesto, evaluado por la sub consulta, es comparado con el valor 2 en la cláusula WHERE de la consulta externa. Si la condición es satisfecha, el registro del empleado es mostrado.) 5. Se repite el procedimiento para las siguientes filas candidatas de la tabla, y así hasta que todas las filas en la tabla hayan sido procesadas. La correlación es establecida por el uso de un elemento de la consulta externa en la sub consulta. En este ejemplo, la correlación es establecida por la sentencia EMPLOYEE_ID = E.EMPLOYEE_ID en donde se compara EMPLOYEE_ID de la tabla en la sub consulta con el EMPLOYEE_ID de la tabla de la consulta externa.
13
Operador EXISTS Cuando anidamos sentencias SELECT, todos los operadores lógicos son válidos. En suma, se puede usar el operador EXISTS. Este operador es frecuentemente usado en sub consultas correlacionadas para verificar cuando un valor recuperado por la consulta externa existe en el conjunto de resultados obtenidos por la consulta interna. Si la sub consulta obtiene al menos una fila, el operador obtiene el valor TRUE. Si el valor no existe, se obtiene el valor FALSE. Consecuentemente, NOT EXISTS verifica cuando un valor recuperado por la consulta externa no es parte del conjunto de resultados obtenidos por la consulta interna.
Usando el operador EXISTS El operador EXISTS se asegura que la búsqueda en la consulta interna no continué cuando al menos una correspondencia sea encontrada para el jefe y número de empleado en la condición: WHERE manager_id = outer.employee_id. Note que el SELECT de la consulta interna no necesita obtener un valor específico, de tal manera que una constante puede ser seleccionada. Desde el punto de vista de la ejecución, es más rápido seleccionar una constante que una columna. Nota: Teniendo EMPLOYEE_ID en la cláusula SELECT de la consulta interna causa una búsqueda de esa columna en la tabla. Remplazando esta por una literal X, o cualquier constante, mejora el desempeño. Esto es más eficiente que el uso del operador IN.
14
Un operador IN puede ser usado como una alternativa para un operador EXISTS, como se ve en el siguiente ejemplo:
Usando el operador NOT EXISTS
Solución alternativa
Un operador NOT IN puede ser utilizado como una alternativa para el operador NOT EXISTS, como se muestra en el siguiente ejemplo:
Sin embargo, NOT IN evalúa a FALSE si algún miembro del conjunto de resultados es un valor nulo. Por consiguiente, las consultas pueden no obtener algunas filas si estas filas en la tabla DEPARTMENTS no satisfacen la condición WHERE.
15
UPDATE Correlacionado En el caso de la sentencia UPDATE, se puede usar una sub consulta correlacionada para actualizar filas en una tabla con base a las filas de otra tabla.
En el ejemplo se modifica la tabla EMPLOYEES con la adición de la columna DEPARTMENT_NAME, para almacenar el nombre del departamento y poblar la tabla con el uso del UPDATE correlacionado.
16
Problema en la sentencia
Use una sub consulta correlacionada para actualizar filas en la tabla EMPLOYEES basándose en las filas de la tabla REWARDS:
Este ejemplo usa la tabla REWARDS. La tabla REWARDS tiene la columna EMPLOYEE_ID, PAY_RAISE y PAYRAISE_DATE. Cada ves que un empleado tiene un aumento de sueldo, un registro con el detalle del empleado, la cantidad de incremento y la fecha es insertada en esta tabla. La tabla REWARDS puede contener más de un registro para un empleado. La columna PAYRAISE_DATE es utilizada para identificar el aumento más reciente recibido por un empleado. En el ejemplo, la columna SALARY en la tabla EMPLOYEES es actualizada para reflejar el último aumento recibido para el empleado. Esto es realizado incrementando al salario actual el incremento otorgado.
17
DELETE Correlacionado En el caso de la sentencia DELETE, se puede usar una sub consulta correlacionada para eliminar solo aquellas filas que también existan en otra tabla. Si decides que debes mantener solo los últimos cuatro registros históricos de la tabla JOB_HISTORY, entonces cuando un empleado sea transferido a su quito puesto, debes de eliminar las filas mas antiguas. El siguiente código muestra como se puede usar un DELETE correlacionado:
18
Ejemplo
Dos tablas son usadas en este ejemplo: La tabla EMPLOYEES, que proporciona los detalles de los empleados actuales La tabla EMP_HISTORY, que proporciona los detalles de los empleados anteriores •
•
EMP_HISTORY contiene los datos de los empleados anteriores, sin embargo esto podría ser incorrecto si el mismo empleado existe en ambas tablas. El ejemplo anterior elimina los registros incorrectos con el uso de una sub consulta correlacionada.
Cláusula WITH Usando la cláusula WITH, se puede definir un bloque de una consulta antes de que esta sea usada. La cláusula WITH (formalmente conocida como “subquery_factoring_clausula ” cláusula de sub consulta factorizada) habilita la reutilización del mismo bloque de la consulta en una sentencia SELECT cuando esto ocurre en mas de una ocasión en una consulta compleja. Esto es particularmente útil cuando una consulta tiene muchas referencias al mismo bloque de una consulta y se tienen asociaciones y agrupaciones. Usando la cláusula WITH, se puede reutilizar la misma consulta cuando es de alto costo evaluar el bloque de la consulta y ocurre más de una vez en una consulta compleja. Usando la cláusula WITH, el servidor de Oracle recupera los resultados de un bloque de la consulta y los almacena en un tablespace temporal del usuario. Esto puede mejorar el desempeño.
19
Beneficios de la cláusula WITH • •
Hace que la consulta sea fácil de leer Evalúa una cláusula una sola vez, aun si esta aparece muchas veces en la consulta, por esta razón aumenta el desempeño
Ejemplo de la cláusula WITH
Problema Usando la cláusula WITH, escriba una consulta para desplegar el nombre del departamento y el salario total para aquellos departamentos cuyo salario total es mayor que el salario promedio de los departamentos. Este problema requiere de los siguientes cálculos intermedios: 1. Calcular el salario total para cada departamento y almacenar el resultado usando la cláusula WITH 2. Calcular el salario promedio de todos los departamentos y almacenar el resultado usando la cláusula WITH 3. Comparar el salario total calculado en el paso 1 con el salario promedio calculado en el paso 2. Si el salario total de un departamento en particular es mayor que el salario total de todos los departamentos, despliegue el nombre del departamento y el salario total para ese departamento. La solución se muestra a continuación:
El código SQL anterior un ejemplo de la situación en la cual se puede mejorar el desempeño y escribir sentencias SQL de forma más clara con el uso de la cláusula WITH. La consulta crea los nombres de consulta DEPT_COSTS y AVG_COST que son usadas en el cuerpo de la consulta principal. Internamente, la cláusula WITH es determinada como una vista en línea o una tabla temporal. El decidir optimizar la solución apropiada depende del costo o beneficio del almacenamiento temporal de los resultados con la cláusula WITH. 20
Nota: Una sub consulta en la cláusula FROM de una sentencia SELECT es también llamada vista en línea. El resultado generado por código SQL anterior muestra lo siguiente:
Consideraciones para el uso de la cláusula WITH • •
•
•
Esta es usada solo con sentencias SELECT Un nombre de consulta es visible para todos los elementos dentro del bloque WITH (incluyendo sus bloques de sub consultas) Cuando el nombre de una consulta es el mismo que el nombre de una tabla, el análisis se hace de adentro hacia fuera, el nombre del bloque de la consulta toma precedencia sobre el nombre de la tabla La cláusula WITH puede mantener más de una consulta. Cada consulta es entonces separada por una coma.
Resumen En este capítulo se ha revisado lo siguiente: Una sub consulta de múltiples columnas obtiene mas de una columna Las comparaciones de múltiples columnas pueden ser en pares o no pares. Una sub consulta de múltiples columnas puede también ser usada en la cláusula FROM de una sentencia SELECT Las sub consultas escalares han sido mejoradas en Oracle9i Las sub consultas correlacionadas son útiles cuando una sub consulta debe obtener un resultado diferente por cada fila candidata El operador EXISTS es un operador Boleano que verifica la presencia de un valor Las sub consultas correlacionadas pueden ser usadas con sentencias SELECT, UPDATE y DELETE Se puede usar la cláusula WITH para usar el mismo bloque de consulta en una sentencia SELECT cuando esta sea necesaria en más de una ocasión. • •
•
• •
•
•
•
21
