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 derived table을 지원하며, 서브쿼리가 외부 테이블의 컬럼을 참조할 수 있다. 그 이전과 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 aggregation 안에 중첩한 top_hits 하위 aggregation으로 구현한다. 상관 서브쿼리 대신 단일 인덱스에서의 그룹화와 그룹 내 샘플링이다.