FULL OUTER JOIN 을 UNION ALL + NOT EXISTS 로 변환
FULL OUTER JOIN은 OUTER JOIN + UNION ALL + NOT EXISTS 세 조각으로 분해할 수 있습니다.
핵심 정리
FULL OUTER JOIN≡(A 기준 OUTER JOIN) UNION (B 기준 OUTER JOIN)— 양쪽의 unmatched row 까지 모두 보존하기 위한 합집합입니다.- 두 번째
SELECT를NOT EXISTS로 좁히면 첫 번째SELECT와 결과 row 가 disjoint(겹치지 않음) 가 되므로,UNION의 중복 제거 부담을 떼어내고UNION ALL로 충분해집니다.
시나리오
EMPLOYEE 와 DEPARTMENT 를 department_id 로 연결하면서 부서가 없는 사원 + 사원이 없는 부서 까지 모두 보존하려는 쿼리입니다.
EMPLOYEE(PK =employee_id, FK =department_id)DEPARTMENT(PK =department_id, 인덱스EMP_DEPARTMENT_IXonEMPLOYEE.department_id)- 결과 컬럼: 사원 4개 + 부서명 — 양쪽 unmatched 도 포함되어야 하므로 어느 쪽도 NULL 이 될 수 있음
1
2
3
SELECT a.employee_id, a.first_name, a.last_name, a.email, b.department_name
FROM employee a FULL OUTER JOIN department b
ON (a.department_id = b.department_id);
1) NATIVE 표현 — FULL OUTER JOIN
옵티마이저는 NATIVE FULL OUTER JOIN 오퍼레이션으로 위 쿼리를 단일 패스 처리합니다. EMPLOYEE 와 DEPARTMENT 를 각 1회씩만 스캔하면서 매칭된 row, 부서 없는 사원, 사원 없는 부서를 한 번에 산출합니다. 별도 변환 없이 작성한 그대로 동작하므로 쿼리 표현이 가장 짧고, I/O 도 최소입니다.
2) 분해 표현 — OUTER JOIN + UNION ALL + NOT EXISTS
같은 결과를 세 조각의 등가 변환으로 풀어 쓴 형태입니다. 위쪽 SELECT 가 매칭된 row + EMPLOYEE 단독 row 를 모두 잡고, 아래쪽 SELECT 가 NOT EXISTS 로 사원이 없는 부서만 골라 합칩니다. 두 SELECT 의 결과가 서로 겹치지 않기 때문에 UNION 대신 UNION ALL 을 쓸 수 있습니다.
1
2
3
4
5
6
7
8
9
10
11
SELECT *
FROM (SELECT a.employee_id, a.first_name, a.last_name, a.email, b.department_name
FROM employee a, department b
WHERE a.department_id = b.department_id(+)
UNION ALL
SELECT NULL, NULL, NULL, NULL, b.department_name
FROM department b
WHERE NOT EXISTS (SELECT 1
FROM employee a
WHERE a.department_id = b.department_id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 123 | 8610 | 10 | 00:00:01 |
| 1 | VIEW | | 123 | 8610 | 10 | 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 107 | 5350 | 7 | 00:00:01 |
| 4 | TABLE ACCESS FULL | EMPLOYEE | 107 | 3210 | 3 | 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPARTMENT | 27 | 540 | 3 | 00:00:01 |
| 6 | NESTED LOOPS ANTI | | 16 | 368 | 3 | 00:00:01 |
| 7 | TABLE ACCESS FULL | DEPARTMENT | 27 | 540 | 3 | 00:00:01 |
|* 8 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 44 | 132 | 0 | 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID"(+))
8 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
Id 5 와 Id 7 둘 다 TABLE ACCESS FULL DEPARTMENT 입니다 — 같은 테이블이 두 번 스캔 되고 있습니다. 이것이 분해 표현의 본질적 비용입니다.
분석
분해가 성립하는 원리 (3 단계)
- 단계 1 —
FULL OUTER JOIN풀어 쓰기:A FULL OUTER JOIN B는(A 기준 OUTER JOIN) UNION (B 기준 OUTER JOIN)과 같습니다. 양쪽의 unmatched row 까지 보존하려면 어느 한쪽 기준으로는 부족하기 때문에 두 OUTER JOIN 의 합집합이 필요합니다. - 단계 2 —
UNION을UNION ALL로 다운그레이드: 두 번째SELECT를B WHERE NOT EXISTS (SELECT 1 FROM A WHERE A.key = B.key)로 좁히면, 첫 번째SELECT의 결과 (매칭된 row +A단독 row) 와 row 가 겹치지 않습니다.UNION의 정렬·중복 제거 비용이 무의미해지므로UNION ALL로 충분합니다. - 단계 3 — 최종 형태: 결과는
A LEFT JOIN B UNION ALL B WHERE NOT EXISTS A로 정리됩니다. OUTER JOIN 절 +UNION ALL+NOT EXISTS절의 조합입니다.
NATIVE 가 유리한 이유 — 실행계획에서 보이는 비용
분해 표현의 실행계획에서 Id 5 (HASH JOIN OUTER 의 build/probe 한쪽) 와 Id 7 (NESTED LOOPS ANTI 의 driving) 모두 DEPARTMENT 를 풀스캔합니다. 즉 DEPARTMENT 가 두 번 읽힙니다. NATIVE FULL OUTER JOIN 은 단일 패스로 양쪽 unmatched 까지 처리하므로 각 테이블 1회 스캔이면 충분합니다.
이 예제처럼 DEPARTMENT 가 작은 코드성 테이블이면 cost 차이가 미미하지만, fact-fact 두 큰 테이블 사이의 FULL OUTER JOIN 이라면 분해 표현은 양쪽 모두 I/O 가 2배가 됩니다.
분해 형태는 “옵티마이저가 내부에서 무엇을 하는지” 를 들여다보는 케이스를 정리한 예시입니다. 실무에서는 FULL OUTER JOIN 한 줄이 정답이며, 본 건은 방법을 이해하기 위한 예시 설명입니다.
정리
FULL OUTER JOIN은 OUTER JOIN + UNION ALL + NOT EXISTS 로 분해 가능 — 등가 변환의 원리만 머릿속에 두면 충분합니다.NOT EXISTS절이 두 SELECT 의 disjoint 를 보장하기 때문에UNION→UNION ALL다운그레이드가 가능합니다.- 실무는 NATIVE FULL OUTER JOIN — 같은 테이블 2회 스캔을 피하기 위해서입니다. 분해 형태는 NATIVE 오퍼레이션이 없던 시절의 동작 모델일 뿐 권장 패턴이 아닙니다.