2.5. Subconsultas

Una subconsulta es una instrucción SELECT anidada dentro de una sentencia SELECT, SELECT...INTO, INSERT...INTO, DELETE, o UPDATE o dentro de otra subconsulta.

Una subconsulta, a su vez, puede contener otra subconsulta y así hasta un máximo de 16 niveles. Las particularidades de las subconsultas son:

  1. Su resultado no se visualiza, sino que se pasa a la consulta principal para su comprobación.
  2. Puede devolver un valor único o una lista de valores y en dependencia de esto se debe usar el operador del tipo correspondiente.
  3. No puede usar el operador BETWEEN, ni contener la setencia ORDER BY.
  4. Puede contener una sola columna, que es lo más común, o varias columnas.Este último caso se llama subconsulta con columnas múltiples. Cuando dos o más columnas serán comprobadas al mismo tiempo, deben encerrarse entre paréntesis.

Ejemplo: Nombres de los jugadores de la NBA 2015 - 2016 que ganan 2.5 veces el promedio, equipo y que juegan de poivotes.

SELECT nombre, equipo, TO_CHAR( ROUND(salario,0),'999,999,999') salario
FROM nbaSalarios
WHERE salario > 2.5*(
SELECT AVG(salario) media FROM nbaSalarios
WHERE idPosicion = 'C') AND idPosicion = 'C'
ORDER BY salario;

La consulta más interna calcula el salario promedio de la posición Pivote, y la consulta más externa lo utiliza para seleccionar los nombres de los jugadores que ganan 2.5 veces más del promedio.

El valor de comparación puede ser un valor simple, como en el ejemplo anterior, o un conjunto de valores. Hay que tener en cuenta este detalle ya que el tipo de operador a utilizar varía. En el primer caso se puede utilizar un operador de comparación de carácter aritmético (<, >, etc.). Y en el segundo uno de tipo lógico (IN).

Ejemplo: Estados donde existe un municio con nombre 'Jilotepec'.

SELECT estado 
 FROM estados
 WHERE idEstado 
    IN (SELECT idEstado FROM municipios WHERE municipio = 'Jilotepec')

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.

Ejemplo:Estados que contienen en su nombre la cadena 'Carlos'.

SELECT estado
 FROM estados E
 WHERE EXISTS (SELECT * FROM municipios M
                WHERE E.idEstado = M.idEstado 
                     AND M.municipio LIKE '%Carlos%')

Subconsultas multicolumna

Las subconsultas pueden devolver más de una columna, y se habrán de comparar de manera consecuente:

Estas consultas tienen la siguiente sintaxis


Considere el siguiente ejemplo egocéntrico. Que obtiene nombre de las colonias que contienen la palabra Pacheco, idEstado y idEstado

SELECT idEstado, idMunicipio, colonia 
FROM colonia
WHERE  ( idEstado, idMunicipio, colonia )
   IN (  SELECT idEstado, idMunicipio, colonia 
         FROM colonia  
         WHERE colonia LIKE '%Pacheco%');

Subconsulta en la cláusula FROM

Estas consultas son llamadas vista en línea. Una subconsulta en una cláusula FROM de una sentencia SELECT define un origen de datos para esa sentencia SELECT en particular, y solo esa sentencia SELECT.

Ejemplo: Nombre de los Estados que tiene al menos un municipio cuyo nombre contiene la palabra Carlos

SELECT E.estado, M.municipio
FROM estados E, ( SELECT idEstado, municipio
                 FROM municipios 
                 WHERE municipio LIKE '%Carlos%') M
WHERE E.idEstado = M.idEstado
ORDER BY estado;

Cláusula WITH

Usando la cláusula WITH, se puede definir un bloque de una consulta antes de que esta sea usada. La cláusula WITH (formalmente conocida como 'subquery_factoring_clausula' cláusula de subconsulta factorizada) habilita la reutilización del mismo bloque de la consulta en una sentencia SELECT

Cuando esto ocurre en mas de una ocasión en una consulta compleja. Esto es particularmente útil cuando una consulta tiene muchas referencias al mismo bloque de una consulta y se tienen asociaciones y agrupaciones.

Usando la cláusula WITH, se puede reutilizar la misma consulta cuando es de alto costo evaluar el bloque de la consulta y ocurre más de una vez en una consulta compleja. Usando la cláusula WITH, el servidor de Oracle recupera los resultados de un bloque de la consulta y los almacena en un tablespace temporal del usuario. Esto puede mejorar el desempeño.

Beneficios de la cláusula WITH

EjemploPosiciones con salario promedio mayor que el promedio de todas ellas.( NBA 2016 - 2017)

WITH
 costoPosicion AS (
   SELECT P.nPosicion, AVG(J.salario) AS salarioPromedio
   FROM posicionNBA P, nbaSalarios J
   WHERE P.idPosicion = J.idPosicion
   GROUP BY nPosicion),
 estadistico AS ( 
   SELECT  AVG(salarioPromedio) AS promedio 
   FROM costoPosicion
 )
 SELECT nPosicion, TO_CHAR(ROUND(salarioPromedio,0),'999,999,999') salario
 FROM costoPosicion
 WHERE salarioPromedio > (SELECT promedio FROM estadistico) 
;

Se puede utilizar una subconsulta para insertar valores en una tabla en el momento de la creación de la misma.

Ejemplo: Crear una tabla con los datos de los jugadores que participan más que el promedio y su desviación:

CREATE TABLE borrame
  SELECT minutos, nombreJugador FROM femexfut
  WHERE minutos > (
    SELECT AVG(minutos) + STDDEV(minutos) 
    FROM femexfut
    WHERE jj > 0 AND torneo = 'Bicentenario 2010')
	AND torneo = 'Bicentenario 2010'
  ORDER BY minutos DESC, nombreJugador

No es necesario especificar tipos ni tamaños de las columnas, ya que vienen determinados por los tipos y tamaños de las columnas recuperadas en la subconsulta.