조인의 결과 의미: Inner·Outer·Semi·Anti Join과 NULL

핵심 요약

Join을 정확하게 해석하려면 결과 의미, 실행 알고리즘, 조인 순서를 분리해야 합니다.

Join Type   : 어떤 행을 결과에 남기는가
Join Method : 두 Row Source를 어떤 알고리즘으로 결합하는가
Join Order  : 여러 Row Source를 어떤 순서로 결합하는가

대표 Join Type의 결과 의미는 다음과 같습니다.

INNER JOIN       : 조건이 TRUE인 모든 행 조합
LEFT OUTER JOIN  : 왼쪽 행을 보존하고 오른쪽 Match를 연결
RIGHT OUTER JOIN : 오른쪽 행을 보존하고 왼쪽 Match를 연결
FULL OUTER JOIN  : 양쪽의 미매칭 행을 모두 보존
SEMI JOIN        : 상대에 Match가 존재하는 첫 번째 집합의 행
ANTI JOIN        : 상대에 Match가 존재하지 않는 첫 번째 집합의 행

Nested Loops·Hash·Sort Merge는 Join Method입니다. OUTER, SEMI, ANTI는 결과 의미를 나타내는 Join Type 또는 실행계획 Option입니다. SQL을 변경하거나 튜닝할 때는 먼저 보존 행·중복·NULL·집계 결과가 동일한지를 확인해야 합니다.

학습 목표

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

• Inner·Outer·Semi·Anti Join의 결과 행 차이를 계산한다.
• Preserved Row Source와 Optional Row Source를 구분한다.
• Outer Join의 ON과 WHERE Predicate가 결과를 바꾸는 이유를 설명한다.
• Null-Rejecting Predicate가 Outer Join의 미매칭 행을 제거하는 과정을 설명한다.
• EXISTS, IN, NOT EXISTS, NOT IN의 중복·NULL 의미를 구분한다.
• ANTI, ANTI NA, ANTI SNA 실행계획의 의미를 해석한다.
• LEFT JOIN ... IS NULL을 Anti Join으로 사용할 때 안전한 Column을 선택한다.
• Outer Join 후 COUNT(*)와 COUNT(optional_key)의 차이를 계산한다.
• Join Type·Join Method·Join Order를 분리해 실행계획을 해석한다.

1. 예제 데이터와 결과 Grain

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

CUSTOMERS

ORDERS

핵심 특징은 다음과 같습니다.

• 고객 1은 주문이 2건이므로 일반 Join에서 고객 행이 복제될 수 있습니다.
• 고객 2와 4는 주문이 없습니다.
• 양쪽 Join Key에 NULL이 존재할 수 있습니다.
• order_id는 주문 행을 식별하는 NOT NULL Key라고 가정합니다.

Join을 변경할 때는 먼저 결과의 Grain을 정합니다.

고객-주문 조합 1행인가?
고객 1명당 1행인가?
주문이 없는 고객도 포함하는가?

2. Inner Join: TRUE인 모든 조합

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

결과는 다음과 같습니다.

Inner Join은 존재 여부만 확인하지 않습니다. 조건이 TRUE인 모든 Row 조합을 생성합니다.

왼쪽 1행 × 오른쪽 Match 2행 = 결과 2행

오른쪽 중복이 원래 업무 의미에 필요한지 확인하지 않고 DISTINCT로 제거하면 Data Grain이 바뀔 수 있습니다.

2.1 Join Key와 NULL

SQL의 등치 비교에서 다음 Predicate는 양쪽 값이 모두 NULL이어도 TRUE가 아닙니다.

a.join_key = b.join_key

NULL끼리 같은 업무 값으로 취급해야 한다면 의도를 명시해야 합니다.

ON a.join_key = b.join_key
OR (a.join_key IS NULL AND b.join_key IS NULL)

다만 이런 Predicate는 일반 등치 Join보다 Access Path와 Cardinality 추정이 달라질 수 있으므로 업무 의미와 실행계획을 함께 검증합니다.

3. Left Outer Join: 왼쪽 행 보존

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

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

• CUSTOMERS는 Preserved Row Source입니다.
• ORDERS는 Optional Row Source입니다.
• Match가 없으면 Optional Row Source의 Column은 NULL로 확장됩니다.
• Match가 여러 건이면 왼쪽 행도 여러 번 반환됩니다.

Left Outer Join은 “왼쪽 행을 정확히 한 번 반환”하는 연산이 아니라 “왼쪽 행을 최소 한 번 보존”하는 연산입니다.

4. ON과 WHERE: 연결 조건과 최종 필터

활성 주문만 연결하되 주문이 없는 고객도 보여야 한다고 가정합니다.

4.1 Optional Table 조건을 ON에 배치

SELECT c.customer_id,
       c.customer_name,
       o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'PAID';

의미는 다음과 같습니다.

PAID 주문만 Match 후보로 사용한다.
→ PAID 주문이 없어도 고객 행은 보존한다.

4.2 Optional Table 조건을 WHERE에 배치

SELECT c.customer_id,
       c.customer_name,
       o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.status = 'PAID';

미매칭 행의 o.status는 NULL입니다. NULL = 'PAID'는 TRUE가 아니므로 미매칭 행이 제거됩니다.

Outer Join으로 왼쪽 행을 보존
→ WHERE의 Null-Rejecting Predicate가 NULL 확장 행 제거
→ 해당 조건에 대해서는 Inner Join과 같은 결과가 될 수 있음

o.status = 'PAID', o.order_date >= :from_date, o.amount > 0처럼 Optional Table의 NULL에서 TRUE가 되지 않는 조건은 미매칭 행을 제거할 수 있습니다.

4.3 Preserved Table 조건의 위치

서울 고객만 조회하려면 일반적으로 최종 대상 집합을 제한하는 WHERE가 자연스럽습니다.

WHERE c.region_code = 'SEOUL'

이를 ON 절에만 두면 서울이 아닌 고객도 왼쪽 보존 규칙에 따라 남을 수 있습니다.

Predicate 위치는 무조건 “가능한 안쪽”에 두는 규칙이 아니라 다음 질문으로 결정합니다.

이 조건은 Match 후보를 제한하는가?
최종 결과 행을 제거하는가?
미매칭 행을 보존해야 하는가?

5. Outer Join 이후 미매칭 행 판별

주문이 없는 고객을 찾는 흔한 표현은 다음과 같습니다.

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

여기서 o.order_id는 실제 Match 행에서 NULL이 될 수 없는 Key여야 합니다. Optional Table의 Nullable Column을 검사하면 실제로 Match한 행의 값이 NULL인 경우까지 미매칭으로 잘못 분류할 수 있습니다.

안전한 기준은 다음과 같습니다.

• Primary Key 또는 NOT NULL Unique Key
• Join 성공 시 반드시 값이 존재하는 Column
• 업무적으로 NULL이 가능한 속성 Column은 미매칭 판별에 사용하지 않음

존재하지 않음을 직접 표현하려면 다음 NOT EXISTS도 명확합니다.

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

두 표현을 변경할 때는 Join 조건, 추가 Predicate, NULL 가능성을 함께 비교합니다.

6. Right Outer Join과 Full Outer Join

6.1 Right Outer Join

Right Outer Join은 오른쪽 Row Source를 보존합니다.

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

고객 Match가 없는 주문 104도 보존되고 고객 Column은 NULL로 확장됩니다. 읽기 쉬운 방향을 위해 Table 위치를 바꾸어 Left Outer Join으로 통일하는 경우가 많습니다.

6.2 Full Outer Join

Full Outer Join은 양쪽의 미매칭 행을 모두 보존합니다.

SELECT c.customer_id AS customer_customer_id,
       o.customer_id AS order_customer_id,
       o.order_id
FROM customers c
FULL OUTER JOIN orders o
  ON o.customer_id = c.customer_id;

대표 용도는 다음과 같습니다.

• 두 시스템의 Master Data 대사
• 기존·신규 데이터의 차이 확인
• 한쪽에만 존재하는 Key 탐지

한쪽에만 존재하는 행을 판별할 때도 각 Table의 신뢰할 수 있는 NOT NULL Key를 사용합니다.

WHERE c.customer_id IS NULL
   OR o.order_id IS NULL

단, 이 예제처럼 c.customer_id 자체가 Nullable이라면 고객 쪽 미매칭 판별에는 별도의 NOT NULL 식별자를 사용해야 합니다.

실행계획에서는 등치 조건의 Full Outer Join이 HASH JOIN FULL OUTER로 실행될 수 있습니다. 다른 경우에는 Left Outer Join과 Anti Join 등을 조합한 형태로 변환될 수 있습니다.

7. Semi Join: 존재 여부

Semi Join은 두 번째 집합에 Match가 하나라도 존재하는 첫 번째 집합의 행을 반환합니다.

대표 SQL은 EXISTS와 IN입니다.

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

고객 1의 주문이 2건이어도 고객 1은 한 번만 반환됩니다.

Inner Join : Match 조합 수만큼 왼쪽 행이 반복될 수 있음
Semi Join  : Match 존재 여부만 필요하므로 왼쪽 행은 최대 한 번 반환

Semi Join은 별도의 SQL Keyword가 아니라 IN·EXISTS Subquery를 Optimizer가 Join 형태로 처리할 때 나타나는 내부 Join Type입니다.

NESTED LOOPS SEMI
HASH JOIN SEMI
MERGE JOIN SEMI

Logical SQL이 EXISTS라고 해서 실행계획에 반드시 SEMI가 표시되는 것은 아닙니다. Subquery Unnesting 가능성, Correlation, OR Branch 등 변환 조건에 따라 다른 실행 구조가 선택될 수 있습니다.

7.1 JOIN 후 DISTINCT와 EXISTS

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

현재 예제에서는 같은 고객 목록을 만들 수 있습니다. 그러나 다음 조건에서는 단순 치환이 안전하지 않습니다.

• Select 목록에서 오른쪽 Column을 사용
• 오른쪽 Match별 금액이나 건수를 집계
• 중복 자체가 업무 의미
• NULL Match 규칙이 다름
• 다른 Join 때문에 중복 원인이 추가됨

존재 여부가 목적이면 EXISTS가 결과 의미를 직접 표현합니다.

8. Anti Join: 존재하지 않음

Anti Join은 두 번째 집합에 Match가 존재하지 않는 첫 번째 집합의 행을 반환합니다.

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

실행계획에는 다음 Operation이 나타날 수 있습니다.

NESTED LOOPS ANTI
HASH JOIN ANTI
MERGE JOIN ANTI

Semi·Anti Join은 첫 Match가 확인되면 존재 여부 판단이 끝날 수 있습니다. 실제 처리 방식은 선택된 Join Method와 실행계획에 따라 달라집니다.

9. NOT IN과 3값 논리

SELECT c.customer_id
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
);

Subquery 결과에 NULL이 포함되면 개념적으로 다음 조건이 포함됩니다.

c.customer_id <> 1
AND c.customer_id <> 3
AND c.customer_id <> NULL

값 <> NULL은 TRUE가 아니라 UNKNOWN입니다. WHERE는 TRUE만 통과시키므로 예상한 미존재 행이 반환되지 않을 수 있습니다.

9.1 안쪽 NULL 제거

WHERE c.customer_id NOT IN (
    SELECT o.customer_id
    FROM orders o
    WHERE o.customer_id IS NOT NULL
)

이 형태는 Subquery의 NULL 문제를 제거합니다. 그러나 바깥 c.customer_id가 NULL이면 NULL NOT IN (...) 역시 UNKNOWN이므로 바깥 NULL 행은 반환되지 않습니다.

같은 등치 상관 조건의 NOT EXISTS는 바깥 Key가 NULL이면 Match가 없다고 판단해 그 행을 반환할 수 있습니다. 따라서 두 표현은 바깥 Column의 NULL 가능성까지 비교해야 합니다.

9.2 Null-Aware Anti Join

Oracle Optimizer는 Nullable Subquery를 포함한 NOT IN을 처리할 때 다음 Operation을 사용할 수 있습니다.

ANTI NA  : Null-Aware Anti Join
ANTI SNA : Single Null-Aware Anti Join

이 Operation은 NOT IN의 NULL 의미를 보존하면서 Anti Join 최적화를 수행하기 위한 것입니다. ANTI NA가 보인다고 NOT IN과 NOT EXISTS의 결과 의미가 같아진다는 뜻은 아닙니다.

9.3 IN 목록의 NULL

WHERE status_code IN ('A', NULL)

이 조건은 status_code IS NULL을 의미하지 않습니다.

WHERE status_code = 'A'
   OR status_code IS NULL

NULL을 찾으려면 IS NULL을 명시합니다.

10. Outer Join과 집계

Outer Join 후 집계에서는 NULL 확장 행을 어떻게 세는지 확인해야 합니다.

SELECT c.customer_id,
       COUNT(*)          AS joined_rows,
       COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

주문이 없는 고객은 다음 결과를 가질 수 있습니다.

COUNT(*)          = 1
COUNT(o.order_id) = 0

COUNT(*)는 Outer Join으로 생성된 NULL 확장 행도 세지만, COUNT(o.order_id)는 NULL을 세지 않습니다.

또한 다음 값은 서로 다를 수 있습니다.

COUNT(*)                : Join 결과 행 수
COUNT(o.order_id)       : Match한 주문 행 수
COUNT(DISTINCT o.key)   : 서로 다른 주문 Key 수
SUM(o.amount)           : 미매칭 그룹에서는 NULL일 수 있음

Join을 EXISTS로 변경하거나 Join 순서를 바꿀 때 Aggregate 결과가 동일한지 반드시 검증합니다.

11. Join Type·Method·Order

Outer Join에서는 Preserved Row Source 때문에 가능한 Join Order에 제약이 생길 수 있습니다. Semi·Anti Join으로 변환된 Subquery도 외부 Query Block과의 의미를 보존하는 범위에서 순서가 결정됩니다.

실행계획에는 다음처럼 SQL Text의 좌우와 다른 방향 표현이 나타날 수 있습니다.

HASH JOIN RIGHT OUTER
HASH JOIN RIGHT SEMI

RIGHT만 보고 SQL Text의 오른쪽 Table이 반드시 보존된다고 단정하지 않습니다. Operation의 Child Row Source, Predicate Information, 실제 결과 Grain과 A-Rows를 함께 확인합니다.

12. Constraint와 Optionality

다음 조건이 실제로 보장되면 일부 Outer Join은 결과상 Inner Join과 같을 수 있습니다.

• Child Foreign Key가 NOT NULL
• Foreign Key가 활성화되고 검증됨
• Parent Key가 Primary 또는 Unique
• 참조 무결성이 유지됨
• 추가 Predicate가 관계를 깨뜨리지 않음

그러나 Outer Join은 Constraint를 대신하지 않습니다.

Optionality : 업무에서 관계가 없어도 되는가
Constraint  : 데이터가 규칙을 위반하지 못하게 하는가
Join Type   : 이번 조회에서 어떤 행을 보존하는가

이 세 가지를 분리해 설계합니다.

13. ANSI Join Syntax와 구식 (+) 표기

Oracle은 일반적으로 FROM ... JOIN ... ON 형태의 ANSI Join Syntax 사용을 권장합니다.

장점은 다음과 같습니다.

• Join Predicate와 최종 Filter를 구분하기 쉬움
• Left·Right·Full Outer Join의 보존 방향이 명확함
• 여러 조건 중 (+) 누락으로 조용히 Inner Join처럼 변하는 위험을 줄임
• Full Outer Join과 복잡한 Join 구조를 표현하기 쉬움

기존 (+) 문법을 유지보수할 때는 Outer Join 대상 Table의 관련 Join 조건에 표시가 일관되게 적용됐는지 확인합니다.

14. Runtime 검증 절차

SQL 변환 전후에는 작은 예제만이 아니라 실제 분포와 Runtime Plan을 비교합니다.

1. 결과 Grain과 보존 Row Source를 정의한다.
2. Join Key의 NULL·Unique·NOT NULL·FK 상태를 확인한다.
3. Match 0건·1건·다수인 Test Data를 준비한다.
4. 안쪽과 바깥 Key가 NULL인 사례를 포함한다.
5. ON·WHERE Predicate별 결과 행 수를 비교한다.
6. COUNT·SUM·DISTINCT 등 Aggregate 결과를 비교한다.
7. 실행계획의 OUTER·SEMI·ANTI·ANTI NA Operation을 확인한다.
8. Predicate Information에서 Access·Filter 위치를 확인한다.
9. Runtime A-Rows로 중복 증폭과 필터 탈락을 확인한다.
10. 결과가 같다는 것이 확인된 뒤 Join Method와 Access Path를 튜닝한다.

15. 혼동하기 쉬운 판단