DQL (Subconsultas)

Tema	Título	Subtema	Versión
04	*El modelo físco: SQL*	*PARTE III*	v 1.0
04	DQL Subconsultas

Subconsultas

Las subconsultas son sentencias SELECT que se utilizan dentro de otra sentencia SELECT, a la que llamaremos consulta principal.

Ejemplo introductorio:

Si queremos saber la edad máxima de la tabla alumnos haríamos:

SELECT max(edad) FROM alumnos;

Pero... ¿y si queremos saber el nombre de ese alumno (el que tiene la máxima edad)?

Esto estaría mal:

select nombre, max(edad) from alumnos;

Estaría mal porque la función max() no se puede usar en la cláusula SELECT para obtener el nombre de un alumno.

Si ampliamos el ejemplo anterior

SELECT nombre, max(edad), min(edad), avg(edad)
   FROM alumnos

¿Qué nombre de alumno debe mostrar? ¿El de la máxima edad, el de la mínima o el de la media?...

Solución:

SELECT nombre
    FROM alumnos
    WHERE edad = (SELECT max(edad) FROM alumnos);

Lo que tenemos entre paréntesis es una subconsulta. La subconsulta se ejecuta primero y su resultado se usa en la consulta principal.

Si queremos saber también la edad del alumno de máxima edad:

   SELECT nombre, edad
      FROM alumnos
      WHERE edad = (SELECT max(edad) FROM alumnos);

Ojo también porque esta consulta puede devolver más de un resultado si hay varios alumnos con la misma edad máxima.

Subconsultas

SQL permite formar sentencias SELECT combinando cualquiera de las formas que hemos visto hasta ahora. Es decir: tanto la consulta principal como la subconsulta pueden ser multi-tabla, con union, group by, etc. Una consulta puede tener distintas subconsultas. Incluso una subconsulta puede tener otras subconsultas. Las sentencias UPDATE y DELETE también pueden tener subconsultas en WHERE. También pueden ir subconsultas en la cláusula FROM y en la SELECT, pero no son tan frecuentes y no lo veremos. Además, en la condición donde ponemos la subselect, en lugar del operador relacional =, también podemos poner otras: <, >, !=, ...

Ejemplos

Ejemplo 1: las tabla principal y la de la subconsulta son la misma

Nombre de alumnos de 1º mayores que todos los alumnos de 2º:

SELECT nombre
FROM alumnos
WHERE curso = 1
        AND edad > (SELECT max(edad) FROM alumnos WHERE curso = 2);

Ejemplo 2: las tablas son distintas

Nombre de alumnos mayores que todos los profesores:

SELECT nombre
FROM alumnos
WHERE edad > (SELECT max(edad) FROM profesores);

Ejemplo 3: Ejemplo con varias subconsultas

Nombres del alumno más mayor y el del más menor:

SELECT nombre
FROM alumnos
WHERE edad = (SELECT max(edad) FROM alumnos)
        OR edad = (SELECT min(edad) FROM alumnos);

Nota

si hay 2 o más personas de mayor edad, saldrán todos ellos. Igual con los menores.

Enlace entre la consulta principal y la subconsulta <!--4.6.10.2>

En algunas consultas puede ser necesario que la subconsulta esté relacionada con la consulta principal. Es decir, que la subconsulta dependa de los valores de la consulta principal. Por ejeemplo:

Nombre, curso y edad del alumno mayor de cada curso.

Ahora si nos detenemos a pensar, la subconsulta no puede ser la misma para todos los alumnos, ya que cada alumno pertenece a un curso distinto. Por tanto, la subconsulta debe depender del curso del alumno de la consulta principal.

Con la solución del ejercicio anterior, para obtener el alumno de mayor edad, teníamos:

SELECT nombre
   FROM alumnos
   WHERE edad = (SELECT max(edad) FROM alumnos);

Pero ahora no queremos comparar la edad de cada alumno con la máxima edad de todos los alumnos, sino que, de cada alumno querremos comparar su edad con todos los alumnos DEL MISMO CURSO DEL ALUMNO QUE ESTAMOS INTENTANDO MOSTRAR (en la consulta principal):

Es decir: en la subconsulta debemos enlazar la tabla de la subconsulta con la tabla de la consulta principal. Y, como en este caso es la misma tabla, necesitamos hacer uso del sobrenombre de las tablas.

Las subconsultas que habíamos visto hasta ahora sacaban el mismo resultado para cada fila seleccionada en la consulta principal. Pero en este caso, la subconsulta se ejecuta para cada fila de la consulta principal, por lo que;

El alumno A se mostrará si su edad es la máxima de los alumnos del curso del alumno A.
El alumno B se mostrará si su edad es la máxima de los alumnos del curso del alumno B.
...

Ahora hemos visto que la subconsulta puede tomar el valor de la consulta principal. Por tanto, la subconsulta puede sacar un resultado diferente para cada fila de la consulta principal.

Tiempo de respuesta de las consultas con subconsultas

Las subconsultas que dependen de la consulta principal pueden ser muy lentas, ya que se ejecutan tantas veces como filas de la consulta principal. Por tanto, si tenemos muchas filas en la consulta principal, puede ser muy lento. En estos casos, es mejor usar un JOIN o una subconsulta en la cláusula FROM.

Ejercicios subconsultas (BD liga1213)

Se ha intentado clasificar los ejercicios de subconsultas por los tipos de soluciones de cada uno de ellos (conforme avanzamos en los apuntes, habrá más tipos de subconsultas). Ahora bien, dado que un ejercicio puede tener muchas soluciones, quizás no estén bien clasificados según la solución aportada por el alumno.

En este apartado son consultas donde la subconsulta sólo devuelve un único valor (una sola fila).

Ejercicio 1

Dorsal, equipo y goles del pichichi (el que ha marcado más goles).

Ejercicio 2

Nombre del pichichi

Ejercicio 3

Muestra el nombre y sueldo del jugador mejor pagado de toda la liga.

Ejercicio 4

Muestra el nombre y sueldo del jugador mejor pagado de cada equipo.

Ejercicio 5

Jugador que más cobra en cada equipo dentro de su categoría (lugar). Se debe mostrar el nombre del equipo, el nombre del jugador, el lugar y el sueldo (expresado en millones de euros, con 1 decimal). Ordenado por equipo y puesto.

Ejercicio 6

Muestra todos los datos de los partidos donde más goles se marcaron de todo el campeonato.

Ejercicio 7

Muestra todos los datos de los partidos donde más goles se marcaron de cada jornada. Ordenado por la jornada.

Ejercicio 8

Nombres de los jugadores de los equipos del partido donde más goles se marcaron. Muestra también el código de sus equipos. Ordenado por equipo y nombre de jugador.

Ejercicio 9

Jornadas en las que se marcaron más goles que la jornada anterior.

Ejercicio 10

Nombre largo de equipos que tienen más de 2 porteros, más de 2 defensas, más de 2 medios y más de 2 delanteros.

Conjunto de resultados de una subconsulta. Uso de ALL, ANY e IN.

En las subselectes que hemos visto sólo devolvían un único valor. Pero sabemos que una SELECT y, por tanto, una subconsulta, pueden dar como resultado un conjunto de filas y de columnas.

Condiciones que deben cumplir las columnas de la subconsulta:

Solo podremos poner una única columna. Es decir, sólo puede aparecer un campo en la cláusula SELECT. No podemos hacer: ...
```
WHERE a = (SELECT~~b, c, d~~FROM...)
```
(Como excepción está el operador EXISTS, que veremos más adelante).
Debemos tener en cuenta que lo que ponemos en la condición que enlaza la consulta principal con la subconsulta, debe poderse comparar:
- deben tener un tipo “similar” (ambos varchar aunque sean de distinta capacidad; o bien un entero con un float, etc)
- deben tener el mismo contenido semántico: no podemos comparar un código de cliente con la edad de una persona, por ejemplo.
Condiciones que deben cumplir las filas de la subconsulta:
- Si, como en los ejemplos anteriores, la subselect sólo va a devolver una fila, no hay problema. Podemos usar de enlace (entre la consulta principal y la subselect) cualquier operador relacional: =, !=, <, > ...
- Pero si la subselect puede devolver más de una fila, estaremos obligados a usar, junto con los operadores relacionales (=, >=, >, <=, <, <>), los operadores ANY (alguno) o ALL (todos) , ya que queremos comparar un valor de la consulta principal con ALGUNO valor devuelto por la subselect o con TODOS los valores devueltos por la subselect. Como veremos después, en lugar de “= ANY” también podemos usar “IN”; y, en lugar de “!=ALL” también podemos usar “NOT IN”.

Ejemplo ANY

Queremos obtener los nombres de los alumnos que son mayores que algún profesor:

SELECT nombre
    FROM alumnos
    WHERE edad > ANY (SELECT edad FROM profesores)

Ejemplo ALL

Queremos obtener nombres de alumnos que son mayores que todos los profesores:

SELECT nombre
    FROM alumnos
    WHERE edad > ALL (SELECT edad FROM profesores)

Equivalencias a expresiones con ANY y ALL:

Comparación	Subconsultas con:ANY o ALL	Subconsultas equivalentes con:max, min ; IN, NOT IN
Mayor que todos los de otra tabla	... WHERE edad > ALL (SELECT edad ...)	... WHERE edad > (SELECT max(edad) ...)
El mayor de una tabla	... WHERE edad >= ALL (SELECT edad ...)	... WHERE edad = (SELECT max(edad) ...)
No mayor	... WHERE edad < ANY (SELECT edad ...)	... WHERE edad < (SELECT max(edad) ...)
No menor	... WHERE edad > ANY (SELECT edad ...)	... WHERE edad > (SELECT min(edad) ...)
El menor de una tabla	... WHERE edad <= ALL (SELECT edad ...)	... WHERE edad = (SELECT min(edad) ...)
Menor que todos los de otra tabla	... WHERE edad < ALL (SELECT edad ...)	... WHERE edad < (SELECT min(edad) ...)
Igual a alguno	... WHERE edad = ANY (SELECT edad ...)	... WHERE edad IN (SELECT edad...)
Distinto de todos	... WHERE edad != ALL (SELECT edad ...)	... WHERE edad NOT IN (SELECT edad ...)

Cuidado con las subconsultas que devuelven más de una fila

Si la subconsulta devuelve más de una fila, debemos tener cuidado con el operador que usamos. Por ejemplo, si usamos =, la consulta fallará si la subconsulta devuelve más de un valor. En ese caso, debemos usar ANY o IN (según lo que queramos consultar).

Hay que recordar que esto estaría mal:

SELECT nombre
    FROM alumnos
    WHERE edad > (SELECT edad FROM profesores)

Porque estamos comparando la edad de 1 alumno con MUCHAS edades. Habría que poner ALL o ANY justo delante de la subconsulta (según qué pretendemos consultar).

SELECT nombre
    FROM alumnos
    WHERE edad > ANY (SELECT edad FROM profesores)

Equivalencias de subconsultas con multi-tabla.

En SQLal igual que en la mayoría de los lenguajes de programación no hay una única forma de expresar una consulta. Por tanto, las subconsultas que hemos visto hasta ahora también se pueden expresar como consultas multi-tabla aunque no siempre es posible. En este apartado veremos ejemplos de subconsultas que se pueden expresar como consultas multi-tabla y otras que no.

Esquema relacional que utilizaremos

ALUMNOS = codigo+ nombre
MATRÍCULAS = alumno + asig + nota - C. Aj: alumno --> ALUMNOS(codigo)

	Con subconsulta (tipo IN)	Con multi-tabla
Alumnos matriculados en BD	SELECT nombre FROM alumnos WHERE código IN (SELECT alumno FROM matriculas WHERE asig = 'BD');	SELECT nombre FROM alumnos, matrículas WHERE alumnos.código = matriculas.alumno AND asig = 'BD';
Alumnos NO matriculados de BD	SELECT nombre FROM alumnos WHERE código NOT IN (SELECT alumno FROM matriculas WHERE asig = 'BD');	Este caso no tiene una consulta multi-tabla equivalente.

Para el segundo caso, que no hay solución con multi-tabla, podemos pensar que el siguiente ejemplo sería correcto:

   SELECT nombre
      FROM alumnos, matrículas
      WHERE alumnos.código = matriculas.alumno
         AND asig != 'BD';

No va a dar error pero no es lo que queremos mostrar. Esta consulta mostrará a los alumnos matriculados de alguna asignatura que no sea BD.

Consulta multi-tabla equivalente a subconsulta tipo IN

las consultas con subconsultas tipo IN (o bien “= ANY”) podrían resolverse también como consulta multi-tabla. Pero no las de NOT IN.

Ejercicios de subconsultas

Sencillos:

Ejercicio 1

Nombre del equipo con más presupuesto (de 2 formas: usando max y usando ALL)

Ejercicio 2

Nombre de los equipos que no tienen el mayor presupuesto (de 2 formas: usando max y usando ANY)

Ejercicio 3

Nombres de ciudades que no tienen equipo (de 2 formas: usando ALL y usando in).

Más complicados:

Ejercicio 4

Muestra los nombres de los jugadores que cobran más que todo otro equipo entero.

Ejercicio 5

Nombre de los jugadores que han marcado más goles que otro equipo entero.

Ejercicio 6

Nombre de los jugadores que han marcado más goles que otro equipo entero. También debe aparecer el código del equipo del goleador y el código del equipo al que supera. Ordenado por el equipo del goleador, nombre del goleador y equipo al que supera.

Ejercicio 7

Al igual que el ejercicio anterior pero también debe aparecer los goles del goleador y los goles del equipo con el que se compara.

Ejercicio 8

Jugadores (equipo y nombre) que todavía no han marcado ningún gol. Ordenado por equipo y nombre.

Ejercicio 9

Equipo con más jugadores.

Ejercicio 10

Equipo con más jugadores y cantidad de jugadores.

Ejercicio 11

Equipo con más jugadores y equipo con menos jugadores. También debe aparecer las cantidades y una palabreja al lado que diga “MAX” o “MIN”.

El operador `EXISTS`

Ejemplo

Queremos mostrar a los alumnos mayores que algún profesor. Varias formas de hacerlo:

SELECT nombre FROM alumnos WHERE edad > ANY (SELECT edad FROM profesores);

SELECT nombre FROM alumnos WHERE edad > (SELECT min(edad) FROM profesores);   

SELECT DISTINCT alumnos.nombre
    FROM alumnos, profesores
    WHERE alumnos.edad > profesores.edad;

Hay que poner DISTINCT para no repetir nombres de alumnos (ya que un alumno le quitará tantas vueltas como la cantidad de profesores mayores que él hayan).

Pero a veces es más sencillo expresar el requerimiento así:

Mostraremos a un alumno si EXISTE un profesor más joven que ese alumno.

La consulta sería:

SELECT nombre
 FROM alumnos
    WHERE EXISTS (SELECT \*
                    FROM profesores
                    WHERE profesores.edad < alumnos.edad);

Vemos que el enlace entre la consulta principal y la subconsulta no se realiza en el WHERE de la consulta principal sino en el de la subconsulta. La subconsulta se ejecuta para cada fila de la consulta principal, por lo que;

El alumno A será mostrado si existe un profesor de menor edad que ese alumno A.
El alumno B será mostrado si existe un profesor de menor edad que ese alumno B
...

La expresión WHERE EXISTS (subconsulta) produce un resultado verdadero siempre que en la subconsulta se obtenga alguna fila, sin importar los campos seleccionados en la subconsulta (por eso he puesto * pero podría poner cualquier campo de la tabla profesores). Y producirá un resultado falso de lo contrario: si la subconsulta no obtiene ningún resultado.

Resumen

Al principio del presente apartado hemos visto que lo que hacemos con EXISTS lo podemos hacer con otro tipo de sentencias. Por tanto, podríamos vivir sin el EXISTS pero ya hemos dicho que es otra forma de plantear la resolución de las consultas. Además, en los siguientes apartados veremos el NOT EXISTS, que sí ayuda a resolver diferentes tipos de consultas.

Ejercicios subconsultas con EXISTS

Ejercicio 12

Muestra los nombres de los jugadores que cobran más que algún jugador de su equipo. Muestra el nombre de las ciudades que tengan algún equipo de fútbol. Hazlo al menos con 3 soluciones posibles:

exists
in
multi-tabla (composición)
multi-tabla (join)

Ejercicio 13

Muestra el nombre de las ciudades que no tengan ningún equipo de fútbol. Este ejercicio ya lo habíamos realizado con “not in” y con “!=all”. Hazlo ahora con el “not exists”.

Ejercicio 14

Nombres de equipos que no tienen ningún jugador con el dorsal 2.

Ahora veremos dos tipos de consultas para ver de qué formas suelen resolverse. Una forma será con el NOT EXISTS. Estas consultas son, leer con calma, los enunciados son parecidos pero no iguales.

ejercicio 15

Muestra a los alumnos tales que todas las asignaturas que se ha matriculado son de 1r curso

ejercicio 16

Muestra a los alumnos que se han matriculado de todas las asignaturas de 1r curso

DQL (Subconsultas)

Subconsultas

Ejemplos

Enlace entre la consulta principal y la subconsulta <!--4.6.10.2>

Ejercicios subconsultas (BD liga1213)

Conjunto de resultados de una subconsulta. Uso de ALL, ANY e IN.

Ejercicios de subconsultas

El operador EXISTS

Ejercicios subconsultas con EXISTS

El operador `EXISTS`