LATERAL / CROSS APPLY(行ごとのサブクエリ)

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 により、FROM 内のサブクエリがその左側のテーブルの列を参照できるため、「各行に対する先頭 N 行」のような相関結合を記述できる。Oracle 12c は ANSI の LATERAL と 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;

PostgreSQL では、左側の行に依存するサブクエリを LATERAL キーワード(JOIN LATERAL / CROSS JOIN LATERAL / LEFT JOIN LATERAL)で記述する。「グループごとの上位 N 件」や、集合を返す関数による行の展開に非常に便利。CROSS APPLY 構文はなく、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;

MySQL 8.0.14 以降、LATERAL 派生テーブルがサポートされ、サブクエリが外側のテーブルの列を参照できる。それ以前、および CROSS APPLY については相当するものがなく、「グループごとの上位 N 件」はウィンドウ関数や相関サブクエリで解決していた。MariaDB にはまだ 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

SQL Server で LATERAL に相当するのは APPLY 演算子: CROSS APPLY(INNER JOIN LATERAL に類似、一致しないものは除外)と OUTER APPLY(LEFT JOIN LATERAL に類似、一致しないものは NULL)。各行に対してテーブル値関数を呼び出す場合や、グループごとの上位 N 件のクエリによく使われる。

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

MongoDB で LATERAL/APPLY に相当するのは、サブパイプラインと let 変数を使う $lookup の形式。外側のドキュメントのフィールドを let で $$variable としてサブパイプラインに渡し、そこで照合/ソート/制限を行う。これで「各ドキュメントに関連する上位 N 件」のパターンを解決できる。

Redis

# Not applicable: Redis has no subqueries / LATERAL.

Redis は行ごとの相関サブクエリを提供しない。「各項目に関連する上位 N 件」のパターンは、アプリケーション側のループ(例: キーごとに個別の ZRANGE)またはサーバー側の Lua スクリプトで解決する。

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 には LATERAL/APPLY がない。「グループごとの上位 N レコード」のパターンは、terms 集計内にネストした top_hits サブ集計で実現する。相関サブクエリではなく、単一のインデックス上でのグループ化とグループ内のサンプリングとなる。