Subconsultas y CTE, o cómo pensar en pisos

Con lo de los siete capítulos anteriores ya puedes contestar casi cualquier pregunta de negocio. El problema empieza cuando la pregunta tiene dos pisos.

"¿Cuánto gasta en promedio un cliente de cada ciudad?" no es un AVG. Es dos cuentas encadenadas: primero cuánto gastó cada cliente, y sobre eso, el promedio por ciudad. Y para eso necesitas meter una consulta dentro de otra.

Este capítulo va de las dos formas de hacerlo, y de por qué una de ellas te va a cambiar la vida cuando la consulta pase de veinte líneas 🌟

Una consulta dentro de otra

La más simple: una subconsulta que devuelve un solo valor y que usas como si fuera un número escrito a mano.

SELECT ROUND(AVG(monto), 2) AS el_promedio FROM pedidos;

el_promedio
-----------
610.14

SELECT COUNT(*) AS por_encima_del_promedio
FROM pedidos
WHERE monto > (SELECT AVG(monto) FROM pedidos);

por_encima_del_promedio
-----------------------
421

Eso de dentro del paréntesis se calcula primero, da 610,14, y la consulta de fuera lo usa como si hubieras escrito WHERE monto > 610.14.

La ventaja no es que sea más corto. Es que no hay que actualizarlo nunca: mañana entran cien pedidos más, el promedio cambia solo y la consulta sigue siendo correcta. Un número escrito a mano se pudre; una subconsulta no.

Y fíjate en el 421 de 873 pedidos con monto: casi la mitad está por encima del promedio. Eso pasa cuando los datos están repartidos parejos. Si te sale que solo el 5% supera el promedio, ahí hay unos poquitos pedidos gigantes tirando de la media, y entonces la media no era la medida que buscabas.

Subconsultas que devuelven una lista

SELECT c.nombre, c.ciudad
FROM clientes c
WHERE c.id IN (SELECT id_cliente FROM pedidos WHERE monto > 1300)
ORDER BY c.id;

nombre                   ciudad
-----------------------  --------
Autoservicio Norte 007   Chiclayo
Bodega San Martin 030    Arequipa
Cafe del Puerto 060      Arequipa
Market Central 064       Chiclayo
Market Central 099       Chiclayo
  COMERCIAL ROJAS 102    Piura
Bodega San Martin 103    Arequipa

Aquí la subconsulta devuelve muchas filas, y IN pregunta "¿este id está en esa lista?". Siete clientes hicieron alguna vez un pedido de más de S/1.300.

Se lee de adentro hacia afuera y es de las cosas que más rápido se vuelven naturales 💛

La trampa del NOT IN, que es de las peores del libro

Ya sabemos del capítulo 7 que hay exactamente un cliente que nunca compró. Vamos a buscarlo con NOT IN, que es lo que sale solo.

SELECT COUNT(*) AS nunca_compraron
FROM clientes
WHERE id NOT IN (SELECT id_cliente FROM pedidos);

nunca_compraron
---------------
0

Cero. Y sabemos que es uno 😳

El culpable es el NULL, otra vez. La tabla pedidos tiene 24 filas con id_cliente nulo, así que la lista que devuelve la subconsulta contiene nulos. Y id NOT IN (5, 8, NULL) significa "id <> 5 Y id <> 8 Y id <> NULL". Esa última no es ni verdadera ni falsa, es desconocida, y una condición desconocida hace que toda la fila se caiga.

Resultado: con un solo NULL en la lista, NOT IN no devuelve nunca ninguna fila. Y no da error. Te entrega un cero limpio que parece una buena noticia.

Esto es igual en los cuatro motores, que para variar se ponen de acuerdo en lo peor. Hay dos arreglos.

SELECT COUNT(*) AS nunca_compraron
FROM clientes
WHERE id NOT IN (SELECT id_cliente FROM pedidos WHERE id_cliente IS NOT NULL);

nunca_compraron
---------------
1

SELECT COUNT(*) AS nunca_compraron
FROM clientes c
WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.id_cliente = c.id);

nunca_compraron
---------------
1

El primero limpia la lista a mano. El segundo usa NOT EXISTS, que no tiene este problema nunca porque no compara valores: pregunta "¿existe al menos una fila que cumpla esto?" y a eso el NULL no le hace nada.

Mi regla, y te la regalo: usa NOT EXISTS siempre y olvídate de NOT IN. Es igual de legible, es igual o más rápido en los cuatro motores, y no te va a mentir un martes por la tarde.

Ese SELECT 1 de dentro llama la atención y es a propósito: a EXISTS no le importa qué devuelvas, solo si devuelve algo. Puedes poner SELECT 1, SELECT * o SELECT 'pollito' y da exactamente lo mismo 🐣

Una subconsulta en el FROM

Volvamos a la pregunta del principio: cuánto gasta en promedio un cliente de cada ciudad.

SELECT ciudad, ROUND(AVG(soles), 2) AS gasto_medio_por_cliente
FROM (
    SELECT c.id, c.ciudad, SUM(p.monto) AS soles
    FROM clientes c
    JOIN pedidos p ON p.id_cliente = c.id
    GROUP BY c.id, c.ciudad
) AS por_cliente
GROUP BY ciudad
ORDER BY gasto_medio_por_cliente DESC;

ciudad    gasto_medio_por_cliente
--------  -----------------------
Chiclayo  4639.18
Arequipa  4610.57
Piura     4465.22
Lima      4249.9
Cusco     4060.39
Trujillo  3773.57

La de dentro arma una tabla temporal con una fila por cliente y su total. La de fuera la trata como si fuera una tabla normal y le hace el promedio por ciudad. Eso se llama tabla derivada.

Y mira qué distinto se ve el negocio así: por ticket promedio (capítulo 7) mandaba Piura; por gasto anual de cada cliente manda Chiclayo. Son dos preguntas distintas y dan dos respuestas distintas, las dos correctas 📊

WITH, o cómo escribir de arriba abajo

La consulta de arriba funciona, pero se lee al revés: lo primero que pasa está en el medio, entre paréntesis. Con dos pisos se aguanta; con cuatro es ilegible.

WITH arregla eso. Es la misma consulta, dada vuelta.

WITH por_cliente AS (
    SELECT c.id, c.ciudad, c.segmento, SUM(p.monto) AS soles, COUNT(p.id) AS pedidos
    FROM clientes c
    JOIN pedidos p ON p.id_cliente = c.id
    GROUP BY c.id, c.ciudad, c.segmento
)
SELECT ciudad, COUNT(*) AS clientes, ROUND(AVG(soles), 2) AS gasto_medio
FROM por_cliente
GROUP BY ciudad
ORDER BY gasto_medio DESC;

ciudad    clientes  gasto_medio
--------  --------  -----------
Chiclayo  25        4639.18
Arequipa  22        4610.57
Piura     24        4465.22
Lima      15        4249.9
Cusco     16        4060.39
Trujillo  17        3773.57

Mismos números, y ahora se lee como se piensa: primero calculo el gasto de cada cliente y le pongo nombre, después uso ese nombre.

Eso se llama CTE (common table expression) y en cristiano es "una tabla temporal con nombre, que vive solo mientras dura la consulta". Es lo que más me cambió la forma de escribir SQL, y por eso lo pongo tan pronto en el libro.

Y ojo a un detalle de la salida: Cusco dice 16 clientes y en el capítulo 6 dijimos 17. No es un error: aquí hay un JOIN normal, así que Comercial Rojas 120 no entra porque nunca compró. Cuando un número no cuadra con otro capítulo, casi siempre es que la pregunta no era la misma 🔍

Los CTE se encadenan, y ahí es donde brilla.

WITH por_cliente AS (
    SELECT c.id, c.nombre, SUM(p.monto) AS soles
    FROM clientes c JOIN pedidos p ON p.id_cliente = c.id
    GROUP BY c.id, c.nombre
),
promedio AS (
    SELECT AVG(soles) AS media FROM por_cliente
)
SELECT COUNT(*) AS por_encima
FROM por_cliente, promedio
WHERE por_cliente.soles > promedio.media;

por_encima
----------
58

Dos CTE, y el segundo usa al primero. 58 de los 119 clientes que compraron están por encima del gasto medio.

Fíjate en la coma del FROM por_cliente, promedio: es un CROSS JOIN del capítulo 7, y aquí es correcto justamente porque promedio tiene una sola fila. Multiplicar por uno no multiplica nada.

Una subconsulta en el SELECT

SELECT c.nombre,
       (SELECT COUNT(*) FROM pedidos p WHERE p.id_cliente = c.id) AS pedidos
FROM clientes c
ORDER BY pedidos DESC
LIMIT 3;

nombre                  pedidos
----------------------  -------
Distribuidora Paz 058   17
Autoservicio Norte 008  14
Bodega San Martin 048   14

Esta se llama correlacionada porque la de dentro mira a la de fuera: ese c.id cambia en cada fila. O sea que la subconsulta se ejecuta una vez por cliente, 120 veces.

Con 120 filas ni lo notas. Con dos millones, esa misma consulta se cuelga y el JOIN con GROUP BY del capítulo 7 hace lo mismo en un segundo. Cuando una subconsulta en el SELECT se puede escribir como JOIN, escríbela como JOIN.

Y ahora el error que tiene que salir sí o sí.

SELECT (SELECT id, nombre FROM clientes LIMIT 1) AS x;

OperationalError: sub-select returns 2 columns - expected 1

sub-select returns 2 columns - expected 1. Una subconsulta que va en el SELECT o al lado de un = tiene que devolver exactamente una columna, y como mucho una fila. Es de los pocos sitios donde SQLite se pone estricto, y menos mal 🙌

Apilar resultados: UNION

Los JOIN pegan tablas de lado. UNION las apila una debajo de otra.

SELECT id_cliente FROM pedidos WHERE canal = 'Web' AND id_cliente IS NOT NULL
EXCEPT
SELECT id_cliente FROM pedidos WHERE canal = 'WhatsApp' AND id_cliente IS NOT NULL
ORDER BY id_cliente
LIMIT 5;

id_cliente
----------
3
4
16
24
25

Ese EXCEPT es "lo del primero que no esté en el segundo": los clientes que compran por Web y nunca por WhatsApp. Son 16 en total, y esa es una lista concreta para el equipo que quiere mover gente al canal barato 📞

Las cuatro operaciones de conjuntos son:

• ➕ UNION: apila y quita duplicados.
• ➕ UNION ALL: apila y no quita nada. Es más rápida, y es la que quieres cuando sabes que no hay repetidos.
• ✖️ INTERSECT: solo lo que está en las dos.
• ➖ EXCEPT: lo del primero que no esté en el segundo.

Las cuatro piden que las dos consultas tengan el mismo número de columnas y en el mismo orden. Los nombres no importan: manda la primera.

El CTE recursivo, que parece magia

Un CTE puede llamarse a sí mismo. Suena raro y sirve para dos cosas muy concretas: recorrer jerarquías (el jefe del jefe del jefe) y fabricar listas que no existen en ninguna tabla, que es la que vas a usar tú.

WITH RECURSIVE meses(mes) AS (
    SELECT '2026-01'
    UNION ALL
    SELECT STRFTIME('%Y-%m', DATE(mes || '-01', '+1 month'))
    FROM meses
    WHERE mes < '2026-06'
)
SELECT mes FROM meses;

mes
-------
2026-01
2026-02
2026-03
2026-04
2026-05
2026-06

Se lee en tres partes:

1. 1️⃣ El primer escalón, SELECT '2026-01'. De dónde arranca.
2. 2️⃣ El escalón siguiente, que se calcula a partir del anterior. Aquí, sumarle un mes.
3. 3️⃣ Cuándo parar, ese WHERE mes < '2026-06'. Si te lo olvidas, la consulta no termina nunca.

¿Para qué quieres una lista de meses? Para el problema más común de todo reporte: los meses sin ventas no salen en un GROUP BY, porque no hay filas que agrupar. Con esta lista y un LEFT JOIN del capítulo 7, salen con cero.

WITH meses(mes) AS (
    SELECT '2026-01' UNION ALL SELECT '2026-02' UNION ALL SELECT '2026-03'
)
SELECT m.mes, COUNT(p.id) AS pedidos
FROM meses m
LEFT JOIN pedidos p ON STRFTIME('%Y-%m', p.fecha) = m.mes
GROUP BY m.mes
ORDER BY m.mes;

mes      pedidos
-------  -------
2026-01  51
2026-02  40
2026-03  48

Aquí los tres meses tienen pedidos, así que el ejemplo se ve tonto. El día que uno tenga cero, tu gráfico va a mostrar el hueco en vez de saltárselo, que es la diferencia entre un reporte honesto y uno que disimula 🌟

Ejercicios

Siete sobre tienda.db. Intenta antes de abrir 💛

SELECT id, monto FROM pedidos WHERE monto = (SELECT MAX(monto) FROM pedidos);

id  monto
--  -------
25  1399.98

SELECT COUNT(*) AS clientes_con_dos_direcciones
FROM clientes c
WHERE (SELECT COUNT(*) FROM direcciones d WHERE d.id_cliente = c.id) >= 2;

clientes_con_dos_direcciones
----------------------------
33

SELECT c.nombre, c.ciudad, ROUND(SUM(p.monto), 2) AS soles
FROM clientes c JOIN pedidos p ON p.id_cliente = c.id
GROUP BY c.id, c.nombre, c.ciudad
HAVING SUM(p.monto) > (SELECT AVG(monto) * 10 FROM pedidos)
ORDER BY soles DESC
LIMIT 4;

nombre                      ciudad    soles
--------------------------  --------  --------
Distribuidora Paz 058       Lima      10131.72
Almacenes Vega 067          Piura     9516.06
Market Central 087          Chiclayo  8148.56
Restaurante Miraflores 093  Trujillo  8119.95

SELECT COUNT(*) AS en_los_dos FROM (
SELECT id_cliente FROM pedidos WHERE canal = 'Web' AND id_cliente IS NOT NULL
INTERSECT
SELECT id_cliente FROM pedidos WHERE canal = 'WhatsApp' AND id_cliente IS NOT NULL
);

en_los_dos
----------
89

WITH grandes AS (
    SELECT id_cliente, COUNT(*) AS pedidos_grandes
    FROM pedidos
    WHERE monto > 800 AND id_cliente IS NOT NULL
    GROUP BY id_cliente
)
SELECT c.nombre, c.ciudad, g.pedidos_grandes
FROM grandes g
JOIN clientes c ON c.id = g.id_cliente
ORDER BY g.pedidos_grandes DESC, c.id
LIMIT 5;

nombre                  ciudad    pedidos_grandes
----------------------  --------  ---------------
Mayorista Peru 027      Cusco     7
  MARKET CENTRAL 069    Trujillo  6
Cafe del Puerto 092     Piura     6
Minimarket El Sol 001   Piura     5
Almacenes Vega 067      Piura     5

SELECT COUNT(*) AS con_not_in
FROM clientes
WHERE id NOT IN (SELECT id_cliente FROM pedidos);

con_not_in
----------
0

SELECT COUNT(*) AS con_not_exists
FROM clientes c
WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.id_cliente = c.id);

con_not_exists
--------------
1

SELECT COUNT(*) AS con_left_join
FROM clientes c
LEFT JOIN pedidos p ON p.id_cliente = c.id
WHERE p.id IS NULL;

con_left_join
-------------
1

-- Se escribe IGUAL en PostgreSQL, MySQL, SQL Server y SQLite.
SELECT c.id, c.nombre
FROM clientes c
WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.id_cliente = c.id);

Lo que te llevas

• 🔢 Una subconsulta escalar en el WHERE te deja poner un listón que se recalcula solo. Nunca escribas el número a mano.
• 💣 NOT IN con un NULL en la lista devuelve cero filas, sin error, en los cuatro motores. Usa NOT EXISTS y listo.
• 🧱 Una subconsulta en el FROM es una tabla derivada. Ponle alias siempre: tres de los cuatro motores te lo exigen.
• 📖 WITH es la misma consulta escrita de arriba abajo. Está en los cuatro y es lo que hace legible una consulta larga.
• 🐌 Una subconsulta correlacionada en el SELECT corre una vez por fila. Con pocas filas da igual; con muchas, pásala a JOIN.
• ➕ UNION quita duplicados y UNION ALL no. INTERSECT y EXCEPT no existen en MySQL anterior a la 8.0.31.
• 🔁 WITH RECURSIVE fabrica listas que no están en ninguna tabla, como el calendario de meses para que no falten los ceros. En SQL Server va sin la palabra RECURSIVE.

En el capítulo 9 vienen las funciones de ventana, que es lo que te deja calcular un ranking, un acumulado o un "cuánto creció respecto al mes pasado" sin perder el detalle. Es la herramienta que más separa a alguien que sabe SQL de alguien que lo usa.

Que tengas lindo día! 🌸