Subconsultas correlacionadas

En resumen: Una subconsulta correlacionada usa una columna de la consulta exterior, así que se calcula de nuevo para cada fila, mientras que una subconsulta en FROM convierte su resultado en una tabla nueva.

Problema

¿Caro comparado con qué?

De la lección anterior sabes qué productos están por encima del precio promedio de la tienda:

SQLbase de datos: tienda

SELECT name, category_id, price FROM products
WHERE price > (SELECT AVG(price) FROM products);

SELECT name, category_id, price FROM products WHERE price > (SELECT AVG(price) FROM products);

▶ EjecutarRestaurar la consultaBase nueva

Todo lo de la cocina es «caro», y ningún pan lo es. Pero el pan no se compara con una sartén. La gerencia quiere saber qué productos son caros para su propia categoría: el queso frente a los lácteos, el café frente a las bebidas.

La idea

Subconsulta correlacionada

Una subconsulta puede usar una columna de la fila que la consulta exterior está mirando en ese momento:

SQLbase de datos: tienda

SELECT name, price,
  (SELECT ROUND(AVG(p2.price), 2) FROM products p2
   WHERE p2.category_id = p.category_id) AS category_avg
FROM products p;

SELECT name, price, (SELECT ROUND(AVG(p2.price), 2) FROM products p2 WHERE p2.category_id = p.category_id) AS category_avg FROM products p;

▶ EjecutarRestaurar la consultaBase nueva

p es el producto de fuera, p2 son los productos de la subconsulta. La condición p2.category_id = p.category_id dice «los productos de su categoría». Para el pan blanco la subconsulta calcula el promedio de pan y bollería (1,60 €), para el queso blanco el promedio de los lácteos (4,66 €).

Una subconsulta así se llama correlacionada: depende de la fila exterior y se calcula de nuevo para cada fila. La subconsulta común de la lección anterior da la misma respuesta para todas las filas.

No funcionaría sin los nombres cortos p y p2: ambos son la tabla products, y la base de datos tiene que saber cuál category_id es el de fuera y cuál el de dentro.

La idea

En WHERE

Ahora la pregunta de la gerencia: ¿qué productos están por encima del promedio de su categoría?

SQLbase de datos: tienda

SELECT name, category_id, price FROM products p
WHERE price > (
  SELECT AVG(p2.price) FROM products p2
  WHERE p2.category_id = p.category_id
);

SELECT name, category_id, price FROM products p WHERE price > ( SELECT AVG(p2.price) FROM products p2 WHERE p2.category_id = p.category_id );

▶ EjecutarRestaurar la consultaBase nueva

Nueve productos, al menos uno de cada categoría: el pan integral está por encima del promedio de pan y bollería, y el detergente en polvo por encima del promedio de limpieza.

Falta la tarjeta de regalo, aunque cuesta 20 €. Su categoría es NULL, p2.category_id = NULL no es verdadero para ninguna fila, el promedio es NULL y la comparación es desconocida. El mismo NULL de la lección de NULL, solo que mejor escondido.

Pruébalo

El último pedido

Muestra para cada cliente su nombre y la fecha de su último pedido (columna last_order). Los clientes sin pedidos también deben estar en la lista.

SQLbase de datos: tienda

SELECT name FROM customers c;

SELECT name FROM customers c;

Comprobar▶ Ejecutar

Más precisamente, para curiosos

En principio una subconsulta correlacionada se ejecuta una vez por cada fila exterior: 20 clientes, 20 veces. Las bases de datos suelen reescribirla en algo más rápido, pero con tablas grandes conviene compararla con JOIN y GROUP BY. Cómo ver lo que hace la base de datos está en el módulo sobre velocidad.

Una subconsulta correlacionada también da el número del último pedido, no solo su fecha. En el módulo sobre resumir viste que SQLite lo permite con MAX y una columna libre, mientras que otras bases de datos no. Esta es la forma que funciona en todas partes:

SQLbase de datos: tienda

SELECT o.customer_id, o.id, o.ordered_at
FROM orders o
WHERE o.ordered_at = (
  SELECT MAX(o2.ordered_at) FROM orders o2
  WHERE o2.customer_id = o.customer_id
);

SELECT o.customer_id, o.id, o.ordered_at FROM orders o WHERE o.ordered_at = ( SELECT MAX(o2.ordered_at) FROM orders o2 WHERE o2.customer_id = o.customer_id );

▶ EjecutarRestaurar la consultaBase nueva

18 filas, una por cada cliente con pedidos. Si un cliente tiene dos pedidos en su último día, saldrán los dos.

Míralo

El valor promedio de un pedido

Contabilidad pide el valor promedio de un pedido. El primer intento:

SQLbase de datos: tienda

SELECT ROUND(AVG(quantity * unit_price), 2) AS avg_order
FROM order_items;

SELECT ROUND(AVG(quantity * unit_price), 2) AS avg_order FROM order_items;

▶ EjecutarRestaurar la consultaBase nueva

1. AVG(quantity * unit_price) devolvió 12,92 €, y la subconsulta en FROM devolvió 32,73 €. ¿Cuál es el valor promedio de un pedido?

12,92 €: AVG es exactoNinguno: necesitas SUM en lugar de AVG32,73 €: primero se suman las líneas de cada pedido y después se calcula el promedio

Comprobar

Hacen falta dos pasos: primero el valor de cada pedido, luego el promedio de esos valores. El primer paso ya lo sabes hacer:

SQLbase de datos: tienda

SELECT order_id, ROUND(SUM(quantity * unit_price), 2) AS total
FROM order_items
GROUP BY order_id;

SELECT order_id, ROUND(SUM(quantity * unit_price), 2) AS total FROM order_items GROUP BY order_id;

▶ EjecutarRestaurar la consultaBase nueva

La idea

Subconsulta en FROM

El resultado de cada consulta es una tabla. Por eso puede ir en FROM, donde escribes el nombre de una tabla:

SQLbase de datos: tienda

SELECT ROUND(AVG(total), 2) AS avg_order
FROM (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
) AS t;

SELECT ROUND(AVG(total), 2) AS avg_order FROM ( SELECT order_id, SUM(quantity * unit_price) AS total FROM order_items GROUP BY order_id ) AS t;

▶ EjecutarRestaurar la consultaBase nueva

La consulta interior hace una tabla de 60 filas, una por pedido. La exterior la lee como cualquier otra tabla y calcula el promedio: 32,73 €. Esto se llama subconsulta en FROM (tabla derivada, derived table). El nombre después de AS es el que le das a la tabla nueva.

Más precisamente, para curiosos

SQLite no exige un nombre para una subconsulta en FROM, pero MySQL y PostgreSQL hasta la versión 16 lo exigen sin excepción. Dale siempre un nombre y la consulta funcionará en todas partes.

Pruébalo

Cuántos productos hay en un pedido

El almacén está planeando las cajas y pregunta: ¿cuántos productos distintos tiene un pedido en promedio? Redondea a dos decimales (columna avg_lines). El campo ya cuenta los productos de cada pedido.

SQLbase de datos: tienda

SELECT order_id, COUNT(*) AS lines
FROM order_items
GROUP BY order_id;

SELECT order_id, COUNT(*) AS lines FROM order_items GROUP BY order_id;

Comprobar▶ Ejecutar

Ejercicio

Dos tareas

1. Marketing quiere recuperar a los clientes que no han pedido desde el verano.

Muestra los nombres de los clientes cuyo último pedido es anterior al 1 de julio de 2026.

SQLbase de datos: tienda

Comprobar▶ Ejecutar

2. ¿Cuánto gasta en promedio un cliente en la tienda?

Calcula el importe promedio que un cliente ha gastado en sus pedidos, sin contar los cancelados, redondeado a dos decimales (columna avg_spent). Cuenta solo los clientes con al menos un pedido.

SQLbase de datos: tienda

Comprobar▶ Ejecutar

Test

Ponte a prueba

1. ¿En qué se diferencia una subconsulta correlacionada de una común?

Usa una columna de la fila de la consulta exterior y se calcula de nuevo para cada filaUsa JOINSiempre está en FROM

2. ¿Por qué la tarjeta de regalo no está entre los productos por encima del promedio de su categoría?

Porque es la más barataPorque su category_id es NULL y p2.category_id = NULL no es verdadero para ninguna filaPorque no hay existencias

3. ¿Qué es una subconsulta en FROM?

Otro nombre para JOINUna subconsulta que devuelve un solo númeroUna consulta cuyo resultado se usa como una tabla

4. ¿Salen los clientes sin pedidos con WHERE (SELECT MAX(o.ordered_at) …) < '2026-07-01'?

No: MAX es NULL para ellos, y NULL < fecha es desconocidoSí, arriba del todoDepende de la base de datos

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: Funciones de agregación: COUNT, SUM, AVG · GROUP BY: un informe por grupos · JOIN: dos tablas en una consulta · Subconsultas
• Continúa en: Un informe por pasos: WITH · Funciones de ventana · Claves naturales y desnormalización · Bases de datos columnares y series temporales
• Ejercicios sobre el tema: Subconsultas y ventanas