Subconsultas en SQL

Imagina que queremos encontrar alumnos que tengan la misma puntuación que un alumno concreto, p. ej. El alumno_tipo. Lo primero sería saber la puntuación del alumno_tipo. Lo podemos ver con la siguiente consulta:

SELECT puntuacion FROM alumnos WHERE nombre = 'alumno_tipo';

Luego, tendríamos que anotar el resultado de la consulta anterior en algún lugar y crear otra consulta como la siguiente:

SELECT nombre FROM alumnos WHERE puntuacion = 6;

Las subconsultas se crearon para ayudar con estos ejemplos. Son "consultas dentro de consultas" y siempre se ponen entre paréntesis. Echa un vistazo a la siguiente:

SELECT nombre FROM alumnos WHERE puntuacion = (  SELECT     puntuacion   FROM alumnos  WHERE nombre = 'alumno_tipo' );

La base de datos primero ejecutará la subconsulta (entre paréntesis), luego devolverá su resultado y lo pondrá en lugar de la subconsulta y, a continuación, ejecutará la consulta final.

En este ejemplo concreto, debemos escribir la subconsulta para que devuelva exactamente un valor (una columna de una fila), para que se pueda usar en la ecuación "puntuacion = X". No tendría mucho sentido poner una tabla entera.

Subconsultas con varios operadores lógicos

Las subconsultas también se pueden utilizar con otros operadores lógicos. Observa el siguiente ejemplo:

SELECT * FROM montana WHERE altura > (   SELECT altura   FROM montana   WHERE nombre = 'Mont Blanc' );

La consulta anterior devolverá todas las montañas que son más altas que el Mont Blanc. Como se puede ver, hemos utilizado el signo "mayor que" (>) junto con una subconsulta.

Funciones en subconsultas

Echa un vistazo a la siguiente consulta:

SELECT nombre FROM alumnos WHERE puntuacion <(SELECT AVG(puntuacion) FROM alumnos WHERE nombre = 'Alumno_tipo');

Ahora, nuestra consulta busca todos los alumnos con una puntuación inferior al promedio del alumno tipo. Como se puede ver, en la subconsulta utilizamos la función AVG() que nos da el valor promedio de una columna.

El operador IN

Hasta ahora, nuestras subconsultas solo devolvían valores únicos (como 6 o 16,28, por ejemplo).

SELECT *  FROM alumno WHERE puntuacion IN (3, 4, 5);

IN permite especificar varios valores en la cláusula WHERE, en lugar de uno solo.

En nuestro ejemplo, solo queremos mostrar alumnos que tengan una puntuación de 3 OR 4 OR 5. Eso es lo que significa IN (3,4,5).

Usar el operador IN con subconsultas

Para  utilizar el nuevo operador IN junto con subconsultas.  Hacemos lo siguiente:

SELECT precio FROM viaje WHERE id_ciudad IN (   SELECT id   FROM ciudad   WHERE poblacion < 2000000 );

En la subconsulta, buscamos los Id de todas las ciudades con una población inferior a 2 millones de habitantes. A continuación, utilizamos estos ID como valores para el operador IN.

Así, podemos obtener los precios de los viajes a las ciudades que tienen una población inferior a 2 millones de personas.

El operador ALL

SELECT * FROM pais WHERE superficie > ALL ( SELECT superficie   FROM ciudad );

Como se puede ver, tenemos el nuevo operador ALL a la derecha del operador lógico >. En este caso, > ALL significa "mayor que todos los valores del paréntesis".

Como resultado, obtendremos todos los países cuya superficie sea mayor que las superficies de todas las ciudades. Liechtenstein, por ejemplo, es un país muy pequeño. Es mayor que algunas ciudades (como, por ejemplo, Lyon), pero no es mayor que todas las demás (Berlín, por ejemplo, es mayor), por lo que Liechtenstein no aparecerá en el resultado.

También se puede utilizar ALL con otros operadores lógicos, como, por ejemplo, = ALL, != ALL, < ALL, <= ALL y >= ALL.

Usar el operador ALL en subconsultas correlacionadas

SELECT * FROM alumnos mejor_alumno WHERE puntuacion >= ALL ( SELECT puntuacion   FROM alumnos otros_alumnos   WHERE mejor_alumno.id_clase = otros_alumnos.id_clase);

La consulta anterior busca todos los alumnos que han sacado la mayor puntuación de cada clase. La consulta solo devuelve los alumnos cuya puntuación es igual o superior a la de todos los alumnos de su clase.

El operador ANY

SELECT * FROM viaje WHERE precio < ANY (   SELECT precio   FROM viaje_de_senderismo   WHERE id_montana = 1 );

En el ejemplo anterior, queremos encontrar viajes a ciudades que sean más baratos que algún viaje de senderismo a la montaña con id 1 (Mont Blanc). Si encuentra un viaje urbano que sea más barato que cualquiera de los valores de los viajes al Mont Blanc, se mostrará en el resultado.

También se puede usar con otros operadores, como, por ejemplo, = ANY, != ANY, < ANY, <= ANY y >= ANY.

Usar el operador ANY en subconsultas correlacionadas

También se puede utilizar el operador ANY en las subconsultas correlacionadas.

SELECT * FROM viajes_america WHERE precio < ANY (   SELECT precio FROM viaje_asia   WHERE viaje_america.dias = viaje_asia.dias );

La consulta anterior compara los viajes a américa y los viajes a asia que duran el mismo número de días. Después, devuelve todos los viajes a asia que sean más baratos que cualquier viaje a una viaje a américa que dure los mismos días.

Consultas correlacionadas

Hasta ahora, solo hemos  visto subconsultas que eran independientes de la consulta principal. Se podría ejecutar solo la subconsulta y, luego, poner su resultado en la consulta principal.

Vamos a ver subconsultas que dependen de la consulta principal. Se llaman subconsultas correlacionadas.

SELECT * FROM país WHERE superficie <= ( SELECT MIN(superficie)   FROM ciudad

 WHERE ciudad.id_pais = pais.id );

Queremos obtener todos los países cuya superficie sea igual o menor que la superficie mínima de las ciudades de ese país. En otras palabras, si hay un país más pequeño que su ciudad más pequeña, se mostrará. ¿Por qué utilizaríamos una consulta de este tipo? Puede ser muy útil para comprobar si hay algún error en la base de datos. Si esta consulta devolviera algún registro, sabríamos que algo raro está pasando con nuestros datos.

¿Cuál es la novedad en esta consulta? Echa un vistazo a la cláusula WHERE de la subconsulta. utiliza pais.id. ¿A qué país se refiere? Obviamente, al país de la consulta principal. Éste es el secreto de las subconsultas correlacionadas; si se ejecutase solo la subconsulta, la base de datos dará error.

Pero, si se ejecutase la instrucción como una subconsulta y se utiliza por ejemplo el campo fecha que explore la tabla pais, la base de datos compararía todas las veces el pais.id de la subconsulta con el pais.id de ese momento de la fecha.

Recuerde la regla de oro: las subconsultas pueden utilizar tablas de la consulta principal, ¡pero la consulta principal no puede utilizar tablas de la subconsulta!

Usar alias para las tablas

Puede haber ejemplos en los que la misma tabla se utilice tanto en la consulta principal como en la subconsulta correlacionada.

SELECT * FROM ciudad ciudad_principal WHERE poblacion > (SELECT AVG(poblacion)

  FROM ciudad  media_ciudades WHERE media_ciudades.id_pais = ciudad_principal.id_pais);

En este ejemplo, queremos encontrar ciudades con una población superior a la media de la población de todas las ciudades del país en cuestión. El problema es que buscamos las ciudades en la cláusula principal y comprobamos el valor de la media de la población de las ciudades en la subconsulta. La misma tabla aparece dos veces, lo que no es bueno.

Por eso, debemos utilizar alias para las tablas. En la subconsulta ponemos ... FROM ciudad media_ciudades ... y en la consulta principal ... FROM ciudad ciudad_principal. Como se puede ver, dimos nuevos nombres temporales a la tabla ciudad, y son diferentes para la consulta principal y para la subconsulta. El nombre temporal (el llamado alias) se pone después del nombre de la tabla, separado por un espacio. Recuerda, aquí no se usan comas.

Usar el operador IN con subconsultas correlacionadas

El operador IN nos permite especificar algunos valores en la cláusula WHERE, por lo que funciona un poco como el operador OR.

SELECT * FROM ciudad WHERE id_pais IN (SELECT id FROM país WHERE pais.poblacion < 40000);

Mostrará todas las ciudades de aquellos países que tengan una población total inferior a 40.000 habitantes.

El operador EXISTS

EXISTS es un operador que comprueba si alguna fila cumple la condición.

SELECT * FROM empleado WHERE EXISTS ( SELECT * FROM jefe WHERE id_empleado = empleado.id );

La consulta anterior solo mostrará la información de los empleados para los que existe al menos un empleado subordinado, del que es jefe el primero. No se mostrarán los empleados que no son jefes, (no tienen empleados subordinados). Si queremos lo contrario podemos poner NOT EXISTS

Usar subconsultas en la cláusula FROM

Las consultas también pueden utilizarse en otros lugares. Por ejemplo, en la cláusula FROM se puede utilizar una subconsulta en lugar de una tabla.

SELECT * FROM empleado, (SELECT  *   FROM jefe  WHERE subordinados < 10) AS jefe_grupo_pequeño WHERE jefe_grupo_pequeño.id =empleado.id_jefe;

La consulta anterior devuelve los jefes de los grupos pequeños. Pero, en nuestra base de datos no existe ninguna tabla llamada jefe_grupo_pequeño, así que... la creamos "sobre la marcha", utilizando una subconsulta en la cláusula FROM. Ya que tenemos que ponerle un nombre, utilizamos la palabra clave AS. Como resultado, la consulta muestra los empleados junto con sus jefes, siempre que estos tengan un grupo de menos de 10 subordinados.  Tenemos que poner la condición en la cláusula WHERE, porque, de lo contrario, cada empleado se mostraría junto con todos los jefes posibles.

Usar subconsultas en la cláusula FROM

SELECT  nombre,   dias,   precio FROM viaje, (     SELECT       *     FROM ciudad     WHERE puntuacion = 5) AS ciudad_bonita WHERE ciudad_bonita.id = viaje.id_ciudad;

La consulta anterior encuentra los viajes y sus respectivas ciudades para las ciudades que tienen una puntuación de 5. A continuación, muestra las columnas nombre, dias y precio para estas tablas. Cuando todas las columnas de las tablas tienen nombres diferentes, se pueden suprimir los nombres de las tablas (es decir, se puede escribir precio en lugar de viaje.precio, porque solo hay una columna llamada precio).

Obviamente, en esas consultas se pueden elegir solo algunas columnas. Estudia el siguiente ejemplo:

SELECT   nombre,   dias,   precio FROM viaje, ( SELECT       *     FROM ciudad     WHERE puntuacion = 5) AS ciudad_bonita WHERE ciudad_bonita.id = viaje.id_ciudad;

La consulta anterior encuentra los viajes y sus respectivas ciudades para las ciudades que tienen una puntuación de 5. A continuación, muestra las columnas nombre, dias y precio para estas tablas. Cuando todas las columnas de las tablas tienen nombres diferentes, se pueden suprimir los nombres de las tablas (es decir, se puede escribir precio en lugar de viaje.precio, porque solo hay una columna llamada precio).

Usar subconsultas en la cláusula SELECT

También se pueden usar subconsultas dentro de la lista de columnas de una cláusula SELECT. Para ello, es importante que la subconsulta devuelva exactamente una fila y una columna.

 SELECT   nombre,   (SELECT COUNT(*)   FROM viaje   WHERE id_ciudad = ciudad.id) AS numero_de_viajes FROM ciudad;

La consulta anterior devuelve el nombre de cada ciudad junto con el número de viajes que se realizan a la misma. Observa que utilizamos la función COUNT() para contar el número de viajes a cada ciudad.