Una subconsulta normal en el FROM esta sellada: no puede ver las columnas de las tablas vecinas. LATERAL levanta esa restriccion: una subconsulta a la derecha de un join puede referenciar columnas de tablas listadas a su izquierda. Eso convierte la lista del FROM en un bucle: por cada fila externa la subconsulta se evalua de nuevo.
Por que existe LATERAL
Compara dos subconsultas. La primera es ordinaria y no compilara, porque o intenta alcanzar u.id de una tabla declarada fuera de ella:
SELECT u.id, o.amount
FROM users AS u,
(SELECT amount FROM orders WHERE user_id = u.id) AS o;
Agrega LATERAL y la subconsulta obtiene acceso a u.id. PostgreSQL ejecuta el lado derecho una vez por cada fila de users, sustituyendo los valores de esa fila:
SELECT u.id, o.amount
FROM users AS u,
LATERAL (SELECT amount FROM orders WHERE user_id = u.id) AS o;
Propiedades clave:
- Solo puedes referenciar tablas que aparecen a la izquierda en la lista del
FROM.
- Una coma en el
FROM antes de LATERAL es un CROSS JOIN LATERAL.
- La subconsulta corre fila a fila, asi que un
ORDER BY y un LIMIT ligados a la fila externa son validos dentro.
Top-N por grupo
Esta es la jugada estrella de LATERAL. Quieres los dos pedidos mas caros de cada usuario. Una funcion de ventana tambien sirve, pero LATERAL se lee mas directo y deja poner el LIMIT justo dentro:
SELECT u.id, u.email, top.id AS order_id, top.amount
FROM users AS u
JOIN LATERAL (
SELECT id, amount
FROM orders
WHERE user_id = u.id
ORDER BY amount DESC
LIMIT 2
) AS top ON true;
La subconsulta se dispara una vez por usuario y devuelve como mucho dos filas. El ON true es obligatorio: la sintaxis de JOIN exige un ON, y la logica de union ya vive dentro de la subconsulta (WHERE user_id = u.id).
Un punto sutil pero importante. Con INNER JOIN LATERAL o la forma con coma, los usuarios sin pedidos desaparecen: la subconsulta devolvio cero filas, asi que la fila externa se esfuma.
SELECT u.id, top.amount
FROM users AS u,
LATERAL (SELECT amount FROM orders WHERE user_id = u.id
ORDER BY amount DESC LIMIT 2) AS top;
Para conservar a todos los usuarios, usa LEFT JOIN LATERAL ... ON true. Para los usuarios sin pedidos las columnas de la subconsulta se vuelven NULL, pero la fila en si permanece:
SELECT u.id, top.amount
FROM users AS u
LEFT JOIN LATERAL (
SELECT amount FROM orders WHERE user_id = u.id
ORDER BY amount DESC LIMIT 2
) AS top ON true;
Trampa: el ON true en un LEFT JOIN LATERAL es enganoso. Parece un predicado siempre verdadero que vuelve inutil el LEFT, pero no lo es: la fila externa sobrevive con NULL solo cuando la subconsulta devuelve cero filas. La correlacion la lleva el WHERE interno, no el ON. Si pones una condicion real en el ON, filtrara despues de que LATERAL se expanda y rompera el comportamiento que conserva filas.
Funciones que devuelven conjuntos en LATERAL
LATERAL brilla con funciones como generate_series, unnest o jsonb_array_elements, que necesitan un valor de una columna vecina. Genera una fila por mes desde que cada usuario se registro:
SELECT u.id, m.month
FROM users AS u
CROSS JOIN LATERAL generate_series(
date_trunc('month', u.created_at),
date_trunc('month', now()),
interval '1 month'
) AS m(month);
Antes de una llamada a funcion en el FROM la palabra LATERAL es opcional: para funciones de tabla se sobreentiende. Escribirla explicita sigue valiendo la pena: el codigo deja claro que hay una correlacion a la derecha.
MySQL y ClickHouse
- MySQL soporta
LATERAL desde 8.0.14, con la misma sintaxis: JOIN LATERAL (...) ON true. En versiones anteriores el top-N se resuelve con variables o funciones de ventana (desde 8.0).
- ClickHouse no tiene un
LATERAL general. El top-N por grupo se hace con arrayJoin y funciones como groupArraySorted, o con LIMIT BY:
SELECT user_id, id, amount
FROM orders
ORDER BY amount DESC
LIMIT 2 BY user_id;
LIMIT N BY es un sustituto compacto del top-N por grupo que PostgreSQL arma con LATERAL. Recuerda el vinculo: una subconsulta correlacionada en el FROM es exactamente LATERAL, y su pan de cada dia es el top-N y la expansion de funciones que devuelven conjuntos.
Una subconsulta normal en el
FROMesta sellada: no puede ver las columnas de las tablas vecinas.LATERALlevanta esa restriccion: una subconsulta a la derecha de un join puede referenciar columnas de tablas listadas a su izquierda. Eso convierte la lista delFROMen un bucle: por cada fila externa la subconsulta se evalua de nuevo.Por que existe LATERAL
Compara dos subconsultas. La primera es ordinaria y no compilara, porque
ointenta alcanzaru.idde una tabla declarada fuera de ella:-- Fails: subquery cannot see u.id SELECT u.id, o.amount FROM users AS u, (SELECT amount FROM orders WHERE user_id = u.id) AS o;Agrega
LATERALy la subconsulta obtiene acceso au.id. PostgreSQL ejecuta el lado derecho una vez por cada fila deusers, sustituyendo los valores de esa fila:SELECT u.id, o.amount FROM users AS u, LATERAL (SELECT amount FROM orders WHERE user_id = u.id) AS o;Propiedades clave:
FROM.FROMantes deLATERALes unCROSS JOIN LATERAL.ORDER BYy unLIMITligados a la fila externa son validos dentro.Top-N por grupo
Esta es la jugada estrella de
LATERAL. Quieres los dos pedidos mas caros de cada usuario. Una funcion de ventana tambien sirve, peroLATERALse lee mas directo y deja poner elLIMITjusto dentro:SELECT u.id, u.email, top.id AS order_id, top.amount FROM users AS u JOIN LATERAL ( SELECT id, amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 2 ) AS top ON true;La subconsulta se dispara una vez por usuario y devuelve como mucho dos filas. El
ON truees obligatorio: la sintaxis deJOINexige unON, y la logica de union ya vive dentro de la subconsulta (WHERE user_id = u.id).LEFT JOIN LATERAL frente a la forma con coma
Un punto sutil pero importante. Con
INNER JOIN LATERALo la forma con coma, los usuarios sin pedidos desaparecen: la subconsulta devolvio cero filas, asi que la fila externa se esfuma.-- Users with zero orders disappear SELECT u.id, top.amount FROM users AS u, LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 2) AS top;Para conservar a todos los usuarios, usa
LEFT JOIN LATERAL ... ON true. Para los usuarios sin pedidos las columnas de la subconsulta se vuelvenNULL, pero la fila en si permanece:SELECT u.id, top.amount FROM users AS u LEFT JOIN LATERAL ( SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 2 ) AS top ON true;Funciones que devuelven conjuntos en LATERAL
LATERALbrilla con funciones comogenerate_series,unnestojsonb_array_elements, que necesitan un valor de una columna vecina. Genera una fila por mes desde que cada usuario se registro:SELECT u.id, m.month FROM users AS u CROSS JOIN LATERAL generate_series( date_trunc('month', u.created_at), date_trunc('month', now()), interval '1 month' ) AS m(month);Antes de una llamada a funcion en el
FROMla palabraLATERALes opcional: para funciones de tabla se sobreentiende. Escribirla explicita sigue valiendo la pena: el codigo deja claro que hay una correlacion a la derecha.MySQL y ClickHouse
LATERALdesde 8.0.14, con la misma sintaxis:JOIN LATERAL (...) ON true. En versiones anteriores el top-N se resuelve con variables o funciones de ventana (desde 8.0).LATERALgeneral. El top-N por grupo se hace conarrayJoiny funciones comogroupArraySorted, o conLIMIT BY:-- ClickHouse: top-2 orders per user SELECT user_id, id, amount FROM orders ORDER BY amount DESC LIMIT 2 BY user_id;LIMIT N BYes un sustituto compacto del top-N por grupo que PostgreSQL arma conLATERAL. Recuerda el vinculo: una subconsulta correlacionada en elFROMes exactamenteLATERAL, y su pan de cada dia es el top-N y la expansion de funciones que devuelven conjuntos.