Tema 5. Las subconsultas (II)

Condiciones de selección con subconsultas

El test de comparación con subconsulta.

Es el equivalente al test de comparación simple. Se utiliza para comparar un valor de la fila que se está examinado con un único valor producido por la subconsulta. La subconsulta debe devolver una única columna, sino se produce un error.
Si la subconsulta no produce ninguna fila o devuelve el valor nulo, el test devuelve el valor nulo, si la subconsulta produce varias filas, SQL devuelve una condición de error.

La sintaxis es la siguiente:

SELECT oficina, ciudad
FROM oficinas
WHERE objetivo > (SELECT SUM(ventas) FROM empleados WHERE empleados.oficina = oficinas.oficina)

Lista las oficinas cuyo objetivo sea superior a la suma de las ventas de sus empleados.
En este caso la subconsulta devuelve una única columna y una única fila (es un consulta de resumen sin GROUP BY)

El test de comparación cuantificada.

Este test es una extensión del test de comparación y del test de conjunto. Compara el valor de la expresión con cada uno de los valores producidos por la subconsulta. La subconsulta debe devolver una única columna sino se produce un error.
Tenemos el test ANY (algún, alguno en inglés) y el test ALL (todos en inglés).

La sintaxis es la siguiente:

El test ANY.

La subconsulta debe devolver una única columna sino se produce un error.
Se evalúa la comparación con cada valor devuelto por la subconsulta.
Si alguna de las comparaciones individuales produce el resultado verdadero, el test ANY devuelve el resultado verdadero.
Si la subconsulta no devuelve ningún valor, el test ANY devuelve falso.
Si el test de comparación es falso para todos los valores de la columna, ANY devuelve falso.
Si el test de comparación no es verdadero para ningún valor de la columna, y es nulo para al menos alguno de los valores, ANY devuelve nulo.

SELECT oficina, ciudad
FROM oficinas
WHERE objetivo > ANY (SELECT SUM(cuota) FROM empleados GROUP BY oficina)

En este caso la subconsulta devuelve una única columna con las sumas de las cuotas de los empleados de cada oficina.
Lista las oficinas cuyo objetivo sea superior a alguna de las sumas obtenidas.

El test ALL.

La subconsulta debe devolver una única columna sino se produce un error.
Se evalúa la comparación con cada valor devuelto por la subconsulta.
Si todas las comparaciones individuales, producen un resultado verdadero, el test devuelve el valor verdadero.
Si la subconsulta no devuelve ningún valor el test ALL devuelve el valor verdadero. (¡Ojo con esto!)
Si el test de comparación es falso para algún valor de la columna, el resultado es falso.
Si el test de comparación no es falso para ningún valor de la columna, pero es nulo para alguno de esos valores, el test ALL devuelve valor nulo.

SELECT oficina, ciudad
FROM oficinas
WHERE objetivo > ALL (SELECT SUM(cuota) FROM empleados GROUP BY oficina)

En este caso se listan las oficinas cuyo objetivo sea superior a todas las sumas.

Test de pertenencia a conjunto (IN).

Examina si el valor de la expresión es uno de los valores incluidos en la lista de valores producida por la subconsulta.
La subconsulta debe generar una única columna y las filas que sean.
Si la subconsulta no produce ninguna fila, el test da falso.
Tiene la siguiente sintaxis:

SELECT numemp, nombre, oficina
FROM empleados
WHERE oficina IN (SELECT oficina FROM oficinas WHERE region = 'este')

Con la subconsulta se obtiene la lista de los números de oficina del este y la consulta principal obtiene los empleados cuyo número de oficina sea uno de los números de oficina del este.
Por lo tanto lista los empleados de las oficinas del este.

El test de existencia EXISTS.

Examina si la subconsulta produce alguna fila de resultados.
Si la subconsulta contiene filas, el test adopta el valor verdadero, si la subconsulta no contiene ninguna fila, el test toma el valor falso, nunca puede tomar el valor nulo.
Con este test la subconsulta puede tener varias columnas, no importa ya que el test se fija no en los valores devueltos sino en si hay o no fila en la tabla resultado de la subconsulta.
Cuando se utiliza el test de existencia en la mayoría de los casos habrá que utilizar una referencia externa. Si no se utiliza una referencia externa la subconsulta devuelta siempre será la misma para todas las filas de la consulta principal y en este caso se seleccionan todas las filas de la consulta principal (si la subconsulta genera filas) o ninguna (si la subconsulta no devuelve ninguna fila)

La sintaxis es la siguiente:

SELECT numemp, nombre, oficina
FROM empleados
WHERE EXISTS (SELECT * FROM oficinas WHERE region = 'este' AND empleados.oficina = oficinas.oficina)

Este ejemplo obtiene lo mismo que el ejemplo del test IN.
Observa que delante de EXISTS no va ningún nombre de columna.
En la subconsulta se pueden poner las columnas que queramos en la lista de selección (hemos utilizado el *).
Hemos añadido una condición adicional al WHERE, la de la referencia externa para que la oficina que se compare sea la oficina del empleado.

NOTA. Cuando se trabaja con tablas muy voluminosas el test EXISTS suele dar mejor rendimiento que el test IN.

Resumen del tema

Ejercicios propuestos