Subconsultas para resolver consultas

Una subconsulta se define mejor como una consulta dentro de una consulta. Las subconsultas le permiten escribir consultas que seleccionan filas de datos para criterios que se desarrollan realmente mientras la consulta se ejecuta en tiempo de ejecución. Más formalmente, es el uso de una instrucción SELECT dentro de una de las cláusulas de otra instrucción SELECT. De hecho, una subconsulta puede estar contenida dentro de otra subconsulta, que está dentro de otra subconsulta, y así sucesivamente. Una subconsulta también se puede anidar dentro de las instrucciones INSERT, UPDATE y DELETE. Las subconsultas deben ir entre paréntesis.

Una subconsulta se puede usar en cualquier lugar donde se permita una expresión siempre que devuelva un solo valor. Esto significa que una subconsulta que devuelve un solo valor también puede incluirse como un objeto en una lista de cláusulas FROM. Esto se denomina vista en línea porque cuando una subconsulta se usa como parte de una cláusula FROM, se trata como una tabla o vista virtual. La subconsulta se puede colocar en la cláusula FROM, cláusula WHERE o cláusula HAVING de la consulta principal.

Oracle permite un anidamiento máximo de 255 niveles de subconsultas en una cláusula WHERE. No hay límite para anidar subconsultas expresadas en una cláusula FROM. En la práctica, el límite de 255 niveles no es realmente un límite porque es raro encontrar subconsultas anidadas más allá de tres o cuatro niveles.

Una instrucción SELECT de subconsulta es muy similar a la instrucción SELECT utilizada para comenzar una consulta regular o externa. La sintaxis completa de una subconsulta es:

( SELECT [DISTINCT] subquery_select_parameter
  FROM {table_name | view_name}
               {table_name | view_name} ...
  [WHERE search_conditions]
  [GROUP BY column_name [,column_name ] ...]
  [HAVING search_conditions] )

Tipos de subconsultas

Single Row Sub Query: Subconsulta que devuelve un resultado de una sola fila. Marcan el uso de operadores de comparación de una sola fila, cuando se usan en condiciones WHERE.

Multiple row sub query: Subconsulta que devuelve resultados de varias filas. Hacen uso de varios operadores de comparación de filas como IN, ANY, ALL. También puede haber subconsultas que devuelvan múltiples columnas.

Correlated Sub Query: Las subconsultas correlacionadas dependen de los datos proporcionados por la consulta externa. Este tipo de subconsulta también incluye subconsultas que utilizan el operador EXISTS para probar la existencia de filas de datos que satisfacen criterios específicos.

Subconsulta de una sola fila

Se utiliza una subconsulta de una sola fila cuando los resultados de la consulta externa se basan en un único valor desconocido. Aunque este tipo de consulta se denomina formalmente "fila única", el nombre implica que la consulta devuelve varias columnas, pero solo una fila de resultados. Sin embargo, una subconsulta de una sola fila puede devolver solo una fila de resultados que consta de una sola columna para la consulta externa.

En la consulta SELECT a continuación, SQL interno devuelve solo una fila, es decir, el salario mínimo para la empresa. A su vez, utiliza este valor para comparar el salario de todos los empleados y muestra solo aquellos cuyo salario es igual al salario mínimo.

SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN (salary) 
		FROM employees);

Una cláusula HAVING se utiliza cuando los resultados de grupo de una consulta deben restringirse en función de alguna condición. Si el resultado de una subconsulta debe compararse con una función de grupo, debe anidar la consulta interna en la cláusula HAVING de la consulta externa.

SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary)  < (SELECT AVG (salary) FROM employees)

Subconsulta de varias filas

Las subconsultas de varias filas son consultas anidadas que pueden devolver más de una fila de resultados a la consulta principal. Las subconsultas de varias filas se utilizan con mayor frecuencia en las cláusulas WHERE y HAVING. Dado que devuelve múltiples filas, debe ser manejado por operadores de comparación de conjuntos (IN, ALL, ANY). Mientras que el operador IN tiene el mismo significado que se discutió en el capítulo anterior, CUALQUIER operador compara un valor especificado con cada valor devuelto por la subconsulta mientras TODO compara un valor con cada valor devuelto por una subconsulta.

La siguiente consulta muestra el error cuando la subconsulta de una sola fila devuelve varias filas.

SELECT	first_name, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE LOCATION_ID = 100)
department_id = (select
               *
ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

Uso de operadores de filas múltiples

[> TODOS] Más que el valor más alto devuelto por la subconsulta
[<TODOS] Menos que el valor más bajo devuelto por la subconsulta
[<CUALQUIER] Menos que el valor más alto devuelto por la subconsulta
[> CUALQUIERA] Más que el valor más bajo devuelto por la subconsulta
[= CUALQUIER] Igual a cualquier valor devuelto por la subconsulta (igual que IN)

El SQL anterior se puede reescribir usando el operador IN como se muestra a continuación.

SELECT	first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
                   	    FROM departments
                   	    WHERE LOCATION_ID = 100)

Tenga en cuenta que en la consulta anterior, IN coincide con los ID de departamento devueltos por la subconsulta, lo compara con el de la consulta principal y devuelve el nombre del empleado que cumple la condición.

Una combinación sería una mejor solución para la consulta anterior, pero con fines ilustrativos, se ha utilizado una subconsulta en ella.

Subconsulta correlacionada

A diferencia de una subconsulta normal, donde la consulta externa depende de los valores proporcionados por la consulta interna, una subconsulta correlacionada es aquella en la que la consulta interna depende de los valores proporcionados por la consulta externa. Esto significa que en una subconsulta correlacionada, la consulta interna se ejecuta repetidamente, una vez por cada fila que pueda ser seleccionada por la consulta externa.

Las subconsultas correlacionadas pueden producir tablas de resultados que responden a preguntas de gestión complejas.

Considere la siguiente consulta SELECT. A diferencia de las subconsultas consideradas anteriormente, la subconsulta en esta instrucción SELECT no se puede resolver independientemente de la consulta principal. Observe que la consulta externa especifica que las filas se seleccionan de la tabla de empleados con un alias de e1. La consulta interna compara la columna del número de departamento del empleado (DepartmentNumber) de la tabla de empleados con el alias e2 con la misma columna para el nombre de la tabla de alias e1.

SELECT EMPLOYEE_ID, salary, department_id
FROM   employees E
WHERE salary > (SELECT AVG(salary)
                FROM   EMP T
                WHERE E.department_id = T.department_id)

Subconsulta de varias columnas

Una subconsulta de varias columnas devuelve más de una columna a la consulta externa y se puede enumerar en la cláusula FROM, WHERE o HAVING de la consulta externa. Por ejemplo, la consulta siguiente muestra los detalles históricos del empleado para aquellos cuyo salario actual está en el rango de 1000 y 2000 y trabajan en el departamento 10 o 20.

SELECT first_name, job_id, salary
FROM emp_history
WHERE (salary, department_id) in (SELECT salary, department_id
				  FROM employees
 				  WHERE salary BETWEEN 1000 and 2000 
				  AND department_id BETWEEN 10 and 20)
ORDER BY first_name;

Cuando se utiliza una subconsulta de varias columnas en la cláusula FROM de la consulta externa, crea una tabla temporal a la que se puede hacer referencia con otras cláusulas de la consulta externa. Esta tabla temporal se denomina más formalmente vista en línea. Los resultados de la subconsulta se tratan como cualquier otra tabla en la cláusula FROM. Si la tabla temporal contiene datos agrupados, los subconjuntos agrupados se tratan como filas separadas de datos en una tabla. Considere la cláusula FROM en la consulta siguiente. La vista en línea formada por la subconsulta es la fuente de datos para la consulta principal.

SELECT * 
FROM (SELECT salary, department_id
	FROM employees
 	WHERE salary BETWEEN 1000 and 2000);

↰ Previous page Next page ↱