LATERAL / CROSS APPLY (sous-requête ligne par ligne)

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 permet à une sous-requête dans FROM de référencer les colonnes de la table située à sa gauche, ce qui permet d'écrire des jointures corrélées telles que « les N premières lignes pour chaque ligne ». Oracle 12c prend en charge à la fois la syntaxe ANSI LATERAL et 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;

Dans PostgreSQL, les sous-requêtes qui dépendent de la ligne de gauche s'écrivent avec le mot-clé LATERAL (JOIN LATERAL / CROSS JOIN LATERAL / LEFT JOIN LATERAL). Très pratique pour le « top N par groupe » et pour développer des lignes avec des fonctions renvoyant des ensembles. Il n'y a pas de syntaxe CROSS APPLY ; on utilise 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;

Depuis MySQL 8.0.14, les tables dérivées LATERAL sont prises en charge ; la sous-requête peut référencer les colonnes de la table externe. Auparavant, et pour CROSS APPLY, il n'y avait pas d'équivalent ; le « top N par groupe » se résolvait avec des fonctions de fenêtrage ou des sous-requêtes corrélées. MariaDB n'a pas encore 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

L'équivalent de LATERAL dans SQL Server est l'opérateur APPLY : CROSS APPLY (comme INNER JOIN LATERAL, écarte les non-correspondances) et OUTER APPLY (comme LEFT JOIN LATERAL, NULL pour les non-correspondances). Courant pour appeler des fonctions table pour chaque ligne et pour les requêtes top-N-par-groupe.

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"
  } }
])

Dans MongoDB, l'équivalent de LATERAL/APPLY est la forme de $lookup avec sous-pipeline et variables let : les champs du document externe sont transmis au sous-pipeline via let sous forme de $$variable, où s'effectuent correspondance/tri/limitation. Cela résout le schéma « top N associé pour chaque document ».

Redis

# Not applicable: Redis has no subqueries / LATERAL.

Redis n'offre pas de sous-requêtes corrélées ligne par ligne. Le schéma « top N associé pour chaque élément » se résout avec une boucle côté application (par ex. un ZRANGE distinct par clé) ou avec un script Lua côté serveur.

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 n'a pas de LATERAL/APPLY ; le schéma « N premiers enregistrements par groupe » s'obtient avec une sous-agrégation top_hits imbriquée dans une agrégation terms. Au lieu d'une sous-requête corrélée, il s'agit d'un regroupement avec échantillonnage intra-groupe sur un seul index.