•Escribir una subconsulta de varias columnas.
•Utilizar subconsultas escalares en SQL.
•Solucionar problemas con subconsultas correlacionadas.
•Actualizar y suprimir filas mediante subconsultas correlacionadas.
•Utilizar los operadores EXISTS y NOT EXISTS.
•Utilizar la cláusula WITH.
NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
_____________________________________________________________________________________
Subconsultas de Varias Columnas.
Sintaxis:
Comparaciones entre Pares y entre No Pares.
Las comparaciones de columnas en una subconsulta de varias columnas pueden ser entre pares y entre no pares.
Una subconsulta de varias columnas también puede ser una comparación entre no pares. En una comparación entre no pares, cada columna de la cláusula WHERE de la sentencia SELECT principal se compara individualmente con varios valores recuperados por la sentencia SELECT interna. Las columnas individuales se pueden corresponder con cualquier valor recuperado por la sentencia SELECT interna. Pero colectivamente, se deben satisfacer todas las condiciones múltiples de la sentencia SELECT principal para que se muestre la fila. El ejemplo de la página siguiente ilustra una comparación entre pares.
Primero, se ejecuta la subconsulta para recuperar los valores de MANAGER_ID y DEPARTMENT_ID para los empleados con EMPLOYEE_ID 199 ó 174. Estos valores se comparan con la columna MANAGER_ID y la columna DEPARTMENT_ID de cada fila de la tabla EMPLOYEES. Si los valores se corresponden, se muestra la fila. En la salida, no se mostrarán los registros de los empleados con EMPLOYEE_ID 199 ó 174.*/
•Subconsultas escalares en expresiones CASE:
Oracle Server realiza una consulta correlacionada cuando la subconsulta hace referencia a una columna desde una tabla a la que se hace referencia en la sentencia principal. Una subconsulta correlacionada se evalúa una vez para cada fila procesada por la sentencia principal. La sentencia principal puede ser una sentencia SELECT, UPDATE o DELETE.
Subconsultas Anidadas frente a Subconsultas Correlacionadas:
Con una subconsulta anidada normal, la consulta SELECT interna se ejecuta primero y una vez, y devuelve valores que serán utilizados por la consulta principal. Sin embargo, una subconsulta correlacionada se ejecuta una vez para cada fila candidata considerada por la consulta externa. Dicho de otro modo, la consulta interna está controlada por la consulta externa.
Ejecución de Subconsultas Anidadas:
•La consulta interna se ejecuta primero y encuentra un valor.
•La consulta externa se ejecuta una vez y utiliza el valor de la consulta interna.
Ejecución de Subconsultas Correlacionadas:
•Obtenga una fila candidata (recuperada por la consulta externa).
•Ejecute la consulta interna mediante el valor de la fila candidata.
•Utilice los valores resultantes de la consulta interna para calificar o descalificar la fila candidata.
•Repita el proceso hasta que no queden filas candidatas.
Una subconsulta correlacionada es una forma de leer todas las filas de una tabla y comparar los valores de cada fila con datos relacionados. Se utiliza siempre que una subconsulta deba devolver un resultado o un juego de resultados diferente para cada fila candidata considerada por la consulta principal. Dicho de otro modo, utilice una subconsulta correlacionada para responder a una pregunta de varias partes cuya respuesta dependa del valor de cada fila procesada por la sentencia principal.
Oracle Server realiza una consulta correlacionada cuando la subconsulta hace referencia a una columna desde una tabla de la consulta principal.
Nota: Puede utilizar los operadores ANY y ALL en una subconsulta correlacionada.
Se puede utilizar una construcción NOT IN como alternativa para un operador NOT EXISTS, como se muestra en el siguiente ejemplo:
Sin embargo, NOT IN se evalúa como FALSE si cualquier miembro del juego es un valor NULL. Por tanto, la consulta no devolverá ninguna fila incluso aunque haya filas en la tabla DEPARTMENTS que satisfagan la condición WHERE.
Consulta Correlacionada: UPDATE.
En el caso de la sentencia UPDATE, puede utilizar una subconsulta correlacionada para actualizar filas de una tabla basada en filas de otra tabla.
_____________________________________________________________________________________
Subconsultas de Varias Columnas.
Hasta ahora, ha escrito subconsultas de una sola fila y subconsultas de varias filas en las que sólo se devuelve una columna mediante la sentencia interna SELECT y esto se utiliza para evaluar la expresión de la sentencia SELECT principal. Si desea comparar dos o más columnas, debe escribir una cláusula WHERE compuesta mediante operadores lógicos. Mediante subconsultas de varias columnas, puede combinar condiciones WHERE duplicadas en una sola cláusula WHERE.
Sintaxis:
SELECT column, column, ...
FROM table
WHERE (column, column, ...) IN
(SELECT column, column, ...
FROM table
WHERE condition);
El gráfico ilustra que los valores de MANAGER_ID y DEPARTMENT_ID de la consulta principal se están comparando con los valores MANAGER_ID y DEPARTMENT_ID recuperados por la subconsulta. Como el número de columnas que se están comparando es superior a uno, el ejemplo se califica como subconsulta de varias columnas.
Comparaciones entre Pares y entre No Pares.
Las comparaciones de columnas en una subconsulta de varias columnas pueden ser entre pares y entre no pares.
Una subconsulta de varias columnas también puede ser una comparación entre no pares. En una comparación entre no pares, cada columna de la cláusula WHERE de la sentencia SELECT principal se compara individualmente con varios valores recuperados por la sentencia SELECT interna. Las columnas individuales se pueden corresponder con cualquier valor recuperado por la sentencia SELECT interna. Pero colectivamente, se deben satisfacer todas las condiciones múltiples de la sentencia SELECT principal para que se muestre la fila. El ejemplo de la página siguiente ilustra una comparación entre pares.
Ejemplos:
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (199,174))
AND employee_id NOT IN (199,174);
/*El ejemplo muestra una subconsulta de varias columnas, ya que la subconsulta devuelve más de una columna. Compara los valores de la columna MANAGER_ID y de la columna DEPARTMENT_ID de cada fila en la tabla EMPLOYEES con los valores de la columna MANAGER_ID y la columna DEPARTMENT_ID para los empleados con EMPLOYEE_ID 199 ó 174. Primero, se ejecuta la subconsulta para recuperar los valores de MANAGER_ID y DEPARTMENT_ID para los empleados con EMPLOYEE_ID 199 ó 174. Estos valores se comparan con la columna MANAGER_ID y la columna DEPARTMENT_ID de cada fila de la tabla EMPLOYEES. Si los valores se corresponden, se muestra la fila. En la salida, no se mostrarán los registros de los empleados con EMPLOYEE_ID 199 ó 174.*/
---
SELECT employee_id, manager_id, department_id
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
(SELECT manager_id
FROM employees
WHERE employee_id IN (174,199))
AND department_id IN
AND department_id IN
(SELECT department_id
FROM employees
WHERE employee_id IN (174,199))
AND employee_id NOT IN (199,174);
/*El ejemplo muestra una comparación
entre no pares de las columnas. Muestra los valores de EMPLOYEE_ID, MANAGER_ID y DEPARTMENT_ID de cualquier empleado cuyo
identificador de supervisor se corresponda con los identificadores de
supervisor de los empleados cuyos identificadores sean 174 ó 199 y los valores de DEPARTMENT_ID se correspondan con los
identificadores de departamento cuyo identificador de empleado sea
174 ó 199.
174 ó 199.
Primero, se ejecuta la subconsulta para recuperar los valores de MANAGER_ID para los empleados con el valor de
EMPLOYEE_ID 199 ó 174. De forma parecida, se ejecuta
la segunda subconsulta para recuperar los valores de DEPARTMENT_ID para los empleados con el valor de
EMPLOYEE_ID 199 ó 174. Los valores recuperados de
las columnas MANAGER_ID y DEPARTMENT_ID se comparan con las columnas MANAGER_ID y DEPARTMENT_ID de cada fila de la tabla EMPLOYEES. Si la columna MANAGER_ID de la fila de la tabla EMPLOYEES se corresponde con cualquiera de
los valores de MANAGER_ID recuperados por la subconsulta interna y si la columna DEPARTMENT_ID de la fila de la tabla EMPLOYEES se corresponde con cualquiera de
los valores de DEPARTMENT_ID recuperados por la segunda subconsulta, se muestra el registro.
*/
---
Subconsultas Escalares.
SELECT employee_id codigo, last_name apellido,
(CASE
WHEN department_id =
(SELECT department_id
FROM departments
WHERE location_id = 1800)
THEN 'Canada'
ELSE 'USA' END) ubicacion
FROM employees;
/*Este ejemplo demuestra que las subconsultas escalares se pueden utilizar en
expresiones CASE. La consulta interna devuelve el
valor 20, que es el identificador de departamento del departamento cuyo
identificador de ubicación es 1800. La expresión CASE de la consulta externa utiliza el
resultado de la consulta interna para mostrar el identificador de empleado, los
apellidos y un valor de Canadá o EE.UU., dependiendo de si el identificador de
departamento del registro recuperado por la consulta externa es 20 o no.*/
---
•Subconsultas escalares en la cláusula ORDER BY:
SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
/*El
segundo ejemplo demuestra que las subconsultas escalares se pueden utilizar en la
cláusula ORDER BY. En el ejemplo, se ordena la salida
basándose en DEPARTMENT_
NAME
al hacer corresponder el valor de DEPARTMENT_ID de la tabla EMPLOYEES con el valor de DEPARTMENT_ID de la tabla DEPARTMENTS. Esta comparación se realiza en una subconsulta escalar en la cláusula ORDER
BY.*/
---
El segundo ejemplo utiliza una subconsulta correlacionada. En una consulta correlacionada, la subconsulta hace referencia a una columna desde una tabla a la que se hace referencia en la sentencia principal. Las subconsultas correlacionadas se explican a continuación.
_____________________________________________________________________________________
Subconsultas Correlacionadas.Oracle Server realiza una consulta correlacionada cuando la subconsulta hace referencia a una columna desde una tabla a la que se hace referencia en la sentencia principal. Una subconsulta correlacionada se evalúa una vez para cada fila procesada por la sentencia principal. La sentencia principal puede ser una sentencia SELECT, UPDATE o DELETE.
Con una subconsulta anidada normal, la consulta SELECT interna se ejecuta primero y una vez, y devuelve valores que serán utilizados por la consulta principal. Sin embargo, una subconsulta correlacionada se ejecuta una vez para cada fila candidata considerada por la consulta externa. Dicho de otro modo, la consulta interna está controlada por la consulta externa.
Ejecución de Subconsultas Anidadas:
•La consulta interna se ejecuta primero y encuentra un valor.
•La consulta externa se ejecuta una vez y utiliza el valor de la consulta interna.
Ejecución de Subconsultas Correlacionadas:
•Obtenga una fila candidata (recuperada por la consulta externa).
•Ejecute la consulta interna mediante el valor de la fila candidata.
•Utilice los valores resultantes de la consulta interna para calificar o descalificar la fila candidata.
•Repita el proceso hasta que no queden filas candidatas.
Sintaxis:
SELECT column1, column2, ...
FROM table1 outer
WHERE column1 operator
(SELECT column1, column2
FROM table2
WHERE expr1 = outer.expr2);
Oracle Server realiza una consulta correlacionada cuando la subconsulta hace referencia a una columna desde una tabla de la consulta principal.
Nota: Puede utilizar los operadores ANY y ALL en una subconsulta correlacionada.
Ejemplo:
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary >
(SELECT AVG(salary)
FROM employees
WHERE department_id = outer.department_id);
/*El ejemplo determina qué empleados ganan más que el salario medio de su departamento. En
este caso, la subconsulta correlacionada calcula
específicamente el salario medio de cada departamento.
Como la consulta externa y la interna utilizan la tabla EMPLOYEES en la cláusula FROM, se asigna un alias a EMPLOYEES en la sentencia SELECT externa por motivos de claridad. El alias no sólo hace que toda la sentencia SELECT resulte más legible, sino que sin él, la consulta no funcionaría correctamente, ya que la sentencia interna no podría distinguir la columna de la tabla interna de la columna de la tabla externa.*/
Como la consulta externa y la interna utilizan la tabla EMPLOYEES en la cláusula FROM, se asigna un alias a EMPLOYEES en la sentencia SELECT externa por motivos de claridad. El alias no sólo hace que toda la sentencia SELECT resulte más legible, sino que sin él, la consulta no funcionaría correctamente, ya que la sentencia interna no podría distinguir la columna de la tabla interna de la columna de la tabla externa.*/
---
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <=
(SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);
/*El ejemplo muestra los detalles de los empleados que han cambiado de puesto al menos dos veces. Oracle Server evalúa una subconsulta correlacionada de esta manera:
1. Selecciona una fila de la tabla especificada en la consulta externa. Ésta será la fila candidata actual.
2. Almacena el valor de la columna a la que se hace referencia en la subconsulta desde esta fila candidata. (En el ejemplo, la columna a la que se hace referencia en la subconsulta es E.EMPLOYEE_ID).
3. Realiza la subconsulta con su condición haciendo referencia al valor de la fila candidata de la consulta externa. (La función de grupo COUNT(*) se evalúa basándose en el valor de la columna E.EMPLOYEE_ID obtenida en el paso 2).
4. Evalúa la cláusula WHERE de la consulta externa basándose en los resultados de la subconsulta realizada en el paso 3. Esto determina si se selecciona la fila candidata para la salida. (En el ejemplo, el número de veces que un empleado ha cambiado de puesto, evaluado por la subconsulta, se compara con 2 en la cláusula WHERE de la consulta externa. Si se satisface la condición, se muestra el registro de ese empleado).
5. Repite el procedimiento para la siguiente fila candidata de la tabla, y así sucesivamente hasta que se hayan procesado todas las filas de la tabla.
La correlación se establece mediante un elemento de la consulta externa en la subconsulta. En este ejemplo, se compara EMPLOYEE_ID de la tabla de la subconsulta con el valor de EMPLOYEE_ID de la tabla de la consulta externa.*/
_____________________________________________________________________________________
Operador EXISTS.
Con las sentencias SELECT anidadas, son válidos todos los operadores lógicos. Además, puede utilizar el operador EXISTS. Este operador se utiliza frecuentemente con subconsultas correlacionadas para probar si existe un valor recuperado por la consulta externa en el juego de resultados de los valores recuperados por la consulta interna. Si la subconsulta devuelve al menos una fila, el operador devuelve TRUE. Si el valor no existe, devuelve FALSE. De forma análoga, NOT EXISTS prueba si un valor recuperado por la consulta externa no forma parte del juego de resultados de los valores recuperados por la consulta interna.
•El operador EXISTS comprueba la existencia de filas en el juego de resultados de la subconsulta.
•Si se encuentra un valor de fila de subconsulta:
–La búsqueda no continúa en la consulta interna.
–La condición se indica como TRUE.
•Si no se encuentra un valor de fila de subconsulta:
–La condición se indica como FALSE.
–La búsqueda continúa en la consulta interna.
FROM employees e
WHERE 2 <=
(SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);
/*El ejemplo muestra los detalles de los empleados que han cambiado de puesto al menos dos veces. Oracle Server evalúa una subconsulta correlacionada de esta manera:
1. Selecciona una fila de la tabla especificada en la consulta externa. Ésta será la fila candidata actual.
2. Almacena el valor de la columna a la que se hace referencia en la subconsulta desde esta fila candidata. (En el ejemplo, la columna a la que se hace referencia en la subconsulta es E.EMPLOYEE_ID).
3. Realiza la subconsulta con su condición haciendo referencia al valor de la fila candidata de la consulta externa. (La función de grupo COUNT(*) se evalúa basándose en el valor de la columna E.EMPLOYEE_ID obtenida en el paso 2).
4. Evalúa la cláusula WHERE de la consulta externa basándose en los resultados de la subconsulta realizada en el paso 3. Esto determina si se selecciona la fila candidata para la salida. (En el ejemplo, el número de veces que un empleado ha cambiado de puesto, evaluado por la subconsulta, se compara con 2 en la cláusula WHERE de la consulta externa. Si se satisface la condición, se muestra el registro de ese empleado).
5. Repite el procedimiento para la siguiente fila candidata de la tabla, y así sucesivamente hasta que se hayan procesado todas las filas de la tabla.
La correlación se establece mediante un elemento de la consulta externa en la subconsulta. En este ejemplo, se compara EMPLOYEE_ID de la tabla de la subconsulta con el valor de EMPLOYEE_ID de la tabla de la consulta externa.*/
_____________________________________________________________________________________
Operador EXISTS.
Con las sentencias SELECT anidadas, son válidos todos los operadores lógicos. Además, puede utilizar el operador EXISTS. Este operador se utiliza frecuentemente con subconsultas correlacionadas para probar si existe un valor recuperado por la consulta externa en el juego de resultados de los valores recuperados por la consulta interna. Si la subconsulta devuelve al menos una fila, el operador devuelve TRUE. Si el valor no existe, devuelve FALSE. De forma análoga, NOT EXISTS prueba si un valor recuperado por la consulta externa no forma parte del juego de resultados de los valores recuperados por la consulta interna.
•El operador EXISTS comprueba la existencia de filas en el juego de resultados de la subconsulta.
•Si se encuentra un valor de fila de subconsulta:
–La búsqueda no continúa en la consulta interna.
–La condición se indica como TRUE.
•Si no se encuentra un valor de fila de subconsulta:
–La condición se indica como FALSE.
–La búsqueda continúa en la consulta interna.
Ejemplos:
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS (SELECT 'X'
FROM employees
WHERE manager_id = outer.employee_id);
/*Este ejemplo retorna todos los empleados que tienen al menos un empleado que se reporte a él. Osea muestra los empleados que son supervisores.*/
---
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id = d.department_id);
/*Este ejemplo muestra los departamentos que no tienen empleados.*/
---
Solución Alternativa:Se puede utilizar una construcción NOT IN como alternativa para un operador NOT EXISTS, como se muestra en el siguiente ejemplo:
SELECT department_id, department_name
FROM departments
WHERE department_id NOT IN
(SELECT department_id
FROM employees);
En el caso de la sentencia UPDATE, puede utilizar una subconsulta correlacionada para actualizar filas de una tabla basada en filas de otra tabla.
Sintaxis:
UPDATE table1 alias1
SET column =
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
Ejemplo:
DROP TABLE empleados;
/*Como ya tenia esta tabla creada, la elimino. Si no la tienes, omite este paso.*/
---
CREATE TABLE empleados
AS
SELECT *
FROM employees;
/*Creo la tabla como una copia de la tabla employees.*/
---
ALTER TABLE empleados
ADD nombre_departamento VARCHAR2(30);
/*Ahora desnormalizo la tabla empleados agregando la columna nombre de departamento.*/
---
UPDATE empleados e
SET e.nombre_departamento =
(SELECT department_name
FROM departments
WHERE department_id = e.department_id);
/*Actualizamos la columna nombre_departamento en la tabla empleados, para ello usamos una subconsulta correlacionada que lee de la tabla departments.*
---
Consulta Correlacionada: DELETE.
En el caso de una sentencia DELETE, puede utilizar una subconsulta correlacionada para suprimir sólo las filas que también existen en otra tabla.
Sintaxis:
DELETE FROM table1 alias1
WHERE column operator
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
Ejemplo:
DELETE FROM empleados e
WHERE EXISTS
(SELECT 'A'
FROM departments
WHERE manager_id IN (145, 121)
AND department_name = e.nombre_departamento);
/*Aquí usamos una subconsulta correlacionada para eliminar los empleados que tengan un nombre de departamento supervisado por los managers 145 o 121.*/
_____________________________________________________________________________________
Cláusula WITH.
Mediante la cláusula WITH, puede definir un bloque de consulta antes de utilizarlo en una consulta. La cláusula WITH (conocida formalmente como subquery_factoring_clause) le permite reutilizar el mismo bloque de consulta en una sentencia SELECT cuando se produce más de una vez dentro de una consulta compleja. Esto resulta particularmente útil cuando una consulta tiene muchas referencias al mismo bloque de consulta y hay presentes uniones y agregaciones.
Mediante la cláusula WITH, puede reutilizar la misma consulta cuando resulta caro evaluar el bloque de consulta y se produce más de una vez dentro de una consulta compleja. Mediante la cláusula WITH, Oracle Server recupera los resultados de un bloque de consulta y los almacena en el tablespace temporal del usuario. Esto puede mejorar el rendimiento.
Ventajas de la Cláusula WITH:
•Hace que la consulta resulte fácil de leer.
•Evalúa una cláusula sólo una vez, incluso aunque aparezca varias veces en la consulta.
•En la mayoría de los casos puede mejorar el rendimiento para las consultas grandes.
Notas de Uso de la Cláusula WITH:
•Se utiliza sólo con sentencias SELECT.
•Un nombre de consulta es visible para todos los bloques de consulta del elemento WITH (incluidos los bloques de subconsulta) definidos después de éste y el propio bloque de consulta (incluidos los bloques de subconsulta).
•Cuando el nombre de consulta es igual que un nombre de tabla existente, el analizador busca de dentro a fuera y el nombre de bloque de consulta tiene prioridad sobre el nombre de tabla.
•La cláusula WITH puede contener más de una consulta. Cada consulta se separa entonces con una coma.
Ejemplos:
WITH not_manager
AS
(
SELECT
e.department_id AS codigo_departamento,
e.first_name||' '||e.last_name AS nombre,
e.salary AS salario
FROM employees e
WHERE NOT EXISTS
(
SELECT 'A'
FROM employees
WHERE manager_id = e.employee_id
)
),
promedio_departamento
AS
(
SELECT
department_id AS codigo_departamento,
AVG(salary) AS promedio
FROM employees
GROUP BY department_id
)
SELECT *
FROM not_manager nm
WHERE salario >
(SELECT promedio
FROM promedio_departamento
WHERE codigo_departamento = nm.codigo_departamento);
/*El pasado ejemplo muestra los empleados que no son supervisores y que ganan mas que el salario promedio de su departamento. Existen otras formas de realizar la misma consulta, pero el uso de la Cláusula WITH supone un mejor rendimiento, especialmente para consultas complejas que se usan con frecuencia.*/
---
WITH buenos_emps
AS
(
SELECT
e.last_name AS nombre,
d.department_name AS departamento,
j.job_title AS puesto,
e.salary AS salario
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND e.salary >
(
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
)
),
salario_medio
AS
(
SELECT
job_title AS puesto,
min_salary AS limite_inferior,
max_salary AS limite_superior,
max_salary-min_salary AS media
FROM jobs
)
SELECT b.*
FROM buenos_emps b, salario_medio s
WHERE b.puesto = s.puesto
AND salario = limite_superior;
/*Este ejemplo muestra los empleados que tiene un salario major al salario promedio de su departamento y que a su vez su salario es igual al salario máximo para el tipo de puesto que desempeñan en la empresa.*/
Mediante la cláusula WITH, puede definir un bloque de consulta antes de utilizarlo en una consulta. La cláusula WITH (conocida formalmente como subquery_factoring_clause) le permite reutilizar el mismo bloque de consulta en una sentencia SELECT cuando se produce más de una vez dentro de una consulta compleja. Esto resulta particularmente útil cuando una consulta tiene muchas referencias al mismo bloque de consulta y hay presentes uniones y agregaciones.
Mediante la cláusula WITH, puede reutilizar la misma consulta cuando resulta caro evaluar el bloque de consulta y se produce más de una vez dentro de una consulta compleja. Mediante la cláusula WITH, Oracle Server recupera los resultados de un bloque de consulta y los almacena en el tablespace temporal del usuario. Esto puede mejorar el rendimiento.
Ventajas de la Cláusula WITH:
•Hace que la consulta resulte fácil de leer.
•Evalúa una cláusula sólo una vez, incluso aunque aparezca varias veces en la consulta.
•En la mayoría de los casos puede mejorar el rendimiento para las consultas grandes.
Notas de Uso de la Cláusula WITH:
•Se utiliza sólo con sentencias SELECT.
•Un nombre de consulta es visible para todos los bloques de consulta del elemento WITH (incluidos los bloques de subconsulta) definidos después de éste y el propio bloque de consulta (incluidos los bloques de subconsulta).
•Cuando el nombre de consulta es igual que un nombre de tabla existente, el analizador busca de dentro a fuera y el nombre de bloque de consulta tiene prioridad sobre el nombre de tabla.
•La cláusula WITH puede contener más de una consulta. Cada consulta se separa entonces con una coma.
Ejemplos:
WITH not_manager
AS
(
SELECT
e.department_id AS codigo_departamento,
e.first_name||' '||e.last_name AS nombre,
e.salary AS salario
FROM employees e
WHERE NOT EXISTS
(
SELECT 'A'
FROM employees
WHERE manager_id = e.employee_id
)
),
promedio_departamento
AS
(
SELECT
department_id AS codigo_departamento,
AVG(salary) AS promedio
FROM employees
GROUP BY department_id
)
SELECT *
FROM not_manager nm
WHERE salario >
(SELECT promedio
FROM promedio_departamento
WHERE codigo_departamento = nm.codigo_departamento);
/*El pasado ejemplo muestra los empleados que no son supervisores y que ganan mas que el salario promedio de su departamento. Existen otras formas de realizar la misma consulta, pero el uso de la Cláusula WITH supone un mejor rendimiento, especialmente para consultas complejas que se usan con frecuencia.*/
---
WITH buenos_emps
AS
(
SELECT
e.last_name AS nombre,
d.department_name AS departamento,
j.job_title AS puesto,
e.salary AS salario
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND e.salary >
(
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
)
),
salario_medio
AS
(
SELECT
job_title AS puesto,
min_salary AS limite_inferior,
max_salary AS limite_superior,
max_salary-min_salary AS media
FROM jobs
)
SELECT b.*
FROM buenos_emps b, salario_medio s
WHERE b.puesto = s.puesto
AND salario = limite_superior;
/*Este ejemplo muestra los empleados que tiene un salario major al salario promedio de su departamento y que a su vez su salario es igual al salario máximo para el tipo de puesto que desempeñan en la empresa.*/
_____________________________________________________________________________________
_____________________________________________________________________________________
