Subconsultas y WITH

Una consulta dentro de otra ▸2:18:15

Una subconsulta es un SELECT metido dentro de otro. La consulta interna calcula algo (por ejemplo, la edad promedio de una ciudad) y la general lo usa como si fuera un valor fijo ▸2:19:23. Es divide y vencerás: consultas pequeñas que se entienden solas, ensambladas en una grande ▸2:20:31.

Usamos la tabla clientes de la clase 7, que en esta versión trae la columna edad. En la base del video había un cliente por ciudad; en la nuestra hay varios, así que los promedios por ciudad sí resumen algo.

Primero, la consulta interna ▸2:22:34

SELECT ciudad, AVG(edad) AS edad_promedio FROM clientes GROUP BY ciudad;

▶ EjecutarReiniciar baseCtrl + Enter

Subconsulta en el WHERE ▸2:25:29

Los clientes cuya edad es mayor o igual al promedio de Quito. La subconsulta va entre paréntesis y tiene que devolver un solo valor ▸2:26:02:

SELECT id_cliente, nombre, ciudad, edad FROM clientes WHERE edad >= ( SELECT AVG(edad) FROM clientes WHERE ciudad = 'Quito' );

▶ EjecutarReiniciar baseCtrl + Enter

En el video se filtra Quito con GROUP BY ciudad HAVING ciudad = 'Quito' ▸2:26:35; el resultado es el mismo, y con WHERE la base no tiene que agrupar todas las ciudades para quedarse con una. Lo importante: el valor ya no está escrito a mano, depende de los datos ▸2:27:40. Si mañana llegan clientes nuevos a Quito, la consulta se ajusta sola.

Si la subconsulta devuelve una lista, se combina con IN ▸2:28:43; lo vemos a fondo en la clase 9. Y no hay límite de anidación: una subconsulta puede tener otra dentro ▸2:29:16.

Columnas calculadas ▸2:32:16

Una condición en el SELECT crea una columna nueva que vale 1 si se cumple y 0 si no ▸2:32:49:

SELECT id_cliente, nombre, edad, edad > 22 AS mayor_22 FROM clientes WHERE edad < 30;

▶ EjecutarReiniciar baseCtrl + Enter

Subconsulta en el FROM ▸2:33:20

El resultado de una consulta también puede ser la tabla sobre la que consultas:

SELECT mayor_22, COUNT(*) AS clientes, AVG(edad) AS edad_promedio FROM ( SELECT id_cliente, edad, edad > 22 AS mayor_22 FROM clientes WHERE edad < 30 ) GROUP BY mayor_22;

▶ EjecutarReiniciar baseCtrl + Enter

Funciona, pero con siete subconsultas anidadas nadie lo entiende, ni siquiera quien lo escribió, seis meses después ▸2:34:26.

WITH: tablas temporales con nombre ▸2:38:28

WITH le da nombre a una subconsulta y la deja arriba, antes de la consulta principal. Esa «tabla» solo existe mientras corre la consulta ▸2:38:59:

WITH jovenes AS ( SELECT id_cliente, edad, edad > 22 AS mayor_22 FROM clientes WHERE edad < 30 ) SELECT mayor_22, COUNT(*) AS clientes, AVG(edad) AS edad_promedio FROM jovenes GROUP BY mayor_22;

▶ EjecutarReiniciar baseCtrl + Enter

Es lo mismo de arriba, pero se lee de arriba abajo. Un WITH sin un SELECT después da error: le dijiste qué tabla crear, no qué hacer con ella ▸2:40:06. Y cuando algo falla, cada bloque se puede revisar por separado ▸2:42:19.

Retos de esta clase

1. Los clientes (id, nombre y edad) cuya edad supera el promedio de toda la tabla.

SELECT AVG(edad) FROM clientes;

▶ EjecutarComprobarReiniciar baseVer soluciónCtrl + Enter

2. Con WITH, las ciudades cuyo promedio de edad supera el promedio general. Una sola columna: la ciudad.

WITH por_ciudad AS ( -- calcula aquí el promedio por ciudad ) SELECT ...;

▶ EjecutarComprobarReiniciar baseVer soluciónCtrl + Enter

3. El cliente más joven de cada ciudad: ciudad, nombre y edad. (Pista: una subconsulta que calcule la edad mínima por ciudad, y un join con clientes.)

SELECT ciudad, MIN(edad) FROM clientes GROUP BY ciudad;

▶ EjecutarComprobarReiniciar baseVer soluciónCtrl + Enter