II. LENGUAJE DE CONSULTAS
8. Subconsultas
Una subconsulta es una instrucción SELECT anidada dentro de una instrucción SELECT, SELECT...INTO,
INSERTINTO, DELETE, o UPDATE o dentro de otra subconsulta. Se pueden utilizar tres formas de sintaxis para crear una subconsulta:
comparación [ANY | ALL | SOME] (instrucción_sql) expresión [NOT] IN (instrucción_sql)
[NOT] EXISTS (instrucción_sql)
En donde: comparación es una expresión seguida de un operador de comparación que compara la expresión con el resultado de la subconsulta; expresión es una expresión por la que se busca el conjunto resultante de la subconsulta; instrucción_sql es una instrucción SELECT, que sigue el mismo formato y reglas que cualquier otra instrucción SELECT.
Se puede utilizar una subconsulta en lugar de una expresión en la lista de campos de una instrucción SELECT, como un origen de registros en la cláusula FROM, o en una cláusula WHERE o HAVING. En una subconsulta, se utiliza una instrucción SELECT para proporcionar un conjunto de uno o más valores especificados para evaluar en la expresión de la cláusula WHERE o HAVING.
Se puede utilizar el predicado ANY o SOME, los cuales son sinónimos, para recuperar registros de la consulta principal, que satisfagan la comparación con cualquier otro registro recuperado en la subconsulta.
Por ejemplo, la siguiente consulta devuelve los datos de matrícula de aquellos alumnos que no tienen faltas en el módulo 1:
SELECT *
FROM Matricula
WHERE idMatricula <> ALL (SELECT DISTINCT idMatricula FROM Falta WHERE idModulo = 1);
La subconsulta devuelve la lista de matrículas que tiene alguna falta en el Módulo 1. El predicado ALL se utiliza para recuperar únicamente aquellos registros de la consulta principal que satisfacen la comparación con todos los registros recuperados en la subconsulta. En este caso, el id de Matricula no debe coincidir con ninguno de la lista establecida por la subconsulta.
Si queremos obtener los alumnos que han aprobado el módulo 1 podemos plantear la siguiente consulta:
SELECT * FROM Alumno
WHERE nif = ANY (SELECT nif FROM Nota WHERE idModulo = 1 AND valor>=5);
La subconsulta devuelve la lista de nif's de alumnos que han aprobado el módulo 1. En este caso basta con que el nif del Alumno sea alguno de los que están en la lista establecida por la subconsulta.
8.2. Recuperación de datos con subconsulta
Si una subconsulta retorna un único registro con una sola columna, el valor de la columna puede ser tratado como si fuese un literal. Por ejemplo, la siguiente consulta recupera las matrículas del alumno José Pérez:
SELECT idMatricula, año FROM MATRICULA
WHERE nif = (SELECT nif FROM ALUMNO WHERE nombre='José' AND apellidos="Pérez")
En esta consulta se compara el nif de cada registro de MATRICULA con el valor del nif del alumno José Pérez, obtenido mediante una subconsulta. Si existe un único alumno llamado José Pérez la subconsulta devuelve una única fila con un único valor. Pero si existen varios alumnos con ese mismo nombre, la subconsulta retornará varios registros y se producirá un error del tipo siguiente:
ERROR en línea 3:
Si nos solicitan los apellidos de alumnos que tienen faltas en el módulo 3, podemos resolverlo con la siguiente consulta:
SELECT DISTINCT Alumno.apellidos
FROM Alumno JOIN Matricula USING(nif) JOIN Falta USING (idMatricula) WHERE Falta.idModulo = 3;
Pero también podríamos resolverlo utilizando subconsultas:
SELECT Alumno.apellidos FROM Alumno
WHERE nif IN (SELECT DISTINCT nif FROM Matricula
WHERE Matricula.id IN ( SELECT DISTINCT idMatricula FROM Falta WHERE idModulo = 3 ));
En este caso las subconsultas se utilizan mediante una condición IN. Para evaluar la consulta completa, el sistema evalúa primero las subconsultas anidadas, desde la más interior a la más exterior. La subconsulta más interior produce como resultado el conjunto de id's de matriculados que tienen faltas en el módulo de id igual a 3. Es como si el resultado de la subconsulta fuese una lista de id's. La siguiente subconsulta obtiene la lista de nif's correspondientes a las matrículas que tienen faltas. La consulta principal evalúa que nif's de alumnos pertenecen a dicha lista, y éstos son precisamente los que tienen una falta en el módulo 3.
Análogamente se puede utilizar NOT IN para recuperar únicamente aquellos registros de la consulta principal para los que no hay ningún registro de la subconsulta que contenga un valor igual.
8.3. Subconsultas correlacionadas.
Al utilizar subconsultas debe estudiarse si la búsqueda es correlacionada o no. Diremos que la búsqueda en subconsultas es correlacionada si el resultado de la subconsulta depende de un resultado de la consulta principal.
En el ejemplo de la sección anterior, el resultado de las subconsultas es independiente de la consulta principal (se obtienen id's de matricula que cumplen una condición particular independiente de los valores obtenidos en la consulta principal).
Un ejemplo de subconsulta correlacionada es el siguiente: obtener el id de matrículas pertenecientes a alumnos que tienen más de una falta.
SELECT DISTINCT F1.idMatricula FROM Falta F1
WHERE F1.idMatricula IN ( SELECT F2.idMatricula FROM Falta F2
WHERE NOT (F2.idMatricula=F1.idMatricula AND F2.fecha=F1.fecha) );
La estrategia en esta subconsulta es obtener el listado de registros de la tabla Falta excluyendo el registro evaluado en la consulta principal. Si este listado incluye un registro con el id evaluado en la consulta principal, quiere decir que el alumno correspondiente a ese id posee más de una falta.
En la consulta principal, por cada registro de F1 se ejecuta la subconsulta recorriendo los registros de F2 cada vez. Es decir, la subconsulta se ejecuta tantas veces como registros contenga la tabla Falta.
En las subconsultas no correlacionadas el número de accesos viene dado por la suma de los accesos en cada
SELECT. En las subconsultas correlacionadas el número de accesos es producto de la multiplicación, pues debe evaluarse la SELECT interior para cada registro de la SELECT exterior.
Por ejemplo, obtener el nif y los apellidos de alumnos que se hayan matriculado en más de un ciclo.
SELECT DISTINCT Alumno.nif , Alumno.apellidos FROM Alumno JOIN Matricula M1 USING (nif)
WHERE M1.nif IN ( SELECT M2.nifFROM Matricula M2WHERE M2.idMatricula<> M1.idMatricula ) 8.4. Subconsultas con operador de comparación distinto de «IN».
Podemos usar una subconsulta para obtener un único valor y entonces compararlo con los operadores de comparación (=, <>, > , >=, <, <=).
Por ejemplo, la siguiente consulta obtiene el nombre y apellidos de los alumnos que residen en la misma localidad que el alumno de nif '11111111A'.
SELECT nombre , apellidos FROM Alumno
WHERE localidad = ( SELECT localidad FROM Alumno WHERE nif = '11111111A' )
Si la subconsulta retornase más de un valor, entonces se produciría un error en la ejecución de la consulta externa.
8.5. Funciones de agregado en subconsultas.
Se suelen utilizar funciones de agregado en subconsultas para obtener un valor de grupo que podamos comparar con valores individuales en cada registro.
En este ejemplo, se obtiene el nif de los alumnos que tienen una nota en el módulo 4 superior a su nota media. El cálculo de la nota media se realiza mediante la subconsulta.
SELECT n1.nif FROM Nota n1
WHERE n1.idModulo = 4
AND n1.valor > ( SELECT AVG(*) FROM Nota n2WHERE n1.nif = n2.nif );
En este ejemplo, se obtienen los nif's de alumnos matriculados en 2004 cuya nota media es superior a la nota media de todos los alumnos.
SELECT A.nif
FROM Matricula M JOIN Alumno AON M.nif=A.nif JOIN Nota N ON A.nif=N.nif WHERE M.año = 2004
GROUP BY A.nif
HAVING AVG (valor) > ( SELECT AVG(valor)FROM Nota ); 8.6. Subconsultas con «EXISTS».
EXISTS es el cuantificador existencial y puede aplicarse sobre subconsultas. La expresión «EXISTS (SELECT … FROM …)» da como resultado un valor verdadero si y solo si el resultado de evaluar la subconsulta no es el conjunto vacío; en otras palabras, si existe al menos un registro en el resultado.
Por ejemplo, la siguiente consulta obtiene el id de matrícula de alumnos del curso 2004-05 que tienen alguna falta en el módulo 5.
SELECT idMatricula FROM Matricula WHERE año = 2004
AND EXISTS ( SELECT 1 FROM Falta WHERE Falta.idMatricula AND Falta.idModulo=5 )
Nota. En una subconsulta evaluada mediante el operador EXISTS la lista de columnas que devuelve el comando SELECT carece de importancia. Por ello es habitual que este tipo de consultas retornen una única columna con una expresión literal.
La forma negada, NOT EXISTS, es importante en cierto tipo de consultas complejas.
Como primer ejemplo obtendremos el id de matrícula de alumnos del curso 2004-05 que no tienen ninguna falta en los módulos 4 y 5.
SELECT idMatricula FROM Matricula WHERE año = 2004
AND NOT EXISTS ( SELECT 1 FROM Falta WHERE Falta.idMatricula AND Falta.idModulo IN (4, 5 ) )
El siguiente ejemplo es más complejo, y obtiene los id de matrícula de alumnos del curso 2004-05 que tienen faltas en todos los módulos.
SELECT Matricula.idMatricula FROM Matricula
WHERE Matricula.año = 2004
ANDNOT EXISTS ( SELECT 1FROM Modulo WHERE NOT EXISTS ( SELECT 1FROM Falta
WHERE Falta.idMatricula = Matricula.idMatricula AND Falta.idModulo = Modulo.idModulo )) ;
Para comprender la consulta, podríamos enunciarla como: «obtener los id de matricula de alumnos del curso 2004-05 que no tengan módulos en los que no tengan faltas».
8.7. Expresiones de columna con subconsultas
Es también posible usar una subconsulta en una de las expresiones de columna de una SELECT siempre y cuando la subconsulta retorne un único registro con un valor simple.
Un primer ejemplo sencillo es obtener los id's de matrícula, los apellidos de alumnos y el año de curso.
SELECT M.idMatricula, (SELECT apellidos FROM Alumno A WHERE A.nif=M.nif), M.año FROM Matricula M;
Como alternativa al uso de la cláusula GROUP BY, podemos obtener el nif de alumnos y el número de módulos que han cursado.
SELECT nif , (SELECT COUNT(*) FROM Nota WHERE Nota.nif=Alumno.nif) FROM Alumno;
8.8. Subconsultas como origen de registros para «FROM».
En la cláusula FROM se pueden usar como orígenes de datos: - Una tabla
- Una vista
- Una función que devuelva una tabla - Una subconsulta
Por ejemplo, podemos obtener en una subconsulta registros con el nif de un alumno, el nombre de un módulo y la nota. Podemos utilizar dicha subconsulta como origen de datos para obtener la media de notas por módulo.
SELECT nombre AS "Módulo" , AVG (valor) AS "Nota media"
FROM (SELECT N.nif, M.nombre, N.valor FROM Nota N JOIN Modulo M USING (idModulo)) GROUP BY nombre;
Algo más complejo es obtener un valor de nota (entre 1 y 10) y el número de alumnos que han conseguido dicha nota en el módulo 1. Para la consulta se debe forzar un registro por cada una de las notas, de forma que si ningún alumno obtuvo esa nota se muestre el valor 0.
Podemos obtener el número de alumnos por nota registrada en la tabla Nota:
SELECT valor, COUNT(*) FROM Nota
GROUP BY valor;
Pero esta consulta no garantiza que se genere necesariamente un registro por cada valor de nota posible (por ejemplo, si ningún alumno tiene la nota 7, no se generará un registro con la nota 7). Para forzar esto necesitamos una subconsulta con todos los valores de nota y forzar un JOIN externo:
SELECT valor, NVL(num, 0) FROM
(SELECT 1 valor FROM DUAL UNION SELECT 2 valor FROM DUAL UNION SELECT 3 valor FROM DUAL UNION SELECT 4 valor FROM DUAL UNION SELECT 5 valor FROM DUAL UNION SELECT 6 valor FROM DUAL UNION SELECT 7 valor FROM DUAL UNION SELECT 8 valor FROM DUAL UNION
SELECT 9 valor FROM DUAL UNION SELECT 10 valor FROM DUAL) T1 LEFT JOIN
(SELECT valor, COUNT(*) num FROM Nota GROUP BY valor) T2 ON T1.valor = T2.valor;
Aquellos valores de nota no incluidos en la tabla Nota no combinarán en el LEFT JOIN y por tanto producirán un registro con las columnas combinadas (valor y num) a valor NULL. Por ello se utiliza la función NVL(), que verifica si el campo num tiene valor NULL y en ese caso lo sustituye por un cero.