Cerrar

Enviar mensaje

twitter FaceBook You Tube Git Hub Docs.com Enviar email

Manual de SQL (Oracle SQL)

[9]
Subconsultas

Publicidad

[9.1] introducción a las subconsultas

El uso de subconsultas es una técnica que permite utilizar el resultado de una tabla SELECT en otra consulta SELECT. Permite solucionar consultas complejas mediante el uso de resultados previos conseguidos a través de otra consulta.

El SELECT que se coloca en el interior de otro SELECT se conoce con el término de SUBSELECT. Ese SUBSELECT se puede colocar dentro de las cláusulas WHERE, HAVING, FROM o JOIN.

[9.2] uso de subconsultas en instrucciones SELECT

[9.2.1]uso de subconsultas simples

Las subconsultas simples son aquellas que devuelven una única fila. Si además devuelven una única columna, se las llama subconsultas escalares, ya que devuelven un único valor.

La sintaxis es:

SELECT listaExpresiones
FROM tabla
WHERE expresión OPERADOR 
			(SELECT listaExpresiones
			FROM tabla);

El operador puede ser >,<,>=,<=,!=, = o IN.

Ejemplo:

SELECT nombre_empleado, paga
FROM empleados
WHERE paga < 
		(SELECT paga FROM empleados
		WHERE nombre_empleado=’Martina’)
;

Esa consulta muestra el nombre y paga de los empleados cuya paga es menor que la de la empleada Martina. Para que funcione esta consulta, la subconsulta solo puede devolver un valor (solo puede haber una empleada que se llame Martina.

Se pueden usar subconsultas las veces que haga falta:

SELECT nombre_empleado, paga
FROM empleados
WHERE paga < 
		(SELECT paga FROM empleados
		WHERE nombre_empleado=’Martina’)
AND paga >
		(SELECT paga FROM empleado 
		WHERE nombre_empleado=’Luis’);

En realidad lo primero que hace la base de datos es calcular el resultado de la subconsulta:

La última consulta obtiene los empleados cuyas pagas estén entre lo que gana Luís (1870 euros) y lo que gana Martina (2500) .

Las subconsultas siempre se deben encerrar entre paréntesis y se deberían (aunque no es obligatorio, sí altamente recomendable) colocar a la derecha del operador relacional.

Una subconsulta que utilice los valores >,<,>=,... tiene que devolver un único valor, de otro modo ocurre un error.

Además tienen que devolver el mismo tipo y número de datos para relacionar la subconsulta con la consulta que la utiliza (no puede ocurrir que la subconsulta tenga dos columnas y ese resultado se compare usando una sola columna en la consulta general).

[9.2.2]uso de subconsultas de múltiples filas

En el apartado anterior se comentaba que las subconsultas sólo pueden devolver una fila. Pero a veces se necesitan consultas del tipo: mostrar el sueldo y nombre de los empleados cuyo sueldo supera al de cualquier empleado del departamento de ventas.

La subconsulta necesaria para ese resultado mostraría todos los sueldos del departamento de ventas. Pero no podremos utilizar un operador de comparación directamente ya que esa subconsulta devuelve más de una fila. La solución a esto es utilizar instrucciones especiales entre el operador y la consulta, que permiten el uso de subconsultas de varias filas.

Esas instrucciones son:

Instrucción

Significado

ANY

o

SOME

Compara con cualquier registro de la subconsulta. La instrucción es válida si hay un registro en la subconsulta que permite que la comparación sea cierta.

Se suele utilizar la palabra ANY (SOME es un sinónimo)

ALL

Compara con todos los registros de la consulta. La instrucción resulta cierta si es cierta toda comparación con los registros de la subconsulta

IN

No usa comparador, ya que sirve para comprobar si un valor se encuentra en el resultado de la subconsulta

NOT IN

Comprueba si un valor no se encuentra en una subconsulta

Ejemplo:

SELECT nombre, sueldo
FROM empleados
WHERE sueldo >= ALL (SELECT sueldo FROM empleados);

La consulta anterior obtiene el empleado que más cobra. Otro ejemplo:

SELECT nombre FROM empleados
WHERE dni IN (SELECT dni FROM directivos);

En ese caso se obtienen los nombres de los empleados cuyos dni están en la tabla de directivos.

Si se necesita comparar dos columnas en una consulta IN, se hace de esta forma:

SELECT nombre FROM empleados
WHERE (cod1,cod2) IN (SELECT cod1,cod2 FROM directivos);

[9.2.3]consultas correlacionadas

En las subconsultas a veces se puede desear poder utilizar datos procedentes de la consulta principal. Eso es posible utilizando el alias de la tabla que queremos usar de la consulta principal.

Por ejemplo, supongamos que deseamos obtener de una base de datos geográfica, el nombre y la población de las localidades que sean las más pobladas de su provincia. Es decir, las localidades cuya población es la mayor de su provincia. Para ello necesitamos comparar la población de cada localidad con la de todas las localidades de su provincia. Supongamos que la tabla de las localidades almacena el nombre, población y el número de la provincia a la que pertenecen.

La consulta sería:

En el código anterior se observa que dentro de la subconsulta usamos el alias l correspondiente a la tabla de localidades de la consulta principal (por eso se le ha puesto como alias l2 a la tabla localidades en la subconsulta).

[9.2.4]consultas EXISTS

Este operador devuelve verdadero si la consulta que le sigue devuelve algún valor. Si no, devuelve falso. Se utiliza normalmente mediante consultas correlacionadas. Ejemplo:

SELECT tipo,modelo, precio_venta
FROM piezas p
WHERE EXISTS (
            SELECT tipo,modelo FROM existencias
                   WHERE tipo=p.tipo AND modelo=p.modelo);

Esta consulta devuelve las piezas que se encuentran en la tabla de existencias (es igual al ejemplo comentado en el apartado subconsultas sobre múltiples valores).

La consulta contraria es :

SELECT tipo,modelo, precio_venta
FROM piezas p
WHERE NOT EXISTS (
            SELECT tipo,modelo FROM existencias
                  WHERE tipo=p.tipo AND modelo=p.modelo);

Normalmente las consultas EXISTS se pueden realizar de alguna otra forma con otros operadores.

[9.3] uso de subconsultas SELECT en instrucciones DML y DDL

A pesar del poco ilustrativo título de este apartado, la idea es sencilla. Se trata de cómo utilizar instrucciones SELECT dentro de las instrucciones DML (INSERT, DELETE o UPDATE) o incluso dentro de otros apartados.

[9.3.1]relleno de registros a partir de filas de una consulta

Hay un tipo de consulta, llamada de adición de datos, que permite rellenar datos de una tabla copiando el resultado de una consulta. Se hace mediante la instrucción INSERT y, en definitiva, permite copiar datos de una tabla a otra.

Ese relleno se basa en una consulta SELECT que poseerá los datos a añadir. El orden y tipo de las columnas que resultan del SELECT debe de coincidir con el orden y tipo de las columnas de la instrucción INSERT.

Sintaxis:

INSERT INTO tabla (columna1, columna2,...)
SELECT expresioCompatibleColumna1, expresionCompatibleColumna2,...
FROM listaDeTablas
[...otras cláusulas del SELECT...]

Ejemplo:

Lógicamente las columnas del SELECT se tienen que corresponder (en cuanto al tipo de datos y posición) con las columnas a rellenar mediante INSERT (observar las flechas).

[9.3.2]subconsultas en la instrucción UPDATE

26subconsultas en el apartado WHERE

La instrucción UPDATE permite modificar filas. Es muy habitual el uso de la cláusula WHERE para indicar las filas que se modificarán. Esta cláusula se puede utilizar con las mismas posibilidades que en el caso del SELECT, por lo que es posible utilizar subconsultas. Por ejemplo:

UPDATE empleados
SET sueldo=sueldo*1.10
WHERE id_seccion =(SELECT id_seccion FROM secciones
					WHERE nom_seccion=’Producción’);

Esta instrucción aumenta un 10% el sueldo de los empleados de la sección llamada Producción.

Es posible utilizar subconsultas correlacionadas.

UPDATE empleados e
SET sueldo=sueldo*1.10
WHERE 1 = (SELECT COUNT(*) FROM historial h
  		WHERE h.id_empleado=e.id_empleado);

Esta consulta aumenta un 10% el sueldo de los empleados si solo han tenido un empleo en su historial.

27subconsultas en el apartado SET

También podemos utilizar subconsultas en la cláusula SET de la instrucción UPDATE.

Ejemplo:

UPDATE empleados
SET puesto_trabajo=(SELECT puesto_trabajo
 				 FROM empleados
				 WHERE id_empleado=12)
WHERE seccion=23;

Esta instrucción coloca a todos los empleados de la sección 23 el mismo puesto de trabajo que el que posee el empleado número 12. Este tipo de actualizaciones sólo son válidas si el subselect devuelve un único valor (es decir, si utiliza una subconsulta de tipo escalar), que además debe de ser compatible con la columna que se actualiza.

[9.3.3]subconsultas en la instrucción DELETE

Al igual que en el caso de las instrucciones INSERT o SELECT, DELETE dispone de cláusula WHERE y en dichas cláusulas podemos utilizar subconsultas. Por ejemplo:

DELETE empleados
WHERE id_empleado IN 
(SELECT id_empleado FROM errores_graves);

En este caso se trata de una subconsulta utilizada en el operador IN, se eliminarán los empleados cuyo identificador esté dentro de la tabla de errores graves.

[9.4] subconsultas insertadas en las cláusulas FROM y JOIN

El resultado de una operación de tipo SELECT es una vista (aunque sea temporal, ya que no se almacena de forma permanente). Y las vistas pueden ser utilizadas dentro de otras vistas (al igual que las tablas).

Así una consulta como esta:

SELECT tipo,modelo, SUM(cantidad) suma_cantidad
FROM existencias
GROUP BY tipo, modelo

Muestra los tipos y modelos de piezas en los almacenes y la suma de cantidades que poseen sumando la de cada almacén. El resultado es una vista de tres columnas. Lo interesante es que puede ser un inicio para una nueva consulta.

Por ejemplo:

SELECT tipo, COUNT(modelo), SUM(suma_cantidad)
FROM (
  SELECT tipo,modelo, SUM(cantidad) suma_cantidad
  FROM existencias
  GROUP BY tipo, modelo
)
GROUP BY tipo;

La posibilidad de usar así las subconsultas se extiende a la cláusula JOIN:

SELECT tipo,modelo,precio_venta
FROM piezas P1
JOIN (
  SELECT MAX(precio_venta) max_precio_venta
  FROM piezas
) P2 ON P1.precio_venta=P2.max_precio_venta;

Así, esta consulta nos muestra el tipo y modelo de las piezas que tiene el precio de venta más alto. No es la única forma de resolver esta consulta, pero nos permite observar la capacidad de usar subconsultas de forma muy avanzada.

A esta técnica se le llama usar vistas en línea (en inglés inline views). Ahora bien, para que eso sea posible las columnas de la subconsulta deben usar alias obligatorios para que nos e repita el nombre de la columna y especialmente en las columnas con datos calculados.

Por ejemplo supongamos que deseamos saber el nombre de los empleados que tienen un jefe que gana más de 2000 euros. Para ello primero haremos un consulta que obtenga el nombre e identificador de los empleados que ganan más de 200o euros; luego bastará con combinar esta consulta y la tabla de empleados de modo que el jefe del empleado esté en la lista de empleados que ganan más de 200 euros. En formato SQL 92 sería:

SELECT e.nombre
FROM empleados e, 
	(SELECT nombre, id_empleado FROM empleados WHERE salario>2000) e2
WHERE e.id_jefe=e2.id_empleado;

Usando SQL 99:

SELECT e.nombre
FROM empleados e, 
JOIN (SELECT nombre, id_empleado FROM empleados WHERE salario>2000) e2
	ON(e.id_jefe=e2.id_empleado);

[9.5] subconsultas escalares

Las subconsultas escalares son aquellas que devuelven un único resultado. En definitiva son SELECT que devuelven una única consulta y un único valor. La tabla DUAL es un ejemplo de subconsulta escalar.

Estas subconsultas son muy útiles porque se pueden utilizar en muchas partes del lenguaje SELECT. Concretamente:

Desgraciadamente (y la tentación es grande), no se pueden utilizar en las restricciones CHECK para validar datos. Esta necesidad sólo la pueden resolver los triggers mediante el lenguaje PL/SQL.

Ejemplo de uso de subconsulta escalar:

SELECT nombre,
  (SELECT COUNT(*) 
  FROM comunidades 
  JOIN provincias USING (ID_COMUNIDAD) 
  where ID_comunidad=c.id_comunidad 
  GROUP BY ID_COMUNIDAD
  ) AS numero_provincias
FROM comunidades c;

Esta consulta obtiene el nombre de cada comunidad autónoma, seguida del número de provincias que tiene (habría otras formas más sencillas de conseguirla).

Con imaginación, podemos conseguir consultas tan espectaculares como esta:

SELECT nombre,
  DECODE(
         (SELECT COUNT(*) 
          FROM comunidades 
          JOIN provincias USING (id_comunidad) 
          WHERE id_comunidad=c.id_comunidad 
          GROUP BY id_comunidad
	  ),
       1,’Uniprovincial’, ‘Multiprovincial’
      )
FROM comunidades c;

Basada en la anterior, ahora obtenemos el nombre de cada comunidad y un texto que dice si es uniprovincial o multiprovincial.

[9.6] creación de tablas mediante subconsultas

También la instrucción CREATE TABLE tiene la capacidad de ser utilizada mediante una subconsulta. Lo que se crea es una tabla que contiene una copia de los datos de la consulta y cuya estructura se basa en función de dichos datos.

Sintaxis:

CREATE TABLE nombreTabla AS
  SELECT ...contenidoConsultaSELECT...;

Ejemplo:

CREATE TABLE resumenAlmacenes
AS
  SELECT tipo || ‘-’ || modelo AS clave, precio_venta, 
		SUM(cantidad) AS cantidad
  FROM almacen.piezas p
  JOIN almacen.existencias e USING(tipo,modelo) 
  GROUP BY tipo,modelo,precio_venta;

El resultado es una tabla creada a partir de la consulta que recoge datos de las tablas piezas y existencias dentro de un esquema llamado almacen. Los tipos de las columnas se determinan a partir de los datos origen.

Estas tablas valen solo como copia de los datos, ya que no contendrán las restricciones habituales (PRIMARY KEY, FOREIGN KEY, etc.) de las tablas relacionales.