LATERAL / CROSS APPLY (subconsulta fila a fila)

Oracle

-- Each customer's 3 most recent orders (LATERAL, 12c+)
SELECT m.name, s.created_at, s.amount
FROM customer m,
     LATERAL (SELECT created_at, amount FROM orders
              WHERE customer_id = m.id
              ORDER BY created_at DESC FETCH FIRST 3 ROWS ONLY) s;

-- Alternative: CROSS APPLY (12c+)
SELECT m.name, s.amount
FROM customer m
CROSS APPLY (SELECT amount FROM orders WHERE customer_id = m.id) s;

LATERAL/APPLY permite que una subconsulta en FROM haga referencia a columnas de la tabla situada a su izquierda, de modo que se pueden escribir joins correlacionados como 'las N primeras filas para cada fila'. Oracle 12c admite tanto la sintaxis ANSI LATERAL como CROSS/OUTER APPLY.

PostgreSQL

SELECT m.name, s.created_at, s.amount
FROM customer m
CROSS JOIN LATERAL (
  SELECT created_at, amount FROM orders
  WHERE customer_id = m.id
  ORDER BY created_at DESC LIMIT 3
) s;

En PostgreSQL, las subconsultas que dependen de la fila de la izquierda se escriben con la palabra clave LATERAL (JOIN LATERAL / CROSS JOIN LATERAL / LEFT JOIN LATERAL). Muy útil para 'top N por grupo' y para expandir filas con funciones que devuelven conjuntos. No hay sintaxis CROSS APPLY; se usa LATERAL.

MySQL

-- MySQL 8.0.14+ supports LATERAL
SELECT m.name, s.amount
FROM customer m
JOIN LATERAL (
  SELECT amount FROM orders
  WHERE customer_id = m.id
  ORDER BY created_at DESC LIMIT 3
) s ON true;

Desde MySQL 8.0.14 se admiten las LATERAL derived tables; la subconsulta puede hacer referencia a columnas de la tabla externa. Antes, y para CROSS APPLY, no había equivalente; 'top N por grupo' se resolvía con funciones de ventana o subconsultas correlacionadas. MariaDB todavía no tiene LATERAL.

SQL Server

SELECT m.name, s.created_at, s.amount
FROM customer m
CROSS APPLY (
  SELECT TOP 3 created_at, amount FROM orders
  WHERE customer_id = m.id
  ORDER BY created_at DESC
) s;
-- Use OUTER APPLY to return non-matching rows too

El equivalente de LATERAL en SQL Server es el operador APPLY: CROSS APPLY (como INNER JOIN LATERAL, descarta las no coincidencias) y OUTER APPLY (como LEFT JOIN LATERAL, NULL para las no coincidencias). Habitual para llamar a funciones con valores de tabla para cada fila y en consultas de top N por grupo.

MongoDB

// Closest equivalent: $lookup with a sub-pipeline
db.customer.aggregate([
  { $lookup: {
      from: "orders",
      let: { mid: "$id" },
      pipeline: [
        { $match: { $expr: { $eq: ["$customer_id", "$$mid"] } } },
        { $sort: { created_at: -1 } }, { $limit: 3 }
      ],
      as: "last3"
  } }
])

En MongoDB, el equivalente de LATERAL/APPLY es la forma de $lookup con un sub-pipeline y variables let: los campos del documento externo se pasan al sub-pipeline mediante let como $$variable, donde se hacen la coincidencia/ordenación/limitación. Esto resuelve el patrón 'top N relacionados para cada documento'.

Redis

# Not applicable: Redis has no subqueries / LATERAL.

Redis no ofrece subconsultas correlacionadas fila a fila. El patrón 'top N relacionados para cada elemento' se resuelve con un bucle en la aplicación (p. ej., un ZRANGE aparte por clave) o con un script Lua en el servidor.

Elasticsearch

# Closest equivalent: terms + top_hits sub-aggregation
GET orders/_search
{
  "size": 0,
  "aggs": { "by_customer": { "terms": { "field": "customer_id" },
    "aggs": { "last3": { "top_hits": {
      "sort": [ { "created_at": "desc" } ], "size": 3 } } } } }
}

Elasticsearch no tiene LATERAL/APPLY; el patrón 'top N registros por grupo' se logra con una subagregación top_hits anidada dentro de una terms aggregation. En lugar de una subconsulta correlacionada, se trata de agrupación más muestreo dentro del grupo sobre un único índice.