Subquery Unnesting: Semi·Anti Join과 NOT IN NULL 정합성

핵심 요약

Subquery Unnesting은 WHERE 절의 Nested Subquery를 동등한 Join 형태로 바꾸어 Subquery Table을 전체 Join Order·Join Method·Access Path 후보에 포함하는 Transformation입니다.

EXISTS·IN
→ 존재 여부를 보존하는 Semi Join 후보

NOT EXISTS
→ 미존재 여부를 보존하는 Anti Join 후보

NOT IN
→ NULL의 3값 논리를 보존하는 Null-Aware Anti Join 후보 가능

Semi Join은 오른쪽에서 여러 Match가 있어도 왼쪽 행을 한 번만 반환합니다. Anti Join은 오른쪽 Match가 없는 왼쪽 행을 반환합니다. 일반 Inner Join으로 수동 변환하면 중복 때문에 결과 행 수가 증가할 수 있으므로 Join Type의 의미를 먼저 이해해야 합니다.

SQL 의미
→ 존재·미존재·값 비교

Transformation
→ 의미를 보존하는 Join Type과 NULL 처리

Plan 선택
→ NL·HASH·MERGE, Access Path, Join Order

이 이론의 범위

SQLP SQL 고급 활용 및 튜닝 → 서브쿼리와 조인 변환 범위에서 Subquery Unnesting, Semi·Anti Join, IN·EXISTS, NOT IN·NOT EXISTS, Null-Aware Anti Join과 Hint·실행계획 검증을 다룹니다.

학습 목표

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

1. Subquery Unnesting이 Join Order 후보를 넓히는 원리를 이해한다.
2. Semi Join과 일반 Inner Join의 중복 처리 차이를 구분한다.
3. Anti Join과 NOT EXISTS의 결과 의미를 설명한다.
4. NOT IN에서 Outer·Inner NULL이 결과에 미치는 영향을 계산한다.
5. Null-Aware Anti Join이 필요한 이유를 이해한다.
6. Aggregation·Row Limiting·Analytic Function 등이 Unnesting을 제한할 수 있는 이유를 설명한다.
7. EXISTS 내부의 불필요한 ROWNUM <= 1이 Transformation에 미치는 영향을 판단한다.
8. UNNEST, NO_UNNEST, QB_NAME Hint를 제한적으로 검증한다.
9. 자동 Unnesting 대상과 공식 제한 요소를 구분한다.
10. IN·EXISTS가 OR Branch 안에 있을 때 Transformation 제한을 설명한다.
11. 복합 Column NOT IN의 NULL 결과를 Row Comparison으로 검증한다.
12. FILTER, SEMI, ANTI Plan의 실제 작업량을 비교한다.

1. Unnesting 전후 구조

다음 SQL은 서울에 있는 부서에 소속된 사원을 조회합니다.

SELECT e.employee_id,
       e.department_id
FROM   employees e
WHERE  EXISTS (
           SELECT 1
           FROM   departments d
           WHERE  d.department_id = e.department_id
           AND    d.location_id = :location_id
       );

Unnesting되지 않은 개념 구조는 다음과 같습니다.

FILTER
  EMPLOYEES
  DEPARTMENTS 상관 Subquery

Unnesting되면 Subquery의 DEPARTMENTS가 Main Join Graph에 포함됩니다.

HASH JOIN SEMI
  EMPLOYEES
  DEPARTMENTS

이제 Optimizer는 다음 후보를 비교할 수 있습니다.

• DEPARTMENTS를 먼저 읽고 EMPLOYEES를 찾는 순서
• EMPLOYEES를 먼저 읽고 DEPARTMENTS를 확인하는 순서
• Nested Loops Semi Join
• Hash Semi Join
• Merge Semi Join
• 각 Table의 Full Scan·Index Scan

Unnesting의 목적은 무조건 Hash Join을 만드는 것이 아니라 Subquery 경계를 풀어 전체 Join 선택 공간을 넓히는 것입니다.

Oracle은 Subquery Body를 Outer Query Block에 Merge하거나 Inline View 형태로 바꿀 수 있습니다. 자동 Unnesting의 대표 범위는 다음과 같습니다.

제한이 없는 Uncorrelated IN
→ 자동 Unnesting 후보

Aggregate·GROUP BY가 없는 Correlated IN·EXISTS
→ 자동 Unnesting 후보

그 밖의 구조
→ 별도 Transformation·Hint·원래 FILTER Plan 후보

IN·EXISTS 또는 NOT IN·NOT EXISTS가 OR Branch 안에 있으면 일반적인 Semi·Anti Join Transformation 후보가 제한될 수 있습니다. 실제 Plan에서 확인합니다.

2. Semi Join의 결과 의미

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

CUSTOMERS
CUSTOMER_ID
-----------
10
20
30

ORDERS
ORDER_ID | CUSTOMER_ID
---------+------------
101      | 10
102      | 10
201      | 20

EXISTS

SELECT c.customer_id
FROM   customers c
WHERE  EXISTS (
           SELECT 1
           FROM   orders o
           WHERE  o.customer_id = c.customer_id
       );

결과는 다음과 같습니다.

10
20

고객 10은 주문이 두 건이지만 한 번만 반환됩니다. Semi Join의 의미는 다음과 같습니다.

오른쪽에 Match가 하나 이상 존재
→ 왼쪽 행을 한 번 반환

일반 Inner Join

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

결과는 다음과 같습니다.

10
10
20

오른쪽 Match 수만큼 왼쪽 행이 증가합니다. 따라서 EXISTS를 일반 Join으로 수동 변경하려면 다음을 확인해야 합니다.

• 오른쪽 Join Key가 유일한가?
• 중복이 있어도 결과가 늘어나도 되는가?
• DISTINCT를 추가하면 원래 Outer 입력의 정당한 중복까지 제거하지 않는가?
• Semi Join을 직접 표현하는 EXISTS가 더 정확한가?

Semi Join은 반드시 오른쪽 전체를 SORT UNIQUE한 뒤 실행하는 방식만을 의미하지 않습니다. Nested Loops Semi Join은 첫 Match에서 Inner 탐색을 종료할 수 있고, Hash·Merge Semi Join도 Join Algorithm 자체가 존재 여부만 반환할 수 있습니다.

EXISTS 안의 DISTINCT
→ 존재 의미에는 대개 불필요
→ 별도 Sort·Hash Unique 또는 Transformation 제한 가능

3. IN과 Semi Join

다음 Query도 존재 집합을 검사합니다.

SELECT s.sale_id
FROM   sales s
WHERE  s.customer_id IN (
           SELECT c.customer_id
           FROM   customers c
           WHERE  c.grade = 'VIP'
       );

Subquery의 같은 CUSTOMER_ID가 여러 번 나타나도 바깥 SALE_ID가 그 중복 수만큼 증가해서는 안 됩니다. Optimizer는 Semi Join이나 필요한 중복 제어를 사용해 IN의 집합 의미를 보존할 수 있습니다.

긍정 조건의 단일 Column IN과 EXISTS는 WHERE 절에서 같은 존재 의미를 만드는 경우가 많고 Semi Join 후보가 될 수 있습니다. 그러나 Query 구조와 복합 Column 비교·상관 조건이 달라지면 SQL을 직접 대응시켜야 합니다.

WHERE (a.customer_id, a.region_id) IN (
    SELECT b.customer_id, b.region_id
    FROM   allowed_customer b
)

복합 IN은 두 Column의 Row Equality가 TRUE인 Tuple이 존재해야 합니다. EXISTS로 바꿀 때도 두 Equality를 모두 작성해야 합니다.

WHERE EXISTS (
    SELECT 1
    FROM   allowed_customer b
    WHERE  b.customer_id = a.customer_id
    AND    b.region_id   = a.region_id
)

단순한 문법 교체만으로 성능을 보장하지 않고 결과·Plan을 함께 검증합니다.

4. NOT EXISTS와 Anti Join

주문이 없는 고객을 조회합니다.

SELECT c.customer_id
FROM   customers c
WHERE  NOT EXISTS (
           SELECT 1
           FROM   orders o
           WHERE  o.customer_id = c.customer_id
       );

결과는 고객 30입니다.

Anti Join의 의미는 다음과 같습니다.

오른쪽에 조건을 만족하는 Match가 없음
→ 왼쪽 행 반환

가능한 Plan Operation은 다음과 같습니다.

HASH JOIN ANTI
NESTED LOOPS ANTI
MERGE JOIN ANTI

Optimizer는 입력 크기·Index·Cardinality에 따라 Join Method를 선택합니다.

Anti Join도 첫 Match가 발견되면 해당 Outer Row를 즉시 제거할 수 있습니다. 반대로 Match가 없는 Outer Row는 Inner에 Match가 없음을 확인해야 하므로, Nested Loops Anti Join에서는 No-Match 비율과 Inner 탐색 범위가 중요합니다.

Outer Join 후 IS NULL 패턴도 조건이 안전하면 Anti Join으로 변환될 수 있습니다.

SELECT c.customer_id
FROM   customers c
LEFT JOIN orders o
  ON   o.customer_id = c.customer_id
WHERE  o.customer_id IS NULL;

다만 IS NULL 대상이 Nullable 비Key Column이거나 다른 Predicate 위치가 바뀌면 NOT EXISTS와 의미가 달라질 수 있습니다.

5. NOT IN과 NULL의 3값 논리

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

OUTER_KEYS
KEY_VALUE
---------
10
20
NULL

INNER_KEYS
KEY_VALUE
---------
20
NULL

5.1 NOT EXISTS

SELECT a.key_value
FROM   outer_keys a
WHERE  NOT EXISTS (
           SELECT 1
           FROM   inner_keys b
           WHERE  b.key_value = a.key_value
       );

• Outer 10: Inner Match 없음 → 반환
• Outer 20: Inner 20과 Match → 제거
• Outer NULL: b.key_value = NULL은 TRUE가 되지 않음 → Match 없음 → 반환

개념 결과는 다음과 같습니다.

10
NULL

5.2 NOT IN

SELECT a.key_value
FROM   outer_keys a
WHERE  a.key_value NOT IN (
           SELECT b.key_value
           FROM   inner_keys b
       );

Subquery 결과에 NULL이 포함되면 다음 비교가 섞입니다.

10 <> 20   → TRUE
10 <> NULL → UNKNOWN
TRUE AND UNKNOWN → UNKNOWN

WHERE 절은 TRUE인 행만 반환하므로 Outer 10도 반환되지 않습니다. Outer 20은 일치 값이 있어 FALSE이고, Outer NULL도 모든 비교가 UNKNOWN입니다. 결과는 0행입니다.

Subquery에서 NULL을 제거해도 Outer 값이 NULL이면 NULL NOT IN (...)은 UNKNOWN이므로 반환되지 않습니다.

WHERE a.key_value NOT IN (
    SELECT b.key_value
    FROM   inner_keys b
    WHERE  b.key_value IS NOT NULL
)

이 경우 Outer 10은 반환되지만 Outer NULL은 반환되지 않습니다.

업무 요구가 “Inner에 같은 값이 없으면 반환하되 Outer NULL도 포함”이라면 단순 Inner IS NOT NULL 추가만으로는 부족합니다. NOT EXISTS의 Equality 의미 또는 Outer NULL 처리 규칙을 명시해야 합니다.

Sentinel을 이용한 NVL 변환은 실제 데이터와 충돌하거나 일반 Index Access를 바꿀 수 있으므로 Null 의미를 확정한 뒤 사용합니다.

5.3 복합 Column NOT IN의 NULL

복합 Row Value의 NOT IN은 각 Inner Tuple과의 Row Comparison을 3값 논리로 계산합니다.

WHERE (a.key1, a.key2) NOT IN (
    SELECT b.key1, b.key2
    FROM   inner_keys b
)

Inner Tuple에 NULL이 있다고 해서 모든 Outer Tuple이 무조건 제외된다고 단순화하면 안 됩니다.

Outer (1, 2)
Inner (9, NULL)

1 = 9
→ FALSE

Row Equality 전체
→ FALSE로 확정 가능

NOT IN의 해당 Tuple 비교
→ 제외를 방해하지 않을 수 있음

반대로 NULL이 아닌 Column들이 모두 같고 남은 비교가 NULL이면 UNKNOWN이 됩니다.

Outer (1, 2)
Inner (1, NULL)

1 = 1    → TRUE
2 = NULL → UNKNOWN
Row Equality → UNKNOWN

복합 NOT IN은 Inner·Outer의 부분 NULL 조합별 결과를 Test Data로 직접 검증합니다.

6. Null-Aware Anti Join

Oracle은 NOT IN의 NULL 의미를 보존하면서 집합 방식으로 처리하기 위해 Null-Aware Anti Join 계열의 Plan을 사용할 수 있습니다. 실행계획에는 Version과 조건에 따라 ANTI NA, ANTI SNA와 유사한 표시가 나타날 수 있습니다.

핵심은 Operation 이름을 외우는 것이 아니라 다음 결과 규칙을 유지하는 것입니다.

Inner에 NULL 존재
→ 일치하지 않는 Outer 값도 NOT IN 결과가 UNKNOWN일 수 있음

Outer 값 NULL
→ Inner NULL 제거 여부와 관계없이 비교 결과가 UNKNOWN

업무 의미가 “오른쪽에 같은 Key가 존재하지 않는 행”이라면 NOT EXISTS가 NULL 규칙을 더 직접적으로 표현하는 경우가 많습니다. 단, 기존 SQL을 변경할 때 Outer NULL을 포함할지까지 명시적으로 검증합니다.

ANTI NA
→ Null-Aware Anti Join

ANTI SNA
→ Single Null-Aware Anti Join

Plan에 Null-Aware Operation과 별도 FILTER ... IS NULL이 함께 나타날 수 있습니다. Operation 이름보다 다음을 확인합니다.

• Inner NULL 존재 시 NOT IN 결과가 보존되는가?
• Outer NULL이 반환되지 않는가?
• Inner에 NOT NULL Constraint가 있으면 일반 Anti Join으로 단순화되는가?
• Null 검사로 추가 Scan·Starts가 발생하는가?

7. Unnesting을 제한할 수 있는 요소

Optimizer는 변환 후 결과가 같다고 증명할 수 있을 때 Unnesting을 검토합니다.

Oracle 26ai SQL Language Reference가 명시하는 대표 예외는 다음과 같습니다.

• Hierarchical Subquery
• ROWNUM Pseudocolumn
• Set Operator
• Nested Aggregate Function
• Immediate Outer Query Block이 아닌 상위 Block을 참조하는 Correlation

자동 Unnesting의 대표 범위에서는 Correlated IN·EXISTS에 Aggregate Function이나 GROUP BY가 없어야 합니다.

다음 요소도 의미 보존·비용·별도 Transformation 여부를 확인합니다.

• DISTINCT
• Analytic Function과 Row Limiting
• Outer Join·복잡한 NULL 보존
• 비결정적 함수·Side Effect
• 묵시적 데이터 타입 변환과 복합 상관 조건

이 목록을 “하나라도 있으면 절대 Unnesting 불가”라는 암기 규칙으로 사용하지 않습니다. 일부 Aggregate·복잡한 Subquery는 별도 Transformation이 가능하므로 실제 Cursor Plan을 확인합니다.

8. EXISTS 내부 ROWNUM <= 1

다음 조건을 자주 볼 수 있습니다.

WHERE EXISTS (
    SELECT 1
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
    AND    ROWNUM <= 1
)

단순 존재 여부에서 EXISTS 자체가 첫 Match를 찾으면 결과가 확정되므로 ROWNUM <= 1은 결과 의미를 추가로 바꾸지 않는 경우가 많습니다. 그러나 Row Limiting 요소가 Query Block에 들어가면 Unnesting 가능성을 제한하거나 다른 Plan을 유도할 수 있습니다.

따라서 다음 순서로 처리합니다.

1. ROWNUM이 정말 업무 의미에 필요한지 확인
2. 단순 존재 확인이면 제거 후보로 분류
3. 제거 전후 결과 비교
4. FILTER·SEMI Plan과 실제 작업량 비교

ROWNUM을 제거하는 것만으로 항상 성능이 좋아지는 것은 아니지만, 공식 Unnesting 예외 중 하나인 불필요한 변환 장벽을 없앨 수 있습니다.

EXISTS의 첫 Match 종료는 SQL 의미이며, ROWNUM <= 1은 Query Block의 Row Limiting Predicate입니다. 두 기능이 물리적으로 같은 Plan을 보장하지 않습니다.

EXISTS만 사용
→ FILTER·SEMI Join 후보

EXISTS + ROWNUM <= 1
→ Row Limiting이 포함된 별도 Query Block
→ FILTER 유지 가능성 증가

9. Hint와 Query Block 지정

SELECT /*+ QB_NAME(main_qb) */
       e.employee_id
FROM   employees e
WHERE  EXISTS (
           SELECT /*+ QB_NAME(dept_qb) UNNEST */
                  1
           FROM   departments d
           WHERE  d.department_id = e.department_id
           AND    d.location_id = :location_id
       );

대표적인 Hint는 다음과 같습니다.

Hint는 다음을 확인한 뒤 사용합니다.

• Hint가 정확한 Query Block과 Alias를 가리키는가?
• Subquery 자체가 의미적으로 유효한 Unnesting 대상인가?
• Unnesting 후 대상 Row Source가 Main Block에 존재하는가?
• Outline Data·Hint Report에 Hint가 반영됐는가?
• 결과 행 수·NULL·중복이 동일한가?
• 다른 Bind와 데이터 분포에서도 안정적인가?

UNNEST는 잘못된 Transformation을 강제하는 Hint가 아닙니다. Subquery가 유효하지 않으면 Hint가 있어도 Unnesting되지 않습니다. LEADING이나 USE_HASH도 Unnesting이 실제 발생해 대상 Row Source가 같은 Join Graph에 들어온 뒤에 의미가 있습니다.

10. 실행계획 검증

SELECT /*+ GATHER_PLAN_STATISTICS */
       e.employee_id
FROM   employees e
WHERE  EXISTS (
           SELECT 1
           FROM   departments d
           WHERE  d.department_id = e.department_id
           AND    d.location_id = :location_id
       );

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

11. 혼동하기 쉬운 판단

실전 적용 순서

1. Subquery가 존재·미존재·단일 값 중 무엇을 표현하는지 확인한다.
2. 오른쪽 중복이 결과에 미치는 의미를 계산한다.
3. Outer·Inner NULL 샘플로 결과를 직접 계산한다.
4. FILTER와 SEMI·ANTI Plan을 구분한다.
5. Starts·A-Rows·Buffers와 Inner 중복 제어 비용을 측정한다.
6. Scalar·복합 Key의 Outer·Inner NULL 조합을 검증한다.
7. OR Branch와 자동 Unnesting 범위를 확인한다.
8. Unnesting 제한 요소와 불필요한 ROWNUM·DISTINCT를 확인한다.
9. Hint는 Query Block을 지정해 유효성과 적용 여부를 제한적으로 시험한다.
10. 첫 행·전체 Fetch, 결과 행 수와 다양한 Bind에서 회귀 검증한다.

핵심 정리

Subquery Unnesting
→ Nested Subquery를 Join 후보로 변환

Semi Join
→ 오른쪽 Match가 존재하는 왼쪽 행을 한 번 반환

Anti Join
→ 오른쪽 Match가 없는 왼쪽 행 반환

NOT IN
→ Inner·Outer NULL의 3값 논리 확인

검증
→ FILTER·SEMI·ANTI Operation과 Starts·A-Rows·Buffers 비교