LATERAL

1. Qué es

LATERAL permite que una subconsulta del FROM vea las columnas de las tablas que están a su izquierda.

Suena pequeño. Es lo que hace posible una consulta que llevamos prometiendo desde el tema cross-join-y-self-join y que hasta ahora no sabíamos escribir: los N mejores de cada grupo.

Sin LATERAL, una subconsulta del FROM es una isla. Se calcula una vez, sin saber nada del resto de la consulta:

SELECT c.category_name, t.product_name
FROM categories c,
     (SELECT p.product_name FROM products p
      WHERE p.category_id = c.category_id LIMIT 3) t;

ERROR:  invalid reference to FROM-clause entry for table "c"
DETALLE:  There is an entry for table "c", but it cannot be referenced from this part of the query.
SUGERENCIA:  To reference that table, you must mark this subquery with LATERAL.

PostgreSQL te dice literalmente lo que falta. Añade LATERAL y la subconsulta pasa de ejecutarse una vez a ejecutarse una vez por cada fila de la izquierda, con c.category_id valiendo algo distinto en cada pasada.

Es una subconsulta correlacionada, pero en el FROM. Y ahí está la diferencia que lo cambia todo: una subconsulta correlacionada del SELECT (subconsultas-correlacionadas) devuelve un valor. Una LATERAL devuelve una tabla: varias filas, varias columnas, con ORDER BY y LIMIT propios.

2. Sintaxis

SELECT ...
FROM tabla_izquierda x
CROSS JOIN LATERAL (
    SELECT ... FROM otra WHERE otra.col = x.col ORDER BY ... LIMIT n
) alias;

Dos formas, y elegir mal es el error número uno del tema:

El ON true no es adorno. LEFT JOIN exige sintácticamente una condición, pero la condición real ya está dentro de la subconsulta (WHERE otra.col = x.col). ON true es la forma de decir "ya está emparejado, únelo".

También vale la forma con coma —FROM categories c, LATERAL (...) t— que equivale a CROSS JOIN LATERAL. Se ve mucho en código antiguo.

Regla de lectura: LATERAL solo mira a la izquierda. FROM a, LATERAL (…b…), LATERAL (…c…): el segundo bloque ve a y el primero; el primero no ve el segundo. El orden en el FROM, que casi siempre da igual, aquí importa.

3. El caso que justifica el tema: los N mejores de cada grupo

El producto más vendido de cada categoría no se puede sacar con GROUP BY. GROUP BY colapsa el grupo a una fila de agregados; no sabe devolverte las tres filas de detalle que están arriba de cada grupo.

SELECT c.category_name, t.product_name, round(t.facturacion, 2) AS facturacion
FROM categories c
CROSS JOIN LATERAL (
    SELECT p.product_name,
           sum(od.unit_price * od.quantity * (1 - od.discount)) AS facturacion
    FROM products p
    JOIN order_details od ON od.product_id = p.product_id
    WHERE p.category_id = c.category_id        -- ← esto es lo que LATERAL habilita
    GROUP BY p.product_id, p.product_name
    ORDER BY facturacion DESC
    LIMIT 3
) t
ORDER BY c.category_name, t.facturacion DESC;

 category_name  |           product_name           | facturacion
----------------+----------------------------------+-------------
 Beverages      | Côte de Blaye                    |   141396.74
 Beverages      | Ipoh Coffee                      |    23526.70
 Beverages      | Chang                            |    16355.96
 Condiments     | Vegie-spread                     |    16701.10
 Condiments     | Sirop d'érable                   |    14352.60
 Condiments     | Louisiana Fiery Hot Pepper Sauce |    13869.89
 Confections    | Tarte au sucre                   |    47234.97
 Confections    | Sir Rodney's Marmalade           |    22563.36
 Confections    | Gumbär Gummibärchen              |    19849.14
 Dairy Products | Raclette Courdavault             |    71155.70
 Dairy Products | Camembert Pierrot                |    46825.48
 Dairy Products | Mozzarella di Giovanni           |    24900.13
 Grains/Cereals | Gnocchi di nonna Alice           |    42593.06
 Grains/Cereals | Wimmers gute Semmelknödel        |    21957.97
 Grains/Cereals | Singaporean Hokkien Fried Mee    |     8575.00
 Meat/Poultry   | Thüringer Rostbratwurst          |    80368.67
 Meat/Poultry   | Alice Mutton                     |    32698.38
 Meat/Poultry   | Perth Pasties                    |    20574.17
 Produce        | Manjimup Dried Apples            |    41819.65
 Produce        | Rössle Sauerkraut                |    25696.64
 Produce        | Uncle Bob's Organic Dried Pears  |    22044.30
 Seafood        | Carnarvon Tigers                 |    29171.88
 Seafood        | Ikura                            |    20867.34
 Seafood        | Boston Crab Meat                 |    17910.63

24 filas: 3 por cada una de las 8 categorías. Y Côte de Blaye —que ya lleva cuatro temas apareciendo como el valor atípico— aquí muestra por qué merece la pena mirar el detalle: 141 396,74 frente a 23 526,70 del segundo. Seis veces el siguiente. El "top 3 de Beverages" es en realidad "Côte de Blaye y dos productos cualesquiera". Un GROUP BY category_id habría dado un único número por categoría y esa desproporción no se vería.

El ORDER BY y el LIMIT de dentro son los de cada grupo, no los de la consulta. Ese es el mecanismo entero: LIMIT 3 se aplica ocho veces, una por categoría.

4. Otros usos

4.1 Varias métricas de una sola pasada

En el tema de subconsultas correlacionadas escribíamos una subconsulta por métrica. Cada una recorría orders otra vez. LATERAL las junta:

SELECT c.customer_id, m.pedidos, round(m.total, 2) AS total,
       round(m.ticket, 2) AS ticket, m.ultimo
FROM customers c
CROSS JOIN LATERAL (
    SELECT count(DISTINCT o.order_id) AS pedidos,
           coalesce(sum(od.unit_price*od.quantity*(1-od.discount)), 0) AS total,
           coalesce(sum(od.unit_price*od.quantity*(1-od.discount))
                    / nullif(count(DISTINCT o.order_id), 0), 0) AS ticket,
           max(o.order_date)::date AS ultimo
    FROM orders o LEFT JOIN order_details od ON od.order_id = o.order_id
    WHERE o.customer_id = c.customer_id
) m
ORDER BY m.total DESC LIMIT 5;

 customer_id | pedidos |   total   | ticket  |   ultimo
-------------+---------+-----------+---------+------------
 QUICK       |      28 | 110277.31 | 3938.48 | 1998-04-14
 ERNSH       |      30 | 104874.98 | 3495.83 | 1998-05-05
 SAVEA       |      31 | 104361.95 | 3366.51 | 1998-05-01
 RATTC       |      18 |  51097.80 | 2838.77 | 1998-05-06
 HUNGO       |      19 |  49979.91 | 2630.52 | 1998-04-30

Cuatro métricas, un solo recorrido de orders por cliente. El nullif del ticket medio evita la división por cero de quien no tiene pedidos (funciones-dentro-del-select).

4.2 CROSS contra LEFT: dónde se pierden filas

Northwind tiene 91 clientes, y dos de ellos —FISSA y PARIS— nunca han comprado:

SELECT count(*) FROM customers c
CROSS JOIN LATERAL (SELECT o.order_id FROM orders o
                    WHERE o.customer_id = c.customer_id
                    ORDER BY o.order_date DESC LIMIT 1) u;
-- 89

SELECT count(*) FROM customers c
LEFT JOIN LATERAL (SELECT o.order_id FROM orders o
                   WHERE o.customer_id = c.customer_id
                   ORDER BY o.order_date DESC LIMIT 1) u ON true;
-- 91

CROSS JOIN LATERAL se come a los dos clientes sin pedidos, en silencio. Es exactamente el problema del tema left-right-y-full-join con otra sintaxis: si el informe es "clientes y su última compra", los dos que faltan son precisamente los que Comercial necesita ver.

Hay un matiz que engaña, y conviene tenerlo claro:

Un SELECT max(order_date) FROM orders WHERE customer_id = c.customer_id siempre devuelve una fila, aunque no haya pedidos: una fila con NULL. Un SELECT ... GROUP BY producto ORDER BY ... LIMIT 1 devuelve cero filas si no hay nada que agrupar. Por eso la costumbre sana es usar LEFT JOIN LATERAL ... ON true siempre, salvo que quieras deliberadamente descartar las filas sin coincidencia.

4.3 LATERAL con funciones que devuelven conjuntos

LATERAL no solo acepta subconsultas: también funciones. Generar tres vencimientos mensuales por pedido:

SELECT o.order_id, o.order_date::date, v.n,
       (o.order_date + (v.n || ' month')::interval)::date AS vencimiento
FROM orders o
CROSS JOIN LATERAL generate_series(1, 3) AS v(n)
WHERE o.order_id = 10248;

 order_id | order_date | n | vencimiento
----------+------------+---+-------------
    10248 | 1996-07-04 | 1 | 1996-08-04
    10248 | 1996-07-04 | 2 | 1996-09-04
    10248 | 1996-07-04 | 3 | 1996-10-04

Una fila de entrada, tres de salida. Es la forma limpia de expandir una fila en varias — vencimientos, cuotas, desglose de un texto con unnest.

Con funciones, LATERAL es opcional: FROM orders o, generate_series(1,3) funciona igual. PostgreSQL lo asume. Escribirlo explícito hace más evidente qué está pasando.

5. LATERAL contra las alternativas

5.1 Contra subconsultas correlacionadas repetidas

Tres métricas, tres subconsultas correlacionadas:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT c.customer_id,
  (SELECT count(*)          FROM orders o WHERE o.customer_id = c.customer_id) AS pedidos,
  (SELECT min(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS primero,
  (SELECT max(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS ultimo
FROM customers c;

 Seq Scan on customers c (actual rows=91 loops=1)
   SubPlan 1
     ->  Aggregate (actual rows=1 loops=91)
           ->  Bitmap Heap Scan on orders o (actual rows=9 loops=91)
   SubPlan 3
     ->  Result (actual rows=1 loops=91)
           InitPlan 2
             ->  Limit (actual rows=1 loops=91)
                   ->  Index Scan using idx_orders_order_date on orders o_1 (actual rows=1 loops=91)
                         Rows Removed by Filter: 129
   SubPlan 5
     ->  Result (actual rows=1 loops=91)
           InitPlan 4
             ->  Limit (actual rows=1 loops=91)
                   ->  Index Scan Backward using idx_orders_order_date on orders o_2 (actual rows=1 loops=91)
                         Rows Removed by Filter: 122

Tres SubPlan, cada uno con loops=91: 273 recorridos de orders. Y fíjate en Rows Removed by Filter: 129 — para encontrar el primer pedido de un cliente, PostgreSQL recorre el índice de fechas descartando 129 filas ajenas antes de acertar. Por cliente.

Lo mismo con LATERAL:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT c.customer_id, m.pedidos, m.primero, m.ultimo
FROM customers c
CROSS JOIN LATERAL (
  SELECT count(*) AS pedidos, min(o.order_date) AS primero, max(o.order_date) AS ultimo
  FROM orders o WHERE o.customer_id = c.customer_id
) m;

 Nested Loop (actual rows=91 loops=1)
   ->  Seq Scan on customers c (actual rows=91 loops=1)
   ->  Aggregate (actual rows=1 loops=91)
         ->  Bitmap Heap Scan on orders o (actual rows=9 loops=91)
               Index Cond: ((customer_id)::text = (c.customer_id)::text)

Un solo loops=91 y ocho líneas de plan en lugar de veintidós. Las tres métricas salen del mismo recorrido, y se usa el índice por cliente en vez del de fechas.

La regla: en cuanto haya más de una subconsulta correlacionada contra la misma tabla y con el mismo WHERE, es una LATERAL.

5.2 Contra DISTINCT ON

Para el top 1 de cada grupo, PostgreSQL tiene un atajo más corto (distinct-y-duplicados):

SELECT DISTINCT ON (p.category_id) c.category_name, p.product_name, p.unit_price
FROM products p JOIN categories c USING (category_id)
ORDER BY p.category_id, p.unit_price DESC;

 category_name  |      product_name       | unit_price
----------------+-------------------------+------------
 Beverages      | Côte de Blaye           |     263.50
 Condiments     | Vegie-spread            |      43.90
 Confections    | Sir Rodney's Marmalade  |      81.00
 Dairy Products | Raclette Courdavault    |      55.00
 Grains/Cereals | Gnocchi di nonna Alice  |      38.00
 Meat/Poultry   | Thüringer Rostbratwurst |     123.79
 Produce        | Manjimup Dried Apples   |      53.00
 Seafood        | Carnarvon Tigers        |      62.50

5.3 Contra funciones de ventana

row_number() OVER (PARTITION BY categoria ORDER BY ventas DESC) y filtrar <= 3 resuelve el mismo top-N. Es el bloque 7 (ranking-row-number-rank-ntile) y muchas veces será más rápido, porque hace una sola pasada en vez de una por grupo.

LATERAL gana cuando la subconsulta no es solo un ranking: cuando cada grupo necesita su propia lógica, su propio JOIN, o cuando quieres parar en 3 filas sin calcular las 77.

6. Errores comunes

1. Olvidar LATERAL y no entender el error. invalid reference to FROM-clause entry no dice "falta LATERAL" en la primera línea, pero sí en la SUGERENCIA. Léela: PostgreSQL da la solución exacta.

2. Usar CROSS JOIN LATERAL en un informe que debe listarlo todo. Pierdes las filas sin coincidencia sin ningún aviso. Con Northwind son dos clientes de 91; en producción es "faltan clientes en el informe y nadie sabe por qué".

3. Olvidar el ON true. ERROR: syntax error at or near ")" LEFT JOIN necesita una condición aunque la correlación ya esté dentro.

4. Referenciar hacia la derecha. FROM (LATERAL ...) a, tabla b con la lateral usando b.col falla: solo se ve lo que está a la izquierda. Se arregla reordenando el FROM.

5. Poner el LIMIT fuera. LIMIT 3 en la consulta principal devuelve 3 filas en total, no 3 por grupo. El LIMIT que corta por grupo es el de dentro del paréntesis. Es la diferencia entre 3 filas y 24.

6. Suponer que es gratis. Se ejecuta una vez por fila de la izquierda. Con 91 clientes no se nota; con 5 millones y sin índice sobre la columna de correlación, es una consulta que no termina. El índice sobre la columna del WHERE interno es lo que hace viable una LATERAL.

7. Diferencias con SQL Server

APPLY es lo mismo con otro nombre, y es más antiguo: SQL Server lo tiene desde 2005, PostgreSQL añadió LATERAL en la 9.3 (2013). LATERAL es el nombre del estándar SQL; APPLY es el propietario.

La traducción es mecánica: CROSS APPLY → CROSS JOIN LATERAL, OUTER APPLY → LEFT JOIN LATERAL ... ON true. Lo único que se olvida al portar es el ON true, y el error de sintaxis que sale no lo insinúa.

8. Ejercicios

1. Los 2 pedidos más caros de cada empleado, con su importe.
2. Para cada categoría, el producto más caro y el más barato en la misma fila.
3. Los 2 clientes que más facturan de cada uno de estos países: Germany, USA, France, Brazil, UK.
4. Reescribe con una sola LATERAL esta consulta, y compara los planes: sql SELECT c.customer_id, (SELECT count(*) FROM orders o WHERE o.customer_id = c.customer_id), (SELECT min(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id), (SELECT max(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id) FROM customers c;
5. ¿Qué clientes exactamente pierde CROSS JOIN LATERAL frente a LEFT JOIN LATERAL sobre customers? Encuéntralos sin usar LATERAL.

9. Pregunta de negocio

Comercial quiere una ficha por cliente para preparar la campaña de recuperación: cuándo compró por última vez, cuánto tiempo lleva sin comprar, cuántos pedidos y cuánto ha gastado, y cuál es su producto estrella. Ordenada por quien más tiempo lleva sin comprar.

El producto estrella no es un agregado del cliente: es una fila de detalle elegida dentro de cada cliente. Ahí es donde GROUP BY no llega.