Tema 11: Subconsultas y CTEs: consultas que se leen como párrafos

Identificador: PF-011 | XP: 200 | Insignia: 🥇 Oro | Tier: Aprendiz

Tiempo estimado: 80-100 min | Pre-requisitos: PF-009, PF-010

🖼️ Imagen de inicio del tema

[Moka con pirámide de códices (subconsultas anidadas). Sofía, Don Carlos, Citlalli viendo los pergaminos como si fueran párrafos.]

🎯 Aprenderás

Subconsultas en WHERE, FROM, SELECT. EXISTS y NOT EXISTS. CTEs (Common Table Expressions) con `WITH`. Cuándo usar CTE vs subconsulta.

🏛️ Visión mexica — Por Citlalli

"Los tlacuilos no escribían un solo codex con toda la información. Escribían varios codex (tributos, altépetl, calendarios) y los REFERENCIABAN entre sí. Un codex podía decir 'ver Codex de Altépetl, página 23, Suchimilco'. Eso es una SUBCONSULTA: en lugar de duplicar información, la referencia.

Las CTEs (Common Table Expressions) son el equivalente moderno: defines un 'codex temporal' con un nombre, y luego lo referencias en tu query principal. Es como tener varios codex abiertos al mismo tiempo, cada uno con un nombre claro."

📚 Bloque 1: Subconsulta en WHERE

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

📚 Bloque 2: Subconsulta en FROM (tabla derivada)

-- Clientes con más de 2 pedidos
SELECT c.nombre, c2.num_pedidos
FROM clientes c
INNER JOIN (
    SELECT cliente_id, COUNT() AS num_pedidos
    FROM pedidos
    GROUP BY cliente_id
) c2 ON c.id = c2.cliente_id
WHERE c2.num_pedidos > 2;

📚 Bloque 3: EXISTS y NOT EXISTS

-- Clientes que SÍ tienen pedidos
SELECT c.nombre
FROM clientes c
WHERE EXISTS (
    SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id
);
-- Clientes que NO tienen pedidos
SELECT c.nombre
FROM clientes c
WHERE NOT EXISTS (
    SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id
);

📚 Bloque 4: CTEs — el estilo moderno

WITH clientes_activos AS (
    SELECT id, nombre FROM clientes WHERE activo = 1
),
pedidos_2025 AS (
    SELECT cliente_id, total FROM pedidos
    WHERE YEAR(fecha_pedido) = 2025
)
SELECT ca.nombre, SUM(p.total) AS total_2025
FROM clientes_activos ca
LEFT JOIN pedidos_2025 p ON ca.id = p.cliente_id
GROUP BY ca.nombre
ORDER BY total_2025 DESC;

Don Carlos: "CTEs son la forma MODERNA de escribir queries complejas. En lugar de subconsultas anidadas ilegibles, defines 'tablas temporales con nombre' y los usas. Un DBA senior prefiere CTEs sobre subconsultas anidadas SIEMPRE."

📚 Bloque 5: CTEs recursivos

WITH RECURSIVE categorias_hijas AS (
    SELECT id, nombre, padre_id FROM categorias WHERE id = 1
    UNION ALL
    SELECT c.id, c.nombre, c.padre_id
    FROM categorias c
    INNER JOIN categorias_hijas ch ON c.padre_id = ch.id
)
SELECT  FROM categorias_hijas;

🤖 AI Mission

Pídele: "Reescribe esta subconsulta anidada en 3 niveles usando CTEs para que sea más legible: [pegar subconsulta]"

⚠️ Errores típicos

Subconsulta que devuelve más de una fila cuando se espera una sola. Correlación de subconsultas mal escrita. Olvidar el alias de la subconsulta en FROM. No usar CTEs cuando la query es compleja.

🧪 LAB 11.1 — "Refactoriza con CTEs"

Reescribe estas queries con CTEs:

Clientes de CDMX con su gasto total. Productos sin ventas (usando NOT EXISTS). Top 5 ciudades por ingreso promedio. Categorías con más de 3 productos vendidos (jerarquía recursiva).

📝 Quest 11

¿Qué hace una subconsulta en WHERE? A) Cuenta. B) Filtra. C) Agrupa. D) Ordena. ¿EXISTS devuelve? A) Booleano. B) Número. C) String. D) NULL. ¿CTE es? A) Tabla temporal. B) Función. C) Tipo. D) Servidor. ¿WITH RECURSIVE? A) Repite. B) Auto-referencia. C) Error. D) Loop infinito. ¿Cuándo usar subconsulta en SELECT? A) Siempre. B) Para columnas calculadas de otras tablas. C) Nunca. D) Para filtros. ¿CTEs vs subconsultas? A) Iguales. B) CTEs más legibles. C) Subconsultas más rápidas. D) No hay diferencia. ¿Correlación? A) Subconsulta que referencia columnas externas. B) Error. C) Tipo de JOIN. D) Índice. ¿MySQL 8 soporta CTEs? A) Sí. B) No. C) Solo en MariaDB. D) Solo en PostgreSQL. ¿`SELECT 1` en EXISTS? A) Error. B) Convencional. C) Solo 1 fila. D) Constante. ¿Cuántas CTEs por query? A) 1. B) 5. C) Sin límite. D) Hasta 10.

¿Por qué CTEs son más legibles que subconsultas anidadas? Da un ejemplo de subconsulta correlacionada. ¿Cuándo NO usar CTEs?

✅ Respuestas

1-B, 2-A, 3-A, 4-B, 5-B, 6-B, 7-A, 8-A, 9-B, 10-C.

🏛️ Tras las huellas — Por Citlalli

"Los tlacuilos NO escribían un codex gigante con toda la información. Escribían varios codex referenciados entre sí. Esa práctica de REFERENCIAR (no duplicar) es la esencia de las bases de datos relacionales. Las CTEs son el equivalente moderno: una 'referencia con nombre' que se lee como un párrafo."

📓 Cierre

1. Lo más útil:

2. El refactor más complejo:

3. Una query que voy a reescribir con CTEs:

4. Cómo voy a aplicar CTEs en mi trabajo:

🚀 ¿Qué sigue?

Tema 12 — Window functions: ranking, LAG, LEAD y promedios móviles.