LATERAL / CROSS APPLY (row-by-row subquery)

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 allows a subquery in FROM to reference columns of the table to its left, so you can write correlated joins such as 'the first N rows for each row'. Oracle 12c supports both ANSI LATERAL and CROSS/OUTER APPLY syntax.

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;

In PostgreSQL, subqueries that depend on the row to the left are written with the LATERAL keyword (JOIN LATERAL / CROSS JOIN LATERAL / LEFT JOIN LATERAL). Very handy for 'top N per group' and for expanding rows with set-returning functions. There is no CROSS APPLY syntax; LATERAL is used.

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;

Since MySQL 8.0.14, LATERAL derived tables are supported; the subquery can reference columns of the outer table. Before that, and for CROSS APPLY, there was no equivalent; 'top N per group' was solved with window functions or correlated subqueries. MariaDB does not have LATERAL yet.

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

SQL Server's LATERAL equivalent is the APPLY operator: CROSS APPLY (like INNER JOIN LATERAL, drops non-matches) and OUTER APPLY (like LEFT JOIN LATERAL, NULL for non-matches). Common for calling table-valued functions for each row and for top-N-per-group queries.

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

In MongoDB the LATERAL/APPLY equivalent is the $lookup form with a sub-pipeline and let variables: the outer document's fields are passed into the sub-pipeline via let as $$variable, where matching/sorting/limiting happen. This solves the 'related top N for each document' pattern.

Redis

# Not applicable: Redis has no subqueries / LATERAL.

Redis offers no row-by-row correlated subqueries. The 'related top N for each item' pattern is solved with a loop on the application side (e.g. a separate ZRANGE per key) or with a server-side Lua script.

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 has no LATERAL/APPLY; the 'top N records per group' pattern is achieved with a top_hits sub-aggregation nested inside a terms aggregation. Instead of a correlated subquery, this is grouping plus in-group sampling on a single index.