ORACLE OUTER JOIN
from
1. ORACLE OUTER JOIN
OUTER JOIN 은 JOIN 연결고리 조건을 만족하지 못하는 ROW 에 대해서도 전부 표현하기
위한 JOIN 의 또다른 방법이다.
흔히 사용되는 간단한 예를 이야기 하면 EMPLOYEE 와 DEPARTMENTS 가 있을 때
아직 부서에 발령되지 않은 신입이 있다고 가정하자. 하지만 모든 EMPLOYEE 의
소속부서에 대해 (값의 유무를 떠나) 조회하고 싶을 때 OUTER JOIN 을 사용한다.
OUTER JOIN 의 특징으로는 JOIN 의 방향이 고정된다. 조인의 방향이란 DRIVING 되는
테이블을 의미하며 다시말해 조인을 위해 무엇을 먼저 엑세스하느냐란 물음에 항상 고정된 답변을 하게 된다란 이야기이다. 앞서 사용한 EMPLOYEE, DEPARTMENTS 를 예로들면
EMPLOYEE 를 전부 표현하기 위한 OUTER JOIN 을 해야 한다 할 때 반드시
EMPLOYEE 가 먼저 엑세스 된다.
OUTER JOIN 의 방법으로는 ANSI 방법이 있고 ORACLE 방법이 있다. 전자는 LEFT|RIGHT|FULL OUTER JOIN 과 같이 구문을 사용하며 후자는 + 기호를 사용한다.
select count(*) from(
select e.first_name,d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id
)
/
select count(*) from(
select e.first_name,d.department_name
from employees e right outer join departments d
on e.department_id = d.department_id
)
/
위 두개의 쿼리는 같은 결과값을 가진다. 단지 그 결과를 얻기위한 표현이 다를 뿐이다.
DRIVING TABLE 이 어떤것이냐란 물음에 대해 상/하 쿼리는 각기 다음과 같이 이야기 할 수 있다.
상 : (+) 기호가 없는 테이블이 DRIVING 되는 테이블
하 : ( LEFT | RIGHT ) 방향에 있는 테이블이 DRIVING 되는 테이블
2. OUTER JOIN 예제
<OUTER JOIN 표현과 EQUI JOIN 이 섞여있을 때>
select e.employee_id,e.first_name,d.department_name
from employees e,departments d
where e.employee_id = d.manager_id
and e.department_id = d.department_id(+)
/
outer join 은 무시된다.
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 363 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 11 | 363 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1498 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 19 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
단순히 equi join 으로 풀린다.
<OUTER JOIN 을 JOIN 대상에 대해 일관되게 표현했을 때>
select e.employee_id,e.first_name,d.department_name
from employees e,departments d
where e.employee_id = d.manager_id(+)
and e.department_id = d.department_id(+)
/
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 3531 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 107 | 3531 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1498 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 19 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
OUTER JOIN 으로 풀린다.
<OUTER JOIN 을 JOIN 대상에 대해 비일관하게 표현했을 때>
where e.employee_id = d.manager_id(+)
and e.department_id(+) = d.department_id
앞서 본 쿼리의 조인조건을 위와 같이 바꾸면 아래와 같은 에러를 볼 수 있다. 조인은 두 대상에
이루어지는 것이고 DRIVING 대상은 택일이다. 때문에 에러가 난다.
ERROR at line 3:
ORA-01416: two tables cannot be outer-joined to each other
<OUTER JOIN 의 변형>
OUTER JOIN 은 SCALAR SUB QUERY 로 변형할 수 있다.
하지만 제약사항이 있다. (SCALAR SUB QUERY 문서 참고)
그 제약에 대해 무엇이 드라이빙 되는가에 따라 살펴보겠다.
<CASE1>
select e.first_name,d.department_name
from employees e, departments d
where e.department_id = d.department_id (+)
CASE1 의 DRIVING TABLE 은 EMPLOYEES 이다. 결과적으로 의미를 보면 소속불명의 EMPLOYEE 까지
전부 표현하게 되고 이는 EMPLOYEES 수와 동일하다. 이 경우 SCALAR SUB QUERY 로 변경가능하다.
SCALAR SUB QUERY 는 FUNCTION 과도 같아서 입력에 대해 하나의 결과를 얻어야 한다는 제약이 있다.
select e.first_name,
(select department_name
from departments
where department_id = e.department_id)
department_name
from employees e
/
소속불명 ‘Kimberely’ 의 department_name 은 null 로 나온다.
위와 같이 변경이 가능한 것은 outer join 을 하든 scalar sub query 로 풀든 각employees.department_id 에 대해 그 결과 건수 1:1 로 변하지 않기 때문이다.
<CASE2>
select e.first_name,d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id
위 쿼리의 결과건수는 122건으로 employees, departments 어느 테이블의 결과건과도 일치하지
않는다. Department_id 에 대해 JOIN 에 성공한 모든 테이블 (106건) + employee 가 없는 department (16건) => 122 건이다.
이 쿼리를 스칼라서브쿼리로 변경한다고 할 때 문제가 발생한다. CASE1 같이 스칼라서브쿼리의 메인쿼리에 DRIVING TABLE(여기서는 DEPARTMENT_ID) 를 넣으면 결과건수는 DEPARTMENTS ROWS 만큼이다.
결과건수의 불일치를 볼 수 있으며 이는 DRIVING 에 EMPLOYEES 를 가정해도 마찬가지이다.
추가적인 DEPARTMENTS 가 DRIVING 이면 생길 수 있는 문제는 하나의 DEPARTMENT_ID 에 대해 N
만큼의 EMPLOYEE 가 있을 수 있다는 것이다. 이는 스칼라서브쿼리의 하나의 입력에 하나의 출력이란
법칙에 어긋난다.
<CASE1>,<CASE2> 를 보았을 때 스칼라 서브쿼리로 변경가능한 것은 1:N 관계에서 N 쪽임을 알 수
있다.
*정말 오랜만에 쓰는 기술문서? 이다. 정신오염도(수면욕)가 심한 상태라 글에 헛소리가 있을까 걱정된다;*
