LATERAL / CROSS APPLY (subconsulta linha a linha)

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 uma subconsulta no FROM referencie colunas da tabela à sua esquerda, possibilitando joins correlacionados como 'as N primeiras linhas para cada linha'. O Oracle 12c suporta tanto a sintaxe ANSI LATERAL quanto 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;

No PostgreSQL, subconsultas que dependem da linha à esquerda são escritas com a palavra-chave LATERAL (JOIN LATERAL / CROSS JOIN LATERAL / LEFT JOIN LATERAL). Muito útil para 'top N por grupo' e para expandir linhas com funções que retornam conjuntos. Não há sintaxe CROSS APPLY; usa-se 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 o MySQL 8.0.14, LATERAL derived tables são suportadas; a subconsulta pode referenciar colunas da tabela externa. Antes disso, e para CROSS APPLY, não havia equivalente; 'top N por grupo' era resolvido com funções de janela ou subconsultas correlacionadas. O MariaDB ainda não tem 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

O equivalente a LATERAL no SQL Server é o operador APPLY: CROSS APPLY (como INNER JOIN LATERAL, descarta as não correspondências) e OUTER APPLY (como LEFT JOIN LATERAL, NULL para as não correspondências). Comum para chamar table-valued functions para cada linha e para consultas 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"
  } }
])

No MongoDB, o equivalente a LATERAL/APPLY é a forma do $lookup com sub-pipeline e variáveis let: os campos do documento externo são passados ao sub-pipeline via let como $$variable, onde ocorrem a correspondência/ordenação/limitação. Isso resolve o padrão 'top N relacionados para cada documento'.

Redis

# Not applicable: Redis has no subqueries / LATERAL.

O Redis não oferece subconsultas correlacionadas linha a linha. O padrão 'top N relacionados para cada item' é resolvido com um loop no lado da aplicação (ex.: um ZRANGE separado por chave) ou com um script Lua no lado do 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 } } } } }
}

O Elasticsearch não tem LATERAL/APPLY; o padrão 'top N registros por grupo' é obtido com uma sub-agregação top_hits aninhada dentro de uma terms aggregation. Em vez de uma subconsulta correlacionada, trata-se de agrupamento mais amostragem dentro do grupo em um único índice.