#Curso SQL: Consultas con subquery

Voy a continuar hoy con el curso de SQL. Esta quinta parte será la última de la introducción al DML (Lenguaje de manipulación de datos). En los siguientes artículos nos introduciremos en el la parte de SQL correspondiente al DDL (Lenguaje de definición de datos).

Toca hoy hablar de las subqueries o subconsultas.

Una subquery se define como una consulta (query) dentro de otra consulta. Es posible colocar una subquery dentro de las consultas de tipo SELECT, INSERT, UPDATE y DELETE. La subquery, por su parte, es siempre una sentencia SELECT y debe ir encerrada entre paréntesis.

Una subquery que devuelve un valor simple puede ser colocada en cualquier lugar en que pudiese haber una expresión. Las subconsultas se tratan como una tabla virtual si van situadas en la clausula FROM. Pero se pueden situar en la cláusula FROM, WHERE o HAVING de la consulta principal.

En Oracle se permiten hasta 255 niveles de subconsultas anidadas. Sin embargo, este límite es teórico, pues es muy raro ver subconsultas anidadas más allá de 3 o 4 niveles.

Hay tres tipos de subconsultas: de una sola fila, de varias filas o correlacionadas.

Subconsultas de una sola fila

En este tipo de subquery se considera que se devuelve una sola fila como un único valor. No se consideran en éste tipo consultas que devuelvan varias columnas de una sola fila.

Vamos a tener una base de datos de empleados. Queremos seleccionar el departamento, salario y nombre del empleado con salario mínimo. Para ello usaremos la siguiente consulta:

SELECT Nombre, Id_Departamento, Salario
FROM Empleado
WHERE salary = (SELECT MIN(salario) FROM Empleados);

Necesitamos utilizar esta subquery por que de lo contrario, Nombre e Id_departamento deben estar en una cláusula GROUP BY para poder utilizar la función MIN en la cláusula SELECT. Así que seleccionaríamos el empleado con salario mínimo entre los que se llamen igual y estén en el mismo departamento, no de todos los existentes.

Si queremos añadir una condición en una consulta con cláusula GRUP BY, debemos utilizar la cláusula HAVING. Así podemos seleccionar el salario mínimo de un departamento, siempre que dicho salario esté por debajo de la media de todos los empleados:

SELECT id_dep, MIN (salario)
FROM Empleado
GROUP BY id_dep
HAVING MIN (salario) < (SELECT AVG (salario)
FROM Empleado)

Subquery de múltiples filas

Este tipo de subquery devuelve más de una fila como un conjunto de resultados. Se sitúa, generalmente en las cláusulas WHERE o HAVING. Dado que devuelven múltiples filas, debemos utilizar algún operador de comparación de conjuntos (IN, ALL, ANY).

• IN : devuelve true si el valor es igual que un elemento de la lista.
• ANY : compara el valor con cada elemento de la lista.
• ALL : devuelve true si el valor es igual a todos los valores de la lista.

Los operadores ANY y ALL se utilizan combinados con los operadores de comparación habituales:

• = ANY : Igual a alguno de los valores de la lista (equivalente a IN).
• != ANY : Distinto de alguno de los valores de la lista.
• < ANY : Menor que el mayor de los valores de la lista.
• > ANY : Mayor que el menor de los valores de la lista.

• = ALL: Igual a con todos los valores de la lista.
• != ALL : No igual a ningún valor de la lista (equivalente a NOT IN);
• > ALL : Mayor que el mayor de todos los valores de la lista.
• < ALL : Menor que el menor de los valores de la lista.

Veamos ejemplos de todo ésto:

Seleccionar los empleados cuyo departamento está en la localización 2:

SELECT nombre, id_dep
FROM Empleado
WHERE id_dep IN (SELECT id
FROM Departamento
WHERE localizacion = 2);

Seleccionar todos los nombres de empleados y su salario siempre que tengan menor salario que los empleados del departamento 5:

SELECT nombre, salario
FROM Empleado
WHERE salario < ALL (SELECT salario
                     FROM Empleado
                     WHERE id_dep = 5);

Conocer el nombre y salario de todos los empleados que cobren lo mismo o más que cualquiera de los empleados del departamento 3.

SELECT nombre, salario, id_dep
FROM Empleado
WHERE salario >= ANY (SELECT salario
                      FROM Empleado
                      WHERE id_dep = 3);

Fíjate que las condiciones anteriores también funcionan con los comparadores <= y >=.

Subquery con múltiples filas

Las subconsultas que hemos visto devuelven un único valor por fila. Sin embargo, es posible encontrar subconsultas que tengan más de un valor por fila. Éstas pueden ser colocadas en las cláusulas FROM, WHERE o HAVING.

Cuando este tipo de subquery está en el FROM, se tratan como tablas temporales o más formalmente, vistas “inline”. Estas vistas inline pueden utilizarse como cualquier otra tabla y hacer JOIN o extraer valores de la misma. Incluso, pueden llevar alias.

Observa el siguiente ejemplo que, siendo un poco tonto, ilustra a la perfección esto:

SELECT Salario.salario, Salario.id_dep
FROM (SELECT salario, id_dep
      FROM Empleado
      WHERE salario BETWEEN 1000 and 1500) Salario;

Cuando las consultas de varias columnas están colocadas en el WHERE o el HAVING, la comparación debe hacerse de todas las columnas de cada fila. Como en el siguiente ejemplo, que, siendo simple, permite ver el funcionamiento:

SELECT nombre, salario
FROM Empleado
WHERE (salario, id_dep) in (SELECT salario, id_dep
                            FROM Empleado
                            WHERE salario BETWEEN 1000 and 1500)
ORDER BY nombre;

Subquery correlacionada

Las consultas vistas hasta este momento hacen que el resultado de la columna general dependa de los valores de la subquery. Pero también es posible hacer que el resultado de la consulta interna dependa de valores proporcionados por la consulta externa.

Ésto quiere decir que la consulta interna, se ejecuta una vez por cada fila seleccionada por la consulta principal.

Veamos como ejemplo la siguiente consulta:

SELECT Nombre, Salario, Id_Departamento
FROM Empleado E
WHERE Salario > (SELECT AVG(Salario)
                 FROM Empleado T
                 WHERE E.Id_Departamento = T.Id_Departamento);

Lo que hacemos es seleccionar los empleados de un departamento que tengan un salario superior a la media de dicho departamento. En primer lugar se hacer una selección de todos los empleados en la consulta externa. Después se ejecuta la subquery (en cada fila, y se calcula la media de los empleados del departamento en el que se encuentra el empleado que se está seleccionando en la anterior (E.Id_Empleado).

Probando los ejemplos de código de éste artículo

Para probar los ejemplos de código y experimentar con las consultas puedes ir al siguiente enlace:

http://sqlfiddle.com/#!4/81dae0

En caso de estar en blanco el cuadro de texto sobre el botón “Build Schema”, pegar el siguiente código y pulsar sobre dicho botón.

CREATE TABLE Empleado(
  Id_Empleado number(10,0),
  Id_Departamento number(10,0),
  Nombre varchar2(30),
  Salario number(8,2)
);

Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (1, 1, 'Pepe', 1200);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (2, 1, 'Juan', 1180.68);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (3, 5, 'Pedro', 940.80);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (4, 2, 'Maria', 625.0);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (5, 1, 'Luisa', 890.7);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (6, 4, 'Ruben', 1200);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (7, 4, 'Fatima', 1900.20);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (8, 3, 'Jose', 1500.12);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (9, 5, 'Elena', 901.40);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (10, 4, 'Albaro',1200);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (11, 2, 'Alba', 901.40);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (12, 1, 'Fran', 1200);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (13, 5, 'Emilia', 901.40);
Insert into Empleado(Id_Empleado, Id_Departamento, Nombre, Salario)
values (14, 3, 'Laura', 1200);



CREATE TABLE Departamento(
  Id_Departamento number(10,0),
  Nombre varchar2(50),
  Localizacion number(10,0)
);

Insert into Departamento(Id_Departamento, Nombre, Localizacion)
values (1, 'Ventas', 1);
Insert into Departamento(Id_Departamento, Nombre, Localizacion)
values (2, 'Marketing', 2);
Insert into Departamento(Id_Departamento, Nombre, Localizacion)
values (3, 'Desarrollo', 2);
Insert into Departamento(Id_Departamento, Nombre, Localizacion)
values (4, 'Analítica de datos', 1);
Insert into Departamento(Id_Departamento, Nombre, Localizacion)
values (5, 'Despliegue', 4);

En el recuadro de la derecha se escriben las consultas y se ejecutan pulsando sobre “Run SQL”. Y en la parte inferior se mostrarán los resultados de la ejecución de la consulta.

Artículos anteriores del curso de SQL:

Capítulo 1: Introducción al SQL y las bases de datos relacionales

Capítulo 2: La cláusula WHERE, filtrando datos

Capítulo 3: SELECT avanzado e INSERT INTO

Capítulo 4: Cruzando tablas

El artículo #Curso SQL: Consultas con subquery apareció por primera vez en Instinto Binario.