Subconsultas y CTEs

Algunas preguntas necesitan dos pasos: "productos más caros que el promedio" requiere primero calcular el promedio y luego comparar. Las subconsultas (una consulta dentro de otra) y las CTEs (la cláusula WITH) te permiten resolver problemas complejos dividiéndolos en piezas pequeñas y legibles.

Concepto

Una subconsulta es como pedirle a un ayudante un dato antes de hacer tu trabajo: "Averigua el precio promedio y dímelo"; con esa respuesta, tú filtras los productos.

Una CTE (Common Table Expression) es lo mismo pero con nombre y al principio de la consulta, como preparar los ingredientes en tazones etiquetados antes de cocinar.

Subconsulta escalar (devuelve un solo valor)

-- Productos más caros que el promedio (880.375)
SELECT nombre, precio
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos)
ORDER BY precio DESC;

nombre	precio
Monitor 24 pulgadas	3499.00
Teclado mecánico	1250.00
Audífonos Bluetooth	899.00

También puede ir en el SELECT:

SELECT
  nombre,
  precio,
  ROUND(precio - (SELECT AVG(precio) FROM productos), 2) AS diferencia_vs_promedio
FROM productos
ORDER BY diferencia_vs_promedio DESC
LIMIT 3;

nombre	precio	diferencia_vs_promedio
Monitor 24 pulgadas	3499.00	2618.63
Teclado mecánico	1250.00	369.63
Audífonos Bluetooth	899.00	18.63

Error común

Si una subconsulta escalar devuelve más de una fila, falla con "subquery returns more than 1 row". Asegúrate de que use una agregación o un filtro por clave única.

Subconsulta con IN (devuelve una lista)

-- Clientes que compraron algún producto de Electrónica
SELECT nombre
FROM clientes
WHERE id IN (
  SELECT pe.cliente_id
  FROM pedidos pe
  JOIN detalle_pedido d ON d.pedido_id = pe.id
  JOIN productos pr     ON pr.id = d.producto_id
  WHERE pr.categoria_id = 1
)
ORDER BY nombre;

nombre
Ana López
Carlos Ruiz
Jorge Díaz

EXISTS y NOT EXISTS

EXISTS pregunta "¿hay al menos una fila?". Se detiene al encontrar la primera, así que suele ser muy eficiente.

-- Categorías que tienen productos
SELECT c.nombre
FROM categorias c
WHERE EXISTS (
  SELECT 1 FROM productos p WHERE p.categoria_id = c.id
);
-- Electrónica, Libros, Hogar

-- Categorías SIN productos
SELECT c.nombre
FROM categorias c
WHERE NOT EXISTS (
  SELECT 1 FROM productos p WHERE p.categoria_id = c.id
);

nombre
Deportes

Buena práctica

Para "lo que no tiene..." prefiere NOT EXISTS sobre NOT IN: no tiene el problema de los NULL (recuerda que NOT IN con un NULL en la lista devuelve vacío) y los optimizadores lo manejan muy bien.

Subconsultas correlacionadas

Una subconsulta correlacionada usa columnas de la consulta exterior, así que se evalúa (conceptualmente) una vez por cada fila.

-- El producto más caro de cada categoría
SELECT p.nombre, p.categoria_id, p.precio
FROM productos p
WHERE p.precio = (
  SELECT MAX(p2.precio)
  FROM productos p2
  WHERE p2.categoria_id = p.categoria_id   -- ← referencia a la fila exterior
)
ORDER BY p.categoria_id;

nombre	categoria_id	precio
Monitor 24 pulgadas	1	3499.00
Libro Aprende SQL	2	349.00
Lámpara de escritorio	3	459.00

(El Cable USB-C no sale porque NULL = NULL no es verdadero.)

-- Último pedido de cada cliente
SELECT cl.nombre,
       (SELECT MAX(pe.fecha) FROM pedidos pe WHERE pe.cliente_id = cl.id) AS ultimo_pedido
FROM clientes cl
ORDER BY cl.id;

nombre	ultimo_pedido
Ana López	2024-06-01
Carlos Ruiz	2024-05-10
María Pérez	2024-06-15
Jorge Díaz	2024-06-20
Lucía Gómez	NULL

Subconsulta en FROM (tabla derivada)

El resultado de una subconsulta puede usarse como si fuera una tabla. Debe llevar alias.

-- Ticket promedio: primero el total de cada pedido, luego el promedio de esos totales
SELECT ROUND(AVG(t.total), 2) AS ticket_promedio
FROM (
  SELECT pedido_id, SUM(cantidad * precio_unitario) AS total
  FROM detalle_pedido
  GROUP BY pedido_id
) AS t;

ticket_promedio
1797.60

(1597 + 1847 + 3499 + 1146 + 899 = 8988; 8988 / 5 = 1797.60.)

CTEs: la cláusula WITH

Las tablas derivadas anidadas se vuelven ilegibles rápido. Una CTE pone cada paso arriba y con nombre:

WITH totales AS (
  SELECT pedido_id, SUM(cantidad * precio_unitario) AS total
  FROM detalle_pedido
  GROUP BY pedido_id
)
SELECT ROUND(AVG(total), 2) AS ticket_promedio
FROM totales;

Mismo resultado, pero se lee de arriba abajo como una receta. Funciona en MySQL 8+, MariaDB 10.2+, PostgreSQL y SQLite.

Varias CTEs encadenadas

Pregunta de negocio: "¿Qué clientes gastaron más que el promedio de gasto por cliente (sin contar cancelados)?"

WITH totales_pedido AS (                 -- paso 1: total de cada pedido válido
  SELECT pe.id, pe.cliente_id,
         SUM(d.cantidad * d.precio_unitario) AS total
  FROM pedidos pe
  JOIN detalle_pedido d ON d.pedido_id = pe.id
  WHERE pe.estado <> 'cancelado'
  GROUP BY pe.id, pe.cliente_id
),
gasto_cliente AS (                       -- paso 2: gasto por cliente
  SELECT cliente_id, SUM(total) AS gastado
  FROM totales_pedido
  GROUP BY cliente_id
),
promedio AS (                            -- paso 3: el promedio de gasto
  SELECT AVG(gastado) AS valor FROM gasto_cliente
)
SELECT cl.nombre, g.gastado              -- paso 4: comparar
FROM gasto_cliente g
JOIN clientes cl ON cl.id = g.cliente_id
WHERE g.gastado > (SELECT valor FROM promedio)
ORDER BY g.gastado DESC;

nombre	gastado
Ana López	5096.00

(Gastos: Ana 5096, Carlos 1847, María 1146 → promedio 2696.33; solo Ana lo supera.)

Buena práctica

Construye las consultas complejas por pasos: escribe y ejecuta la primera CTE sola, revisa el resultado, agrega la segunda, y así. Depurar un monstruo de 60 líneas de golpe es casi imposible.

CTEs recursivas

Una CTE recursiva se llama a sí misma. Sirve para recorrer jerarquías (categorías y subcategorías, organigramas, comentarios con respuestas) y generar series.

Estructura:

WITH RECURSIVE nombre AS (
  -- 1. Caso base (ancla): la(s) fila(s) inicial(es)
  SELECT ...
  UNION ALL
  -- 2. Paso recursivo: se une con la propia CTE
  SELECT ... FROM tabla JOIN nombre ON ...
)
SELECT * FROM nombre;

Generar una serie de fechas

-- Todos los días del 1 al 7 de junio de 2024
WITH RECURSIVE dias AS (
  SELECT CAST('2024-06-01' AS DATE) AS dia
  UNION ALL
  SELECT CAST(dia + INTERVAL '1 day' AS DATE)   -- MySQL: dia + INTERVAL 1 DAY
  FROM dias
  WHERE dia < '2024-06-07'
)
SELECT dia FROM dias;

Uso real: un reporte de ventas por día que incluya los días sin ventas (LEFT JOIN de la serie contra los pedidos).

-- PostgreSQL (en MySQL cambia el INTERVAL)
WITH RECURSIVE dias AS (
  SELECT CAST('2024-06-01' AS DATE) AS dia
  UNION ALL
  SELECT CAST(dia + INTERVAL '1 day' AS DATE) FROM dias WHERE dia < '2024-06-07'
)
SELECT d.dia, COUNT(pe.id) AS pedidos
FROM dias d
LEFT JOIN pedidos pe ON pe.fecha = d.dia
GROUP BY d.dia
ORDER BY d.dia;

dia	pedidos
2024-06-01	1
2024-06-02	0
2024-06-03	0
2024-06-04	0
2024-06-05	0
2024-06-06	0
2024-06-07	0

Recorrer una jerarquía de categorías

Imagina que la tienda crece y necesita subcategorías. Creamos una tabla aparte para el ejemplo:

CREATE TABLE categorias_arbol (
  id INT PRIMARY KEY,
  nombre VARCHAR(50) NOT NULL,
  padre_id INT NULL,
  FOREIGN KEY (padre_id) REFERENCES categorias_arbol(id)
);

INSERT INTO categorias_arbol (id, nombre, padre_id) VALUES
  (1, 'Electrónica', NULL),
  (2, 'Computación', 1),
  (3, 'Teclados', 2),
  (4, 'Monitores', 2),
  (5, 'Audio', 1),
  (6, 'Hogar', NULL);

WITH RECURSIVE arbol AS (
  -- ancla: categorías raíz
  SELECT id, nombre, padre_id, 1 AS nivel,
         CAST(nombre AS VARCHAR(500)) AS ruta
  FROM categorias_arbol
  WHERE padre_id IS NULL
  UNION ALL
  -- recursión: hijos de lo que ya está en el árbol
  SELECT c.id, c.nombre, c.padre_id, a.nivel + 1,
         CAST(CONCAT(a.ruta, ' > ', c.nombre) AS VARCHAR(500))
  FROM categorias_arbol c
  JOIN arbol a ON c.padre_id = a.id
)
SELECT id, nivel, ruta FROM arbol ORDER BY ruta;

id	nivel	ruta
1	1	Electrónica
5	2	Electrónica > Audio
2	2	Electrónica > Computación
4	3	Electrónica > Computación > Monitores
3	3	Electrónica > Computación > Teclados
6	1	Hogar

MySQL y CAST a VARCHAR

En MySQL usa CAST(... AS CHAR(500)) en lugar de VARCHAR(500). El CAST es necesario porque la columna toma el tamaño del caso base y la ruta crece en cada nivel.

Recursión infinita

Si los datos tienen un ciclo (A es padre de B y B de A), la CTE nunca termina. Protégete con un tope: WHERE a.nivel < 20 en la parte recursiva. MySQL corta en 1000 iteraciones (cte_max_recursion_depth).

¿Subconsulta, CTE o JOIN?

Situación	Recomendación
Comparar con un valor agregado (promedio, máximo)	Subconsulta escalar o CTE
"Existe / no existe" relación	EXISTS / NOT EXISTS
Varios pasos de cálculo	CTEs encadenadas
Traer columnas de otra tabla	JOIN
Jerarquías, series	CTE recursiva

En rendimiento, los optimizadores modernos suelen convertir IN, EXISTS y JOINs en planes equivalentes. Escribe lo más legible y mide con EXPLAIN si algo es lento.

Ejercicios

1. Muestra los productos cuyo stock es menor que el stock promedio.
2. Usando NOT EXISTS, encuentra los productos que nunca se han vendido.
3. Con una CTE, calcula el total de cada pedido y muestra solo los que superan el ticket promedio (1797.60).
4. Muestra, para cada cliente, el número de pedidos usando una subconsulta correlacionada en el SELECT.
5. Escribe una CTE recursiva que genere los números del 1 al 10.

Ver solución

-- 1 (promedio de stock = 275 / 8 = 34.375)
SELECT nombre, stock FROM productos
WHERE stock < (SELECT AVG(stock) FROM productos)
ORDER BY stock;

nombre	stock
Monitor 24 pulgadas	5
Teclado mecánico	10
Lámpara de escritorio	15
Audífonos Bluetooth	25
Cien años de soledad	30

-- 2
SELECT p.nombre FROM productos p
WHERE NOT EXISTS (SELECT 1 FROM detalle_pedido d WHERE d.producto_id = p.id);
-- Cable USB-C

-- 3
WITH totales AS (
  SELECT pedido_id, SUM(cantidad * precio_unitario) AS total
  FROM detalle_pedido
  GROUP BY pedido_id
)
SELECT pedido_id, total
FROM totales
WHERE total > (SELECT AVG(total) FROM totales)
ORDER BY total DESC;

pedido_id	total
3	3499.00
2	1847.00

-- 4
SELECT cl.nombre,
       (SELECT COUNT(*) FROM pedidos pe WHERE pe.cliente_id = cl.id) AS pedidos
FROM clientes cl
ORDER BY cl.id;

-- 5
WITH RECURSIVE numeros AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numeros WHERE n < 10
)
SELECT n FROM numeros;

Resumen

• Una subconsulta escalar devuelve un valor; úsala para comparar con promedios o máximos.
• IN (subconsulta) filtra contra una lista; EXISTS / NOT EXISTS verifica si hay relación (y evita líos con NULL).
• Las correlacionadas usan columnas de la consulta exterior: útiles para "el máximo de cada grupo".
• Una subconsulta en FROM necesita alias; una CTE (WITH) hace lo mismo de forma más legible.
• WITH RECURSIVE recorre jerarquías y genera series. Pon siempre una condición de parada.