Subconsultas y CTE en SQL: WITH y consultas recursivas

Lección 5 de 8. Hay preguntas que necesitan dos pasos: «¿qué productos cuestan más que la media?» exige calcular primero la media. Las subconsultas meten una consulta dentro de otra, y WITH las ordena de arriba abajo para que se lean como una receta. Al final, la CTE recursiva, que recorre un organigrama entero sin saber cuántos niveles tiene.

Una consulta dentro de otra

¿Qué productos son más caros que la media? Hacen falta dos pasos: calcular la media y comparar cada precio con ella. Una subconsulta los junta en uno: se escribe entre paréntesis donde iría un valor, y SQL la resuelve primero.

SELECT nombre, precio
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos)
ORDER BY precio DESC;

Ábrela en el editor y cámbiala →

resultado+------------------+---------+
|      nombre      | precio  |
+------------------+---------+
| Portátil Nova 16 | 1849    |
| Portátil Aero 14 | 1299.99 |
| Monitor 27" 4K   | 429.9   |
+------------------+---------+

Esta subconsulta es escalar: devuelve un solo valor, una fila y una columna, así que se puede usar en cualquier sitio donde iría un número. También en el SELECT, para poner la media al lado de cada precio:

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

Ábrela en el editor y cámbiala →

resultado+------------------+---------+----------------+
|      nombre      | precio  | sobre_la_media |
+------------------+---------+----------------+
| Portátil Nova 16 | 1849    | 1541.89        |
| Portátil Aero 14 | 1299.99 | 992.88         |
| Monitor 27" 4K   | 429.9   | 122.79         |
| Router Wi-Fi 7   | 249     | -58.11         |
+------------------+---------+----------------+

IN y EXISTS: subconsultas que devuelven una lista

Si la subconsulta devuelve una columna con varias filas, se usa con IN: «el valor está en esta lista». ¿Qué clientes han comprado algún portátil? Los pedidos con portátiles salen de detalle y productos, y los clientes, de esos pedidos:

SELECT nombre, pais
FROM clientes
WHERE id IN (
  SELECT p.cliente_id
  FROM pedidos AS p
  JOIN detalle AS d    ON d.pedido_id = p.id
  JOIN productos AS pr ON pr.id = d.producto_id
  WHERE pr.categoria_id = 1
)
ORDER BY nombre;

Ábrela en el editor y cámbiala →

resultado+----------------+--------+
|     nombre     |  pais  |
+----------------+--------+
| Ana Torres     | México |
| Diego Martínez | México |
| Jorge Ramírez  | México |
| Luis Fernández | España |
| Paula Morales  | España |
+----------------+--------+

Con un JOIN también se podría, pero un cliente que hubiera comprado dos portátiles saldría dos veces. IN solo pregunta si está o no, y cada cliente sale una vez.

EXISTS es parecido: es verdadero si la subconsulta devuelve al menos una fila. Su contrario, NOT EXISTS, es otra forma de encontrar lo que falta, como los clientes sin pedidos:

SELECT c.nombre
FROM clientes AS c
WHERE NOT EXISTS (
  SELECT 1 FROM pedidos AS p WHERE p.cliente_id = c.id
);

Ábrela en el editor y cámbiala →

resultado+--------------+
|    nombre    |
+--------------+
| Tomás Ortega |
+--------------+

Para «lo que falta», prefiere NOT EXISTS o el LEFT JOIN ... IS NULL de la lección 4 antes que NOT IN: si la lista de un NOT IN contiene un solo NULL, la consulta no devuelve nada, sin ningún error. Es de las rarezas más traicioneras.

Subconsultas correlacionadas

La subconsulta del NOT EXISTS tiene algo especial: usa c.id, una columna de la consulta de fuera. Es una subconsulta correlacionada, y conceptualmente se vuelve a calcular para cada fila de fuera. Así se compara cada producto con la media de su propia categoría, no con la general:

SELECT pr.nombre, pr.categoria_id, pr.precio
FROM productos AS pr
WHERE pr.precio > (
  SELECT AVG(otro.precio)
  FROM productos AS otro
  WHERE otro.categoria_id = pr.categoria_id
)
ORDER BY pr.categoria_id;

Ábrela en el editor y cámbiala →

resultado+--------------------+--------------+--------+
|       nombre       | categoria_id | precio |
+--------------------+--------------+--------+
| Portátil Nova 16   | 1            | 1849   |
| Monitor 27" 4K     | 2            | 429.9  |
| Teclado mecánico   | 3            | 89.5   |
| Webcam 4K          | 3            | 139    |
| SSD 1 TB           | 4            | 94.9   |
| Disco externo 4 TB | 4            | 119    |
| Router Wi-Fi 7     | 5            | 249    |
+--------------------+--------------+--------+

Los dos productos sin categoría no salen: para ellos, otro.categoria_id = pr.categoria_id compara NULL con NULL, que no es verdadero, la media queda en NULL y ninguna comparación con NULL pasa el filtro.

Subconsultas en el FROM

Una subconsulta también puede hacer de tabla en el FROM: su resultado se trata como una tabla temporal, a la que hay que darle un alias. Sirve para calcular en dos pasos, por ejemplo el importe medio de los pedidos (primero el importe de cada uno, después la media):

SELECT COUNT(*)              AS pedidos,
       ROUND(AVG(importe), 2) AS importe_medio,
       ROUND(MAX(importe), 2) AS el_mayor
FROM (
  SELECT pedido_id, SUM(cantidad * precio_unitario) AS importe
  FROM detalle
  GROUP BY pedido_id
) AS importes;

Ábrela en el editor y cámbiala →

resultado+---------+---------------+----------+
| pedidos | importe_medio | el_mayor |
+---------+---------------+----------+
| 21      | 724.36        | 3638.78  |
+---------+---------------+----------+

Son 21 y no 22 porque el pedido cancelado no tiene líneas y no aparece en detalle.

WITH: subconsultas con nombre

Cuando las subconsultas se anidan, la consulta se lee de dentro afuera y cuesta seguirla. WITH define antes cada paso con un nombre, una CTE (common table expression), y después se usa como si fuera una tabla. La misma media de antes, más el número de pedidos por encima de ella:

WITH importes AS (
  SELECT pedido_id, SUM(cantidad * precio_unitario) AS importe
  FROM detalle
  GROUP BY pedido_id
),
media AS (
  SELECT AVG(importe) AS valor FROM importes
)
SELECT ROUND(media.valor, 2) AS importe_medio,
       COUNT(*)              AS pedidos_por_encima
FROM importes, media
WHERE importes.importe > media.valor;

Ábrela en el editor y cámbiala →

resultado+---------------+--------------------+
| importe_medio | pedidos_por_encima |
+---------------+--------------------+
| 724.36        | 6                  |
+---------------+--------------------+

Se lee de arriba abajo como una receta: primero los importes, después su media, y por último los pedidos que la superan. Una CTE puede usar las anteriores, y la consulta final puede usar todas. Además, ahora sí se puede filtrar por un cálculo con su nombre, cosa que en el WHERE de una consulta simple solo deja SQLite (lo vimos en la lección 3).

WITH RECURSIVE: recorrer una jerarquía

En la lección 4 unimos empleados consigo misma para ver el jefe de cada uno, pero así solo se sube un nivel. Para recorrer el organigrama entero, de la directora hasta el último empleado, sin saber cuántos niveles hay, se usa una CTE recursiva. Tiene dos partes unidas por UNION ALL: la base, por donde empieza, y la parte recursiva, que se une con lo que ya se ha encontrado para dar el nivel siguiente. Se repite hasta que una vuelta no añade nada.

WITH RECURSIVE organigrama AS (
  -- la base: quien no tiene jefe
  SELECT id, nombre, puesto, 0 AS nivel, nombre AS ruta
  FROM empleados
  WHERE jefe_id IS NULL

  UNION ALL

  -- la parte recursiva: los empleados cuyo jefe ya está en organigrama
  SELECT e.id, e.nombre, e.puesto, o.nivel + 1, o.ruta || ' > ' || e.nombre
  FROM empleados AS e
  JOIN organigrama AS o ON e.jefe_id = o.id
)
SELECT nivel, nombre, puesto, ruta
FROM organigrama
ORDER BY ruta;

Ábrela en el editor y cámbiala →

resultado+-------+---------------+-----------------+-------------------------------------------+
| nivel |    nombre     |     puesto      |                   ruta                    |
+-------+---------------+-----------------+-------------------------------------------+
| 0     | Elena Vargas  | Directora       | Elena Vargas                              |
| 1     | Marta Ruiz    | Jefa de almacén | Elena Vargas > Marta Ruiz                 |
| 2     | Nadia Fuentes | Mozo de almacén | Elena Vargas > Marta Ruiz > Nadia Fuentes |
| 2     | Óscar Lara    | Mozo de almacén | Elena Vargas > Marta Ruiz > Óscar Lara    |
| 1     | Raúl Peña     | Jefe de ventas  | Elena Vargas > Raúl Peña                  |
| 2     | Carla Medina  | Vendedora       | Elena Vargas > Raúl Peña > Carla Medina   |
| 2     | Hugo Salas    | Vendedor        | Elena Vargas > Raúl Peña > Hugo Salas     |
| 2     | Iván Soto     | Vendedor        | Elena Vargas > Raúl Peña > Iván Soto      |
+-------+---------------+-----------------+-------------------------------------------+

La columna ruta se va alargando en cada vuelta con el operador ||, que en SQL estándar une textos. Ordenar por ella deja a cada empleado debajo de su jefe, como en un índice.

PostgreSQL 17ERROR:  recursive query "organigrama" column 5 has type character varying(60) in non-recursive term but type character varying overall
LINE 3:   SELECT id, nombre, puesto, 0 AS nivel, nombre AS ruta
                                                 ^
HINT:  Cast the output of the non-recursive term to the correct type.

SELECT 'Elena' || ' > ' || 'Raúl' AS ruta;

MySQL 8.4+------+
| ruta |
+------+
|    0 |
+------+

Las CTE recursivas sirven para cualquier estructura en árbol (categorías y subcategorías, carpetas, piezas de un producto) y también para generar series, como todos los días de un mes. Hay más casos en el ejemplo de jerarquías recursivas.

Resumen

Retos

SELECT pr.categoria_id, pr.nombre, pr.precio
FROM productos AS pr
WHERE pr.precio = (
  SELECT MAX(otro.precio)
  FROM productos AS otro
  WHERE otro.categoria_id = pr.categoria_id
)
ORDER BY pr.categoria_id;

resultado+--------------+--------------------+--------+
| categoria_id |       nombre       | precio |
+--------------+--------------------+--------+
| 1            | Portátil Nova 16   | 1849   |
| 2            | Monitor 27" 4K     | 429.9  |
| 3            | Webcam 4K          | 139    |
| 4            | Disco externo 4 TB | 119    |
| 5            | Router Wi-Fi 7     | 249    |
+--------------+--------------------+--------+

WITH gasto AS (
  SELECT p.cliente_id, SUM(d.cantidad * d.precio_unitario) AS total
  FROM pedidos AS p
  JOIN detalle AS d ON d.pedido_id = p.id
  WHERE p.estado = 'entregado'
  GROUP BY p.cliente_id
)
SELECT c.nombre, ROUND(g.total, 2) AS total
FROM gasto AS g
JOIN clientes AS c ON c.id = g.cliente_id
WHERE g.total > (SELECT AVG(total) FROM gasto)
ORDER BY g.total DESC;

resultado+----------------+---------+
|     nombre     |  total  |
+----------------+---------+
| Jorge Ramírez  | 3638.78 |
| Luis Fernández | 3088.7  |
| Diego Martínez | 2122.49 |
| Ana Torres     | 1944.68 |
+----------------+---------+

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

resultado+-------+
| valor |
+-------+
| 1     |
| 2     |
| 3     |
| 4     |
| 5     |
+-------+

Todas las consultas de esta lección se ejecutaron en SQLite 3.46.1 sobre la base de ejemplo «tienda» antes de publicarla, y los resultados están copiados de esa ejecución; las diferencias con PostgreSQL 17 y MySQL 8.4 se comprobaron en esos motores. ¿Quieres probar algo? Usa el editor de SQL, que ya trae la base cargada.