Las tres lecciones anteriores han respondido al qué: qué devuelve una subconsulta, si está correlacionada, si pregunta por existencia. Esta responde al dónde. Porque la misma subconsulta colocada en el SELECT, en el FROM o en el WHERE no hace lo mismo, no cuesta lo mismo y no admite lo mismo.

Y hay una cláusula que todavía no has usado y que es, con diferencia, la más potente: el FROM. Una subconsulta ahí se llama tabla derivada y permite algo que ninguna otra construcción del curso permitía: agregar un resultado ya agregado. Con ella calcularás por fin el ticket medio de 36,40 € desde su origen, cruzarás dos agregados de granularidad distinta para que los 727,95 € de producto y los 118,25 € de portes cuadren sin inflarse —el problema que arrastras desde el módulo 3— y conocerás LATERAL, la excepción que permite correlacionar una tabla derivada.

1. Tabla resumen: qué cambia según dónde la pongas
2. En el SELECT: la columna calculada
3. En el FROM: la tabla derivada
4. LATERAL: la tabla derivada que sí puede correlacionarse
5. En el WHERE y en el HAVING
6. En INSERT, UPDATE y DELETE
7. Tabla de decisión: dado un problema, dónde ponerla
8. Errores Comunes y Consejos
9. Ejercicios
10. Conclusión

Tres reglas se deducen de la tabla: en el SELECT solo cabe un valor (dos filas o dos columnas y la consulta revienta); en el FROM el alias es obligatorio, siempre, aunque no lo uses; y una tabla derivada no ve las demás tablas de su mismo FROM, que es la restricción que LATERAL levanta.

Una subconsulta escalar en el SELECT se comporta como una columna más. Ya la usaste en 07-02: aquí interesa su límite y su alternativa.

(4 primeras de 15 filas.) Los tres clientes sin pedidos cierran la lista con 0 y 0.00, gracias al COALESCE de 06-04.

Las tres propiedades que definen este uso: solo puede devolver un valor (si necesitas el total y el número de pedidos, hacen falta dos subconsultas, y cada una recorre pedidos por su cuenta); no filtra filas, los 15 clientes siguen ahí, comportándose como un LEFT JOIN sin serlo; y se ejecuta una vez por fila: dos subconsultas × 15 clientes = 30 ejecuciones.

Resultado idéntico, las mismas 15 filas con los mismos valores. Pero el trabajo es muy distinto:

Fíjate en la penúltima fila, que es el matiz importante: la versión con JOIN necesita COUNT(DISTINCT pe.id) porque el segundo LEFT JOIN multiplica las filas de cada pedido por sus líneas. Es exactamente el problema de 04-04, y la versión con subconsultas es inmune a él. Ninguna de las dos formas es mejor siempre: con una o dos columnas la subconsulta se lee mejor; a partir de tres, el GROUP BY gana por goleada. La discusión completa es 07-05.

Una subconsulta en el FROM produce una tabla derivada: un resultado intermedio que la consulta externa trata como una tabla real. Es la cláusula que desbloquea el patrón más útil del análisis: agregar dos veces.

Toda tabla derivada necesita nombre, aunque no lo utilices: sus columnas tienen que poder cualificarse (t.total), y sin nombre de tabla eso es imposible.

Nota de dialecto: PostgreSQL, MySQL, MariaDB y SQL Server exigen el alias; SQLite y Oracle permiten omitirlo. Escríbelo siempre: es portable y hace la consulta legible.

En 07-01 calculaste el ticket medio como SUM(importe) / COUNT(DISTINCT pedido_id). Es correcto, pero es un atajo: la formulación honesta es calcular el total de cada pedido y luego hacer la media de esos totales. Son dos agregaciones encadenadas, y solo una tabla derivada las permite.

Ahí están las cifras canónicas del curso, y ahora por construcción: 20 tickets, media de 36,40 €, facturación de 727,95 €; el ticket más barato es el pedido 20 de Camille y el más caro el 12 de Julien.

Lo que hace posible el resultado es que la tabla derivada cambia la granularidad: entra a 47 líneas y sale a 20 pedidos, y la consulta externa agrega sobre esas 20. Sin ella, AVG sobre las líneas daría los 15,49 € de importe medio de línea, que es otra cosa.

Una vez tienes la tabla derivada, la filtras como cualquier tabla:

(4 primeras de 6 filas; cierran los pedidos 9 de Tiago, 44,60 €, y 1 de Lucía, 42,10 €.) 6 pedidos de 20 superan el ticket medio. El filtro está en el WHERE de la consulta externa, no en un HAVING: para ella, t.total es una columna normal. Una tabla derivada convierte agregados en columnas corrientes, y eso simplifica muchísimo la escritura.

Este es el caso que el curso lleva pendiente desde el módulo 3. Los gastos de envío viven en pedidos (uno por pedido) y los importes en lineas_pedido (varias por pedido): sumarlos en la misma consulta con un JOIN daba 278,70 € de portes en lugar de 118,25 €, porque cada pedido se contaba tantas veces como líneas tenía. La solución es agregar cada cosa por su lado y unir los resultados ya agregados:

(3 primeras de 12 filas.) Y las columnas cuadran: sumadas las 12 filas dan 727,95 € de producto + 118,25 € de portes = 846,20 €, las tres cifras canónicas del curso. Ni un céntimo inflado.

La clave está en que cada derivada ya viene a granularidad de cliente —ped tiene 12 filas y lin también—, así que al unirlas cada pedido aporta sus portes una sola vez: el SUM se hizo antes del JOIN. La versión global cabe en tres líneas:

A partir de tres niveles, usa CTE. Dos tablas derivadas todavía se leen; con tres, o con una derivada dentro de otra dentro de otra, la sangría se come la pantalla. La solución es dar nombre a cada paso con WITH, y eso es 10-02: todo lo de esta sección se reescribe allí con la mitad de paréntesis.

Ya sabes que una tabla derivada no ve las demás tablas de su mismo FROM:

La palabra clave LATERAL levanta esa restricción: dice al motor "evalúa esta subconsulta una vez por cada fila de lo que hay a su izquierda". Es un for sobre la tabla anterior, y se combina con CROSS JOIN LATERAL o con LEFT JOIN LATERAL ... ON TRUE. Su caso natural es el top-N por grupo:

(6 de las 9 filas.) Las cuatro primeras categorías aportan sus dos productos más vendidos; Higiene personal solo tiene uno vendido (el cepillo, porque el desodorante nunca se vendió) y aporta una fila. Y Complementos no aparece, porque su subconsulta devuelve cero filas y CROSS JOIN LATERAL se comporta como un INNER JOIN; para verla con NULL se usa LEFT JOIN LATERAL (...) AS top ON TRUE, que da 10 filas. Ese ON TRUE no es un adorno: la sintaxis exige una condición de unión y la correlación ya está dentro, así que no queda nada que poner.

Nota de dialecto: LATERAL es estándar SQL:1999 y funciona en PostgreSQL 9.3+, MySQL 8.0.14+ y Oracle 12c+. En SQL Server el equivalente se llama CROSS APPLY (y OUTER APPLY para la versión con NULL), con la misma semántica y sin la palabra LATERAL. SQLite no lo soporta. Y para este mismo problema hay una tercera vía, a menudo mejor: ROW_NUMBER() OVER (PARTITION BY categoria_id ORDER BY unidades DESC) filtrando por <= 2, que es 10-03.

Es el territorio de 07-01 y 07-03: en el WHERE caben subconsultas escalares (> (SELECT AVG(...))), de lista (IN, ANY, ALL) y de existencia (EXISTS), correlacionadas o no; y si necesitas columnas de la tabla interna en el resultado, eso no es un WHERE, es un JOIN. El HAVING merece un ejemplo propio, porque su subconsulta compara el agregado de un grupo con un valor calculado sobre otro conjunto:

3 categorías de las 5 con ventas superan la facturación media por categoría, que es de 145,59 € (727,945 € entre las 5 categorías que han vendido algo). Hogar sostenible (88,58 €) e Higiene personal (31,50 €) se quedan por debajo, y Complementos ni siquiera llega al GROUP BY porque el INNER JOIN la descartó.

Fíjate en el divisor: COUNT(DISTINCT p2.categoria_id) cuenta 5, no 6. Para dividir entre las seis categorías del catálogo, el divisor tendría que salir de categorias, no de las ventas. La media depende de sobre qué la calculas.

El módulo 5 te enseñó las tres instrucciones; ahora puedes darles una subconsulta en cualquiera de sus cláusulas.

La segunda esconde la trampa más peligrosa de la lección. Sin el WHERE EXISTS final, los tres productos nunca vendidos recibirían el resultado de una subconsulta sin filas —es decir NULL— y precio es NOT NULL: la sentencia fallaría entera con null value in column "precio" violates not-null constraint. Si la columna admitiera nulos sería peor: se quedaría en NULL sin decir nada. La regla de 05-03 se amplía: en un UPDATE con subconsulta en el SET, el WHERE debe garantizar que esa subconsulta encuentra algo.

• Olvidar el alias de una tabla derivada. subquery in FROM must have an alias. Ponlo siempre, aunque no lo uses. Y referenciar otra tabla del mismo FROM desde una derivada da invalid reference to FROM-clause entry: para eso está LATERAL.
• Poner una subconsulta multifila en el SELECT. more than one row returned by a subquery used as an expression: ahí solo cabe un valor.
• Sumar gastos_envio después de unir con lineas_pedido. Es el error de 04-04 —278,70 € en lugar de 118,25 €—: agrega cada cosa por su lado en dos derivadas.
• Confundir el ticket medio (36,40 €, media de 20 pedidos) con el importe medio de línea (15,49 €, media de 47). Solo la tabla derivada da el primero. Y contar con COUNT(*) tras varios LEFT JOIN infla: cada pedido aparece tantas veces como líneas, así que COUNT(DISTINCT pe.id).
• Un UPDATE con subconsulta en el SET sin WHERE que la acote. Las filas sin coincidencia reciben NULL: o revienta la restricción, o corrompe los datos en silencio.
• Consejo: construye las derivadas de dentro hacia fuera. Escribe la subconsulta sola, ejecútala, comprueba cuántas filas y qué granularidad tiene, y solo entonces envuélvela: una derivada siempre se puede ejecutar aislada (salvo con LATERAL).
• Consejo: nómbrala por lo que contiene, no t1 y t2: ped, lin, ventas_por_cliente. Y cuenta las filas de cada nivel —47 líneas → 20 pedidos → 1 fila—: si un nivel no reduce lo que esperabas, el error está ahí y no en el de arriba.

Dirección quiere el ticket medio por país: para cada país, el número de pedidos, la facturación de producto y el ticket medio (media de los totales de sus pedidos). Usa una tabla derivada. Después responde: ¿sería distinto el resultado si calcularas SUM(importe) / COUNT(DISTINCT pedido_id) sin tabla derivada?

Un compañero quiere el informe "por cliente: pedidos, líneas, unidades, productos distintos y facturación" y ha empezado así:

1. ¿Cuántas ejecuciones de subconsulta supone tal como está, y cuántas si añade las dos columnas que faltan?
2. Reescríbelo con un LEFT JOIN y GROUP BY, cuidando el recuento de pedidos.
3. ¿Qué diferencia habrá en el resultado para los clientes 13, 14 y 15?

Marketing quiere, para cada cliente que haya comprado, su pedido más caro: id del pedido, fecha e importe. Escríbelo con LATERAL y explica por qué una tabla derivada normal no serviría.

Solución 1

3 + 3 + 14 = 20 pedidos y 156,48 + 137,77 + 433,70 = 727,95 €: cuadra con las cifras canónicas. Y la lectura confirma lo que ya viste en 07-01: los pedidos extranjeros son sensiblemente mayores —52,16 € y 45,92 € frente a 30,98 €—, coherente con unos portes de 9,90 € y 12,50 € que empujan a agrupar la compra.

Sobre la pregunta: aquí el resultado sería el mismo, porque AVG de los totales por pedido y SUM(importe) / COUNT(DISTINCT pedido_id) son aritméticamente idénticos cuando cada pedido pertenece a un solo país. La tabla derivada gana igual por dos motivos: se lee mucho mejor —dice literalmente "media de los totales de los pedidos"— y permite calcular lo que el atajo no puede, como MIN(t.total), MAX(t.total) o la mediana.

Solución 2

1. Las ejecuciones. Tres subconsultas × 15 clientes = 45; con las dos columnas que faltan, 75. Y las cinco recorren las mismas dos tablas con el mismo filtro: cinco veces el mismo trabajo. 2. La reescritura:

(3 de las 15 filas, a modo de muestra.) COUNT(DISTINCT pe.id) es obligatorio: con COUNT(pe.id) a secas, Lucía tendría 9 pedidos en vez de 3, uno por cada línea. Es exactamente la trampa de 04-04. En cambio COUNT(lp.id) sí va sin DISTINCT, porque cada línea es única.

3. Los clientes 13, 14 y 15 aparecen en las dos versiones con los mismos valores: 0 donde hay COUNT y 0.00 donde el COALESCE cubre el NULL de SUM. Ni la subconsulta en el SELECT ni el LEFT JOIN los eliminan; lo que sí los habría eliminado es un INNER JOIN, y por eso el LEFT no es negociable aquí.

Solución 3

(3 primeras de 12 filas.) Los clientes 13, 14 y 15 no aparecen: su subconsulta lateral devuelve cero filas y CROSS JOIN LATERAL los descarta, que es justo lo que pedía el enunciado. Con LEFT JOIN LATERAL ... ON TRUE saldrían las 15 filas, con NULL en las tres últimas.

Por qué una tabla derivada normal no sirve: necesitaríamos escribir WHERE pe.cliente_id = c.id dentro de ella, y una derivada corriente no ve c (invalid reference to FROM-clause entry for table "c"). Podrías rodearlo agregando por cliente y uniendo por MAX(total), pero eso obliga a un segundo JOIN para recuperar el id y la fecha del pedido ganador, y duplica filas si hay empate. LATERAL lo resuelve en una pasada porque el LIMIT 1 se aplica por cliente, y eso ninguna otra construcción del módulo puede hacerlo. La alternativa moderna es ROW_NUMBER() (10-03).

El dónde importa tanto como el qué:

• En el SELECT, una subconsulta escalar es una columna calculada: no filtra filas y se ejecuta una vez por fila. Con una o dos columnas se lee muy bien; a partir de tres gana el LEFT JOIN + GROUP BY, cuidando el COUNT(DISTINCT).
• En el FROM, una subconsulta es una tabla derivada y necesita alias obligatoriamente (subquery in FROM must have an alias). Es la única forma de agregar sobre un agregado: 47 líneas → 20 pedidos → ticket medio de 36,40 €, con mínimo de 22,60 € y máximo de 66,90 €.
• Dos derivadas de granularidad distinta resuelven el problema de los portes que arrastrabas desde el módulo 3: 727,95 € de producto + 118,25 € de portes = 846,20 €, sin inflar nada, porque cada SUM se hace antes del JOIN.
• LATERAL es la excepción que permite correlacionar una tabla derivada, y su caso natural es el top N por grupo: los dos productos más vendidos de cada categoría, con LIMIT aplicado por categoría. En SQL Server se llama CROSS APPLY; en SQLite no existe; y para rankings suele ser mejor una función de ventana (10-03).
• En WHERE y HAVING vale todo lo de 07-01 y 07-03; en UPDATE, una subconsulta en el SET exige un WHERE que garantice que encuentra algo, o escribirás nulos. Y a partir de tres niveles la legibilidad exige CTE (10-02).

Ya tienes todas las piezas: subconsultas escalares, de lista, correlacionadas, EXISTS, tablas derivadas y LATERAL. Y con ellas, un problema nuevo: casi todas las preguntas admiten ahora dos o tres escrituras distintas, y ninguna te dice cuál elegir. En la última lección del módulo, subconsultas o JOIN: cuál elegir, verás las cuatro grandes equivalencias enfrentadas, la diferencia real entre IN y un INNER JOIN —que no es de estilo, sino de número de filas—, la tabla definitiva de las cuatro formas de responder "qué no casa" y el criterio honesto para decidir: legibilidad primero, rendimiento medido después.