Subconsultas SQL

El artículo describe qué es una subconsulta y cómo son estas útiles sentencias. Cubriremos ejemplos básicos con los operadores IN, EXISTS, ANY y ALL, veremos las subconsultas en las cláusulas FROM y WHERE, y exploraremos la diferencia entre subconsultas correlacionadas y anidadas.

En primer lugar, empecemos con una base de datos de ejemplo. Para presentar algunas de estas sentencias necesitamos tener una tabla de ejemplo y llenarla con algunos datos.

¿Qué es una subconsulta?

Una subconsulta es una sentencia SELECT con otra sentencia SQL, como en el ejemplo de abajo.

SELECT *
FROM product
WHERE id IN (
  SELECT product_id
  FROM provider_offer
  WHERE provider_id = 156
);

Las subconsultas se clasifican además como subconsultas correlacionadas o subconsultas anidadas. Por lo general, se construyen de tal manera que devuelven

1. una tabla

   SELECT MAX(average.average_price)
   FROM (
     SELECT
       product_category,
       AVG(price) AS average_price
     FROM product
     GROUP BY product_category
   ) average;
   

2. o un valor

   SELECT id
   FROM purchase
   WHERE value > (
     SELECT AVG(value)
     FROM purchase
   );
   

¿Quieres saber más sobre las subconsultas SQL? Echa un vistazo a nuestra serie We Learn SQL en Youtube. Recuerda suscribirte a nuestro canal.

Subconsultas Anidadas

Las subconsultasanidadas son subconsultas que no dependen de una consulta externa. En otras palabras, ambas consultas en una subconsulta anidada pueden ser ejecutadas como consultas separadas.

Este tipo de subconsulta se puede utilizar en casi todas partes, pero suele adoptar uno de estos formatos:

SELECT
FROM
WHERE [NOT] IN (subquery)

SELECT *
FROM client
WHERE city IN (
  SELECT city
  FROM provider
);

La subconsulta del ejemplo devuelve todos los clientes que son DE la misma ciudad que los proveedores de productos.
El operador IN comprueba si el valor está dentro de la tabla y recupera las filas coincidentes.

SELECT
FROM
WHERE expression comparison_operator [ANY| ALL] (subquery)

Subconsulta con el operador ALL

El operador ALL compara un valor con todos los valores de la tabla de resultados.

Por ejemplo, la siguiente consulta devuelve todos los modelos y fabricantes de bicicletas que tienen un precio superior a los auriculares más caros.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND price > ALL(
    SELECT price
    FROM product
    WHERE product_category = 'headphones'
  );

Subconsulta similar pero con el operador ANY:

Subconsulta con el operador ANY

El operador ANY compara un valor con cada valor de una tabla y evalúa si el resultado de una consulta interna contiene al menos una fila.

La siguiente consulta devuelve todos los modelos y fabricantes de bicicletas que tienen un precio mayor que al menos uno de los auriculares.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND price > ANY(
    SELECT price
    FROM product
    WHERE product_category = 'headphones'
  );

También puede anidar una subconsulta en otra aubquery. Por ejemplo:

Subconsulta anidada en otra subconsulta utilizando el operador IN

Esta consulta devuelve los fabricantes y modelos de bicicletas que existen en las ofertas de los proveedores DESDE EEUU.

SELECT producer, model
FROM product
WHERE product_category = 'bike'
  AND id IN (
    SELECT distinct product_id
    FROM provider_offer
    WHERE provider_id IN (
      SELECT id
      FROM provider
      WHERE country = 'USA'
    )
  );

Lo mismo podría hacerse utilizando joins.

SELECT product.producer, product.model
FROM product, provider_offer, provider
WHERE provider_offer.product_id = product.id
  AND provider_offer.provider_id = provider.id
  AND product_category = 'bike'
  AND provider.country = 'USA';

Subconsultas correlacionadas

Las subconsultasestán correlacionadas cuando las consultas internas y externas son interdependientes, es decir, cuando la consulta externa es una consulta que contiene una subconsulta y la propia subconsulta es una consulta interna. Los usuarios que conocen los conceptos de programación pueden compararlo con una estructura de bucle anidado.

Empecemos con un ejemplo sencillo.

La consulta interna calcula el valor medio y lo devuelve. En la cláusula WHERE de la consulta externa, filtramos sólo las compras que tienen un valor mayor que el valor devuelto por la consulta interna.

Subconsulta correlacionada en la cláusula WHERE

SELECT id
FROM purchase p1
WHERE date > '2013-07-15'
  AND value > (
    SELECT AVG(value)
    FROM purchase p2
    WHERE p1.date = p2.date
  );

La consulta devuelve las compras posteriores al 15/07/2014 con un precio total superior al valor medio DESDE el mismo día.

El ejemplo equivalente, pero con tablas de unión.

SELECT  p1.id
FROM purchase p1, purchase p2
WHERE p1.date = p2.date
  AND p1.date > '2013-07-15'
GROUP BY p1.id
HAVING p1.value > AVG(p2.value);

Este ejemplo también se puede escribir como una sentencia SELECT con una subconsulta correlacionada en una cláusula FROM.

La subconsulta devuelve la tabla que contiene el valor medio de cada compra para cada día. Unimos este resultado con la tabla Purchase en la columna 'date' para comprobar la condición date > '15/07/2014′.

SELECT id
FROM
  purchase,
  (
    SELECT date, AVG(value) AS average_value
    FROM purchase
    WHERE date > '2013-07-15'
    GROUP BY date
  ) average
WHERE purchase.date  = average.date
  AND purchase.date  > '2013-07-15'
  AND purchase.value > average.average_value;

Normalmente, este tipo de subconsulta debe evitarse porque los índices no pueden utilizarse en una tabla temporal en memoria.

Subconsulta con EXISTS

SELECT
FROM
WHERE [NOT] EXISTS (subquery)

El operador EXISTS comprueba si la fila de la subconsulta coincide con alguna fila de la consulta externa. Si no hay datos que coincidan, el operador EXISTS devuelve FALSE.

Esta consulta devuelve todos los clientes que hicieron un pedido después del 10/07/2013.

SELECT id, company_name
FROM client
WHERE EXISTS(
  SELECT *
  FROM purchase
  WHERE client.id = purchase.client_id
  WHERE date > '2013-07-10'
);

Cuando se utiliza una subconsulta, el optimizador de consultas realiza pasos adicionales antes de utilizar los resultados DE la subconsulta. Si una consulta que contiene una subconsulta puede escribirse utilizando una unión, debería hacerse así. Las uniones suelen permitir al optimizador de consultas recuperar los datos de forma más eficiente.

Limitaciones de las subconsultas

Hay algunas limitaciones en el uso de subconsultas:

• En Oracle se pueden anidar hasta 255 niveles de subconsultas en una cláusula WHERE.
• En SQL Server se pueden anidar hasta 32 niveles.