Subconsultas en SQL

Una subconsulta es una consulta metida dentro de otra, entre paréntesis. Sirve para responder preguntas que necesitan dos pasos: “¿qué pedidos superan la media?” exige primero calcular la media y después comparar cada pedido con ella. En esta lección verás dónde se pueden poner las subconsultas, qué tipos hay y cuándo es mejor usar un JOIN o una CTE con WITH.

Usaremos las tablas clientes y pedidos del curso (las tienes en la introducción).

Subconsulta escalar en WHERE

Una subconsulta escalar devuelve un único valor: una fila y una columna. Puedes usarla donde usarías un número o un texto.

Primero, la media de los pedidos:

SELECT AVG(importe) FROM pedidos;

Resultado: 54,348. Ahora podrías escribir WHERE importe > 54.348, pero ese número cambia cada vez que entra un pedido. Mejor deja que lo calcule la base de datos:

SELECT id, cliente_id, importe
FROM pedidos
WHERE importe > (SELECT AVG(importe) FROM pedidos);

id	cliente_id	importe
2	3	120.50
4	2	60.00

La base de datos ejecuta primero la consulta de dentro (la media) y luego usa ese valor en la de fuera.

¿Por qué no vale WHERE importe > AVG(importe)? Porque WHERE trabaja fila a fila y no puede usar funciones agregadas. La subconsulta resuelve el problema: calcula el agregado aparte.

Otro ejemplo, el cliente de más edad:

SELECT nombre, edad
FROM clientes
WHERE edad = (SELECT MAX(edad) FROM clientes);

nombre	edad
Marta Ruiz	45

Subconsulta en SELECT

Una subconsulta escalar también puede ser una columna calculada. Cuánto se aleja cada pedido de la media:

SELECT id, importe,
       ROUND(importe - (SELECT AVG(importe) FROM pedidos), 2) AS diferencia
FROM pedidos;

id	importe	diferencia
1	45.00	-9.35
2	120.50	66.15
3	12.99	-41.36
4	60.00	5.65
5	33.25	-21.10

Y el número de pedidos de cada cliente:

SELECT c.nombre,
       (SELECT COUNT(*) FROM pedidos p WHERE p.cliente_id = c.id) AS num_pedidos
FROM clientes c;

nombre	num_pedidos
Ana García	2
Luis Pérez	1
Marta Ruiz	1
Pablo Díaz	0
Eva Martín	1

Fíjate en que la subconsulta usa c.id, que viene de la consulta de fuera. Es una subconsulta correlacionada; la explicamos más abajo.

Subconsulta en FROM: tablas derivadas

El resultado de una consulta es también una tabla. Por eso puedes ponerla en el FROM y consultarla como si existiera. Se llama tabla derivada y necesita un alias: en MySQL es obligatorio (PostgreSQL también lo exigía antes de la versión 16), así que ponlo siempre.

Total vendido por ciudad, quedándonos solo con las que superan 50 €:

SELECT t.ciudad, t.total
FROM (
  SELECT c.ciudad, SUM(p.importe) AS total
  FROM clientes c
  JOIN pedidos p ON p.cliente_id = c.id
  GROUP BY c.ciudad
) AS t
WHERE t.total > 50;

ciudad	total
Bilbao	60.00
Santander	178.49

(Aquí también valdría HAVING, pero la tabla derivada te deja trabajar por pasos.) Donde sí es imprescindible es para agregar un agregado. ¿Cuántos pedidos hace de media un cliente que compra?

SELECT ROUND(AVG(num), 2) AS media_pedidos
FROM (
  SELECT cliente_id, COUNT(*) AS num
  FROM pedidos
  GROUP BY cliente_id
) AS por_cliente;

media_pedidos
1.25

No puedes escribir AVG(COUNT(*)): hay que contar primero en una consulta y hacer la media en otra.

IN y NOT IN con subconsulta

Cuando la subconsulta devuelve una columna con varias filas, no puedes usar =. Usa IN, que comprueba si el valor está en la lista:

-- Clientes con algún pedido de más de 50 €
SELECT nombre
FROM clientes
WHERE id IN (SELECT cliente_id FROM pedidos WHERE importe > 50);

nombre
Luis Pérez
Marta Ruiz

-- Clientes sin pedidos
SELECT nombre
FROM clientes
WHERE id NOT IN (SELECT cliente_id FROM pedidos);

nombre
Pablo Díaz

La trampa de NOT IN con NULL

Imagina una tabla de devoluciones donde una fila no tiene cliente:

CREATE TABLE devoluciones (id INT, cliente_id INT, motivo VARCHAR(50));
INSERT INTO devoluciones VALUES
  (1, 2, 'Talla incorrecta'),
  (2, NULL, 'Paquete sin remitente');

SELECT nombre
FROM clientes
WHERE id NOT IN (SELECT cliente_id FROM devoluciones);

Resultado: ninguna fila. ¿Por qué? id NOT IN (2, NULL) equivale a id <> 2 AND id <> NULL, y cualquier comparación con NULL da “desconocido”, nunca verdadero (lo viste en valores NULL). Soluciones: añadir WHERE cliente_id IS NOT NULL en la subconsulta o, mejor, usar NOT EXISTS, que no tiene este problema.

EXISTS y NOT EXISTS

EXISTS no mira qué devuelve la subconsulta, solo si devuelve alguna fila. Por eso se suele escribir SELECT 1: el valor da igual.

SELECT nombre
FROM clientes c
WHERE EXISTS (
  SELECT 1 FROM pedidos p
  WHERE p.cliente_id = c.id AND p.importe > 50
);

nombre
Luis Pérez
Marta Ruiz

Con NOT EXISTS, el caso de las devoluciones funciona bien:

SELECT nombre
FROM clientes c
WHERE NOT EXISTS (SELECT 1 FROM devoluciones d WHERE d.cliente_id = c.id);

nombre
Ana García
Marta Ruiz
Pablo Díaz
Eva Martín

Estos dos patrones se llaman semi-join y anti-join (los tienes también en más JOIN).

Consejo: para “los que no tienen…”, usa NOT EXISTS en lugar de NOT IN. Es igual de rápido o más, y no te la juega con los NULL.

ANY y ALL

Comparan un valor con todos los valores de una subconsulta:

• > ANY (...): mayor que alguno (al menos uno).
• > ALL (...): mayor que todos.

-- Pedidos más caros que TODOS los de Ana (45.00 y 12.99)
SELECT id, importe
FROM pedidos
WHERE importe > ALL (SELECT importe FROM pedidos WHERE cliente_id = 1);

Funciona en MySQL y PostgreSQL, pero SQLite no tiene ANY ni ALL: si lo pruebas, da syntax error. Por suerte, casi siempre se reescriben con MAX y MIN:

Con ANY / ALL	Equivale a
x > ALL (subconsulta)	x > (SELECT MAX(...))
x > ANY (subconsulta)	x > (SELECT MIN(...))
x = ANY (subconsulta)	x IN (subconsulta)

SELECT id, importe
FROM pedidos
WHERE importe > (SELECT MAX(importe) FROM pedidos WHERE cliente_id = 1);

id	importe
2	120.50
4	60.00

Cuidado: si la subconsulta no devuelve filas, > ALL es verdadero para todo y > (SELECT MAX(...)) no devuelve nada, porque MAX da NULL. Es un caso raro, pero existe.

Subconsultas correlacionadas, paso a paso

Hasta ahora casi todas las subconsultas eran independientes: podías ejecutarlas solas. Una subconsulta correlacionada usa una columna de la consulta de fuera, así que se evalúa una vez por cada fila exterior.

¿Cuál es el pedido más caro de cada cliente?

SELECT p.id, p.cliente_id, p.importe
FROM pedidos p
WHERE p.importe = (
  SELECT MAX(p2.importe)
  FROM pedidos p2
  WHERE p2.cliente_id = p.cliente_id
);

id	cliente_id	importe
1	1	45.00
2	3	120.50
4	2	60.00
5	5	33.25

Lo que hace la base de datos, fila a fila:

1. Pedido 1, cliente 1. Calcula el máximo de los pedidos del cliente 1: 45.00. ¿45.00 = 45.00? Sí, se queda.
2. Pedido 2, cliente 3. Máximo del cliente 3: 120.50. Se queda.
3. Pedido 3, cliente 1. Máximo del cliente 1: 45.00. ¿12.99 = 45.00? No, fuera.
4. Pedido 4 y pedido 5: son los únicos de sus clientes, se quedan.

Hacen falta dos alias (p y p2) sobre la misma tabla para distinguir “el pedido que estoy mirando” de “los pedidos con los que lo comparo”.

Otro ejemplo: clientes mayores que la media de edad de su ciudad (Bilbao 22, Madrid 23, Santander 39,5):

SELECT c.nombre, c.ciudad, c.edad
FROM clientes c
WHERE c.edad > (
  SELECT AVG(c2.edad) FROM clientes c2 WHERE c2.ciudad = c.ciudad
);

nombre	ciudad	edad
Marta Ruiz	Santander	45
Eva Martín	Madrid	29

CTE con WITH: subconsultas legibles

Cuando las subconsultas se anidan, la consulta se vuelve difícil de leer de dentro hacia fuera. Una CTE (Common Table Expression) te deja ponerle nombre a una subconsulta al principio y usarla después como una tabla:

WITH totales AS (
  SELECT cliente_id, SUM(importe) AS total
  FROM pedidos
  GROUP BY cliente_id
)
SELECT c.nombre, t.total
FROM totales t
JOIN clientes c ON c.id = t.cliente_id
WHERE t.total > (SELECT AVG(total) FROM totales)
ORDER BY t.total DESC;

nombre	total
Marta Ruiz	120.50

Se lee de arriba abajo: “primero calculo los totales por cliente; luego me quedo con los que superan la media de esos totales” (67,935 €). Además, totales se usa dos veces sin repetir código. Puedes definir varias CTE separadas por comas: WITH a AS (...), b AS (...) SELECT ....

Nota: MySQL admite WITH desde la versión 8.0. PostgreSQL, SQLite y SQL Server lo tienen desde hace años.

WITH RECURSIVE

Una CTE recursiva se llama a sí misma. Tiene una parte inicial y una parte que se repite hasta que deja de producir filas:

WITH RECURSIVE numeros(n) AS (
  SELECT 1                                   -- parte inicial
  UNION ALL
  SELECT n + 1 FROM numeros WHERE n < 5      -- parte que se repite
)
SELECT n FROM numeros;

n
1
2
3
4
5

Se usa para generar series (todos los días de un mes) y, sobre todo, para recorrer jerarquías: la cadena completa de jefes de un empleado o un árbol de categorías. No olvides la condición de parada (WHERE n < 5), o la consulta no terminaría nunca.

Subconsulta o JOIN

Muchas preguntas se pueden resolver de las dos formas. Estas dos consultas devuelven los mismos clientes:

SELECT nombre FROM clientes
WHERE id IN (SELECT cliente_id FROM pedidos WHERE importe > 50);

SELECT DISTINCT c.nombre
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
WHERE p.importe > 50;

Criterios para elegir:

• Si necesitas columnas de las dos tablas en el resultado (nombre del cliente e importe), usa JOIN.
• Si solo filtras por algo de la otra tabla, IN o EXISTS expresan mejor la idea y no duplican filas.
• Si comparas con un agregado (media, máximo), necesitas una subconsulta o una CTE.
• En rendimiento, los gestores modernos suelen convertir una forma en la otra por dentro. Escribe primero la versión más clara.

Errores frecuentes

• Subconsulta escalar que devuelve varias filas. WHERE id = (SELECT cliente_id FROM pedidos) da error en MySQL (Subquery returns more than 1 row) y en PostgreSQL. SQLite no avisa: coge la primera fila y sigue, lo que es peor. Usa IN si esperas varias filas.
• Olvidar el alias de la tabla derivada. FROM (SELECT ...) WHERE ... da error en MySQL: escribe FROM (SELECT ...) AS t.
• NOT IN con NULL en la subconsulta: devuelve cero filas. Usa NOT EXISTS.
• Confundir los alias en una correlacionada. Si escribes WHERE p2.cliente_id = p2.cliente_id, la condición siempre es cierta y comparas con el máximo de toda la tabla.
• Anidar cinco niveles. Si te pasa, pásate a WITH.

Resumen

Dónde / cómo	Devuelve	Ejemplo
Escalar en WHERE	1 valor	importe > (SELECT AVG(importe) ...)
Escalar en SELECT	1 valor por fila	(SELECT COUNT(*) ...) AS num_pedidos
En FROM (tabla derivada)	Una tabla, con alias	FROM (SELECT ...) AS t
IN / NOT IN	Una columna	id IN (SELECT cliente_id ...)
EXISTS / NOT EXISTS	Verdadero o falso	WHERE EXISTS (SELECT 1 ...)
ANY / ALL	Comparación con una lista	No existen en SQLite
Correlacionada	Se evalúa por cada fila exterior	WHERE p2.cliente_id = p.cliente_id
CTE (WITH)	Subconsulta con nombre	WITH totales AS (...) SELECT ...

En la siguiente lección, UNION, INTERSECT y EXCEPT, verás cómo juntar los resultados de varias consultas en uno solo.

SELECT nombre
FROM clientes
WHERE id NOT IN (SELECT cliente_id FROM devoluciones);

SELECT COUNT(*)
FROM (SELECT cliente_id FROM pedidos GROUP BY cliente_id) AS t;