LATERAL·APPLY 조인: 왼쪽 상관 참조와 Key별 Top-N

핵심 요약

LATERAL, CROSS APPLY, OUTER APPLY의 공통 핵심은 FROM 절의 오른쪽 Row Source가 왼쪽 Row Source의 Column을 참조할 수 있게 하는 왼쪽 상관(Left Correlation) 입니다.

다만 역할은 구분해야 합니다.

• LATERAL: 오른쪽 Inline View에 왼쪽 Column을 참조할 수 있는 상관 범위를 부여합니다.
• CROSS APPLY: 왼쪽 상관을 허용하면서, 오른쪽 결과가 있는 왼쪽 행만 반환합니다.
• OUTER APPLY: 왼쪽 상관을 허용하면서, 오른쪽 결과가 없어도 왼쪽 행을 보존합니다.

왼쪽 Row 한 건
→ 왼쪽 값을 오른쪽 Query Block에서 참조
→ 오른쪽에서 0건·1건·여러 건 생성
→ APPLY 종류 또는 결합 방식에 따라 왼쪽 Row 보존 여부 결정

APPLY 문법을 사용했다고 해서 물리적으로 반드시 “왼쪽 행마다 오른쪽을 한 번씩 실행”하는 Plan이 고정되는 것은 아닙니다. Optimizer는 View Merging, Predicate Pushing, Join 순서 변경 등 비용 기반 변환을 적용할 수 있으므로 실제 동작은 실행계획과 Runtime 통계로 검증해야 합니다.

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

1. 일반 Inline View와 Lateral Inline View의 상관 범위 차이를 설명한다.
2. CROSS APPLY와 OUTER APPLY의 왼쪽 행 보존 규칙을 계산한다.
3. 오른쪽 결과가 0·1·N건일 때 최종 결과 행 수를 예측한다.
4. Key별 Top-N에서 결정적인 정렬과 Tie-Breaker가 필요한 이유를 설명한다.
5. 상관 Key와 정렬 Key에 맞춘 Index가 Stopkey 처리에 유리한 이유를 설명한다.
6. APPLY 내부의 GROUP BY 없는 집계가 입력 0건에서도 한 행을 반환하는 이유를 설명한다.
7. APPLY와 분석 함수 방식의 비용 구조를 비교한다.
8. Starts, A-Rows, Buffers를 이용해 1회당 반복 작업량을 계산한다.

1. 핵심 용어: Query Block, Inline View, 왼쪽 상관

1.1 Query Block과 Inline View

SELECT 문 안의 독립적인 SELECT 단위를 Query Block이라고 합니다. FROM 절에 배치한 Subquery는 Inline View입니다.

일반 Inline View는 같은 FROM 절에서 자신보다 왼쪽에 있는 Table Alias를 임의로 참조할 수 없습니다.

SELECT c.customer_id,
       x.order_id
FROM   customers c
JOIN   (
         SELECT o.order_id
         FROM   orders o
         WHERE  o.customer_id = c.customer_id
       ) x
  ON   1 = 1;

위 문장에서 x는 일반 Inline View이므로 c.customer_id를 참조할 상관 범위가 없습니다.

1.2 LATERAL이 제공하는 왼쪽 상관

LATERAL을 지정하면 오른쪽 Subquery 안에서 FROM 절 왼쪽에 먼저 정의된 Table의 Column을 참조할 수 있습니다.

SELECT c.customer_id,
       x.order_id
FROM   customers c,
       LATERAL (
         SELECT o.order_id
         FROM   orders o
         WHERE  o.customer_id = c.customer_id
       ) x;

같은 상관 관계를 CROSS APPLY로 표현할 수 있습니다.

SELECT c.customer_id,
       x.order_id
FROM   customers c
CROSS APPLY (
    SELECT o.order_id
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
) x;

여기서 중요한 점은 SQL을 절차형 반복문으로 강제한다는 의미가 아니라, 오른쪽 Query Block이 왼쪽 Column을 사용할 수 있는 논리적 범위를 만든다는 것입니다. 물리 실행 방식은 Optimizer가 비용을 기준으로 결정합니다.

1.3 LATERAL의 대표 제약

Oracle AI Database 26ai 기준으로 Lateral Inline View에는 다음과 같은 제약이 있습니다.

• 같은 Table Reference에서 PIVOT, UNPIVOT, Pattern을 함께 지정할 수 없습니다.
• Right Outer Join 또는 Full Outer Join의 첫 번째 Table을 향한 왼쪽 상관에는 제약이 있습니다.
• query_partition_clause와 Join 위치가 결합될 때 왼쪽 상관 범위에 추가 제약이 생길 수 있습니다.

시험과 실무에서는 모든 제약을 암기하기보다, LATERAL이 왼쪽 상관을 허용하는 문법이라는 핵심과 Join 방향에 따른 제한이 있다는 점을 우선 이해합니다.

2. CROSS APPLY와 OUTER APPLY의 결과 규칙

다음 데이터를 사용합니다.

CUSTOMERS
CUSTOMER_ID | NAME
------------+-----
10          | A
20          | B
30          | C

ORDERS
ORDER_ID | CUSTOMER_ID | AMOUNT
---------+-------------+-------
101      | 10          | 5000
102      | 10          | 7000
201      | 20          | 3000

2.1 CROSS APPLY

SELECT c.customer_id,
       x.order_id
FROM   customers c
CROSS APPLY (
    SELECT o.order_id
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
) x
ORDER BY c.customer_id, x.order_id;

10 | 101
10 | 102
20 | 201

고객 30은 오른쪽 결과가 0건이므로 제거됩니다. 고객 10은 오른쪽 결과가 2건이므로 두 행으로 증가합니다.

2.2 OUTER APPLY

SELECT c.customer_id,
       x.order_id
FROM   customers c
OUTER APPLY (
    SELECT o.order_id
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
) x
ORDER BY c.customer_id, x.order_id;

10 | 101
10 | 102
20 | 201
30 | NULL

고객 30도 보존되며 오른쪽 출력 Column은 NULL로 확장됩니다.

2.3 최종 행 수 계산

왼쪽 i번째 행에 대해 오른쪽 결과 건수를 mᵢ라고 하면 다음처럼 계산할 수 있습니다.

CROSS APPLY 기여 행 수 = mᵢ
OUTER APPLY 기여 행 수 = max(1, mᵢ)

예를 들어 오른쪽 결과 건수가 차례로 2, 0, 1이면 다음과 같습니다.

CROSS APPLY 전체 행 수 = 2 + 0 + 1 = 3
OUTER APPLY 전체 행 수 = 2 + 1 + 1 = 4

OUTER APPLY가 오른쪽에서 항상 한 행만 반환하는 것은 아닙니다. 오른쪽에서 N행이 나오면 해당 왼쪽 행도 N행으로 증가합니다.

3. LATERAL과 APPLY를 구분하는 기준

따라서 다음 문장은 정확하지 않습니다.

LATERAL은 미매칭 왼쪽 행을 보존한다.

정확한 표현은 다음과 같습니다.

LATERAL은 상관 범위를 제공하고,
미매칭 행 보존 여부는 함께 사용한 Join 방식이 결정한다.

4. Key별 최근 주문 한 건 조회

고객마다 가장 최근 주문 한 건을 조회합니다.

SELECT c.customer_id,
       c.customer_name,
       x.order_id,
       x.order_date,
       x.amount
FROM   customers c
OUTER APPLY (
    SELECT o.order_id,
           o.order_date,
           o.amount
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
    ORDER BY o.order_date DESC,
             o.order_id   DESC
    FETCH FIRST 1 ROW ONLY
) x;

결과 한 행의 의미는 다음과 같습니다.

고객 한 명 + 그 고객의 결정적인 최신 주문 최대 한 건

4.1 Tie-Breaker가 필요한 이유

ORDER_DATE가 같은 주문이 여러 건이면 날짜만으로는 첫 번째 행이 결정되지 않습니다. FETCH FIRST 1 ROW ONLY를 사용하더라도 정렬 Key가 유일하지 않으면 실행 시점과 접근 경로에 따라 선택 행이 달라질 수 있습니다.

ORDER BY o.order_date DESC,
         o.order_id   DESC

ORDER_ID처럼 유일한 Column을 마지막 Tie-Breaker로 추가하면 결과가 결정적입니다.

4.2 CROSS APPLY와 OUTER APPLY 선택

• 주문이 없는 고객도 보여야 함: OUTER APPLY
• 최근 주문이 있는 고객만 필요함: CROSS APPLY

문법 선택은 성능보다 먼저 결과 의미로 결정해야 합니다.

5. Key별 Top-N과 Index 설계

다음 Index를 가정합니다.

CREATE INDEX orders_cust_dt_ix
    ON orders(customer_id, order_date DESC, order_id DESC);

고객 한 건이 주어졌을 때 기대하는 접근 순서는 다음과 같습니다.

CUSTOMER_ID 등치 탐색
→ 해당 고객 범위에서 ORDER_DATE 최신 방향으로 읽기
→ ORDER_ID로 동점 순서 확정
→ 첫 행을 찾으면 중단

가능한 Plan 개념 구조는 다음과 같습니다.

NESTED LOOPS OUTER
  TABLE ACCESS ... CUSTOMERS
  VIEW
    WINDOW NOSORT STOPKEY
      TABLE ACCESS BY INDEX ROWID ORDERS
        INDEX RANGE SCAN ORDERS_CUST_DT_IX

Operation 이름은 Version, 통계, Transformation, Select List에 따라 달라질 수 있습니다. 판단의 핵심은 다음입니다.

1. CUSTOMER_ID가 Index access 조건으로 사용되는가?
2. 정렬 Key가 Index 순서와 맞아 별도 Sort가 줄어드는가?
3. Top-N 행 제한이 STOPKEY 계열 Operation으로 나타나는가?
4. 필요한 Column이 Index에 없으면 Table Access 비용이 얼마나 추가되는가?

AMOUNT까지 자주 조회하고 Table Access가 병목이라면 (customer_id, order_date DESC, order_id DESC, amount) 같은 확장 Index를 검토할 수 있습니다. 그러나 Index 폭과 DML 비용이 증가하므로 무조건 추가하지 않고 실제 I/O와 업무 빈도로 판단합니다.

6. APPLY와 분석 함수 방식 비교

모든 고객의 최신 주문을 한 번에 계산하는 방식입니다.

SELECT c.customer_id,
       c.customer_name,
       r.order_id,
       r.order_date,
       r.amount
FROM   customers c
LEFT JOIN (
    SELECT o.order_id,
           o.customer_id,
           o.order_date,
           o.amount,
           ROW_NUMBER() OVER (
               PARTITION BY o.customer_id
               ORDER BY o.order_date DESC,
                        o.order_id   DESC
           ) AS rn
    FROM   orders o
) r
  ON r.customer_id = c.customer_id
 AND r.rn = 1;

비교식은 다음처럼 단순화할 수 있습니다.

APPLY 비용 관점
= Outer 행 수 × 오른쪽 1회 Probe 비용

전체 집합 방식 비용 관점
= 주문 대상 범위 Scan
  + 고객별 순위 계산 또는 집계
  + 고객 집합과 Join

문법만 보고 우열을 정하지 않고 동일한 조건, 동일한 결과 행 수, 동일한 Fetch 범위에서 Runtime 통계를 비교합니다.

7. APPLY 내부 집계와 입력 0건

다음 Query는 고객별 주문 건수와 합계를 계산합니다.

SELECT c.customer_id,
       x.order_count,
       x.total_amount
FROM   customers c
CROSS APPLY (
    SELECT COUNT(*)    AS order_count,
           SUM(amount) AS total_amount
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
) x;

GROUP BY가 없는 전체 집계는 Filter 후 입력이 0건이어도 결과 행 하나를 만듭니다.

COUNT(*)    → 0
SUM(amount) → NULL

따라서 주문이 없는 고객도 오른쪽 결과가 1행 존재하므로 CROSS APPLY에서 제거되지 않습니다.

주문이 있는 고객만 남기려면 집계 결과 자체가 0행이 되도록 제한합니다.

SELECT c.customer_id,
       x.order_count
FROM   customers c
CROSS APPLY (
    SELECT COUNT(*) AS order_count
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
    HAVING COUNT(*) > 0
) x;

HAVING을 통과하지 못하면 오른쪽 결과가 0행이 되어 해당 고객이 제거됩니다.

8. OUTER APPLY의 NULL과 미매칭 구분

OUTER APPLY 결과의 오른쪽 Column이 NULL이라고 해서 반드시 오른쪽 행이 없었다고 단정할 수 없습니다.

오른쪽 행 없음
→ 오른쪽 출력 Column 전체가 NULL로 확장

오른쪽 행 존재, AMOUNT 자체가 NULL
→ ORDER_ID는 값이 있고 AMOUNT만 NULL일 수 있음

매칭 존재 여부가 중요하면 실제 행이 존재할 때 반드시 값이 있는 식별 Column을 함께 반환합니다.

CASE
  WHEN x.order_id IS NULL THEN 'NO_ORDER'
  ELSE 'HAS_ORDER'
END

업무상 ORDER_ID도 Nullable할 수 있다면 별도의 Match Indicator를 설계해야 합니다.

9. 실행계획으로 반복 비용 진단

9.1 Runtime 통계 수집과 출력

SELECT /*+ GATHER_PLAN_STATISTICS */
       c.customer_id,
       x.order_id,
       x.order_date
FROM   customers c
OUTER APPLY (
    SELECT o.order_id,
           o.order_date
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
    ORDER BY o.order_date DESC,
             o.order_id   DESC
    FETCH FIRST 1 ROW ONLY
) x
WHERE  c.region_code = :region_code;

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL,
        NULL,
        'ALLSTATS LAST +PREDICATE +ALIAS +NOTE'
    )
);

ALLSTATS LAST는 마지막 실행의 I/O 및 Memory 관련 Runtime 통계를 확인하는 데 사용합니다. 통계가 수집되지 않았거나 Cursor가 다른 경우 원하는 값이 표시되지 않을 수 있으므로 대상 SQL_ID와 Child Cursor도 확인합니다.

9.2 확인 항목

9.3 1회당 작업량 계산

오른쪽 1회당 평균 출력 행
= 오른쪽 A-Rows ÷ 오른쪽 Starts

오른쪽 1회당 평균 Logical I/O
= 오른쪽 Buffers ÷ 오른쪽 Starts

예를 들어 오른쪽 Operation의 Starts=100, A-Rows=100, Buffers=900이면 평균적으로 한 번 시작할 때 1행을 반환하고 9 Buffer를 사용한 것입니다.

Starts가 Outer A-Rows와 같지 않을 수도 있습니다. Optimizer가 Query를 변환하거나 다른 Join 구조를 선택할 수 있으므로 “APPLY 문법이므로 반드시 Outer 행 수만큼 Starts가 발생한다”고 단정하지 않고 실제 Plan을 기준으로 해석합니다.

10. 일반 Join이 더 명확한 경우

단순 등치 조인만 필요하고 오른쪽 Query Block에서 왼쪽 값에 따른 정렬·행 제한·함수 호출을 수행하지 않는다면 일반 Join이 의도를 더 직접적으로 보여 줍니다.

SELECT c.customer_id,
       o.order_id
FROM   customers c
LEFT JOIN orders o
  ON   o.customer_id = c.customer_id;

APPLY가 특히 유용한 대표 상황은 다음과 같습니다.

• 왼쪽 Key별 Top-N
• 왼쪽 Row에 따라 달라지는 Inline View
• 왼쪽 값을 인수로 받는 Table Function
• 오른쪽에서 여러 Aggregate나 여러 Column을 한 Query Block에서 계산
• 일반 Inline View로는 표현할 수 없는 왼쪽 상관 참조

단순 Join을 APPLY로 바꾸는 것만으로 성능 개선이 보장되지는 않습니다.

11. 혼동하기 쉬운 판단

12. 실전 적용 절차

1. 결과 한 행의 업무 의미를 정의한다.
2. 오른쪽 결과가 0·1·N건일 때 최종 행 수를 계산한다.
3. 미매칭 왼쪽 행 보존 여부로 CROSS/OUTER를 선택한다.
4. Top-N이면 유일한 Tie-Breaker를 포함한 ORDER BY를 작성한다.
5. 상관 Key + 정렬 Key 순서에 맞는 Index를 검토한다.
6. APPLY와 분석 함수·사전 집계 대안을 비교한다.
7. GATHER_PLAN_STATISTICS와 DBMS_XPLAN으로 Runtime 통계를 확인한다.
8. A-Rows/Starts와 Buffers/Starts로 1회당 작업량을 계산한다.
9. 0건·NULL·동점·대량 Outer 조건에서 결과와 성능을 회귀 검증한다.

핵심 정리

LATERAL
→ 오른쪽 Inline View에 왼쪽 상관 범위를 부여

CROSS APPLY
→ 오른쪽 결과가 있는 왼쪽 행만 반환

OUTER APPLY
→ 모든 왼쪽 행을 보존하고 미매칭 오른쪽 Column을 NULL로 확장

Key별 Top-N
→ 상관 Key + 결정적인 ORDER BY + 행 제한 + 적절한 Index

성능 진단
→ 문법 추측이 아니라 Starts·A-Rows·Buffers와 1회당 작업량 확인

공식 검증 기준

• Oracle AI Database 26ai SQL Language Reference — SELECT, LATERAL, cross_outer_apply_clause, row_limiting_clause
• Oracle AI Database 26ai SQL Language Reference — Aggregate Functions, COUNT
• Oracle AI Database 26ai SQL Tuning Guide — Query Transformations
• Oracle Database PL/SQL Packages and Types Reference — DBMS_XPLAN
• 한국데이터산업진흥원 데이터자격시험 — SQL 전문가 시험주요내용