Oracle OUTER JOIN 정리 4 Lateral View

1. 오라클 Lateral View 개념

Lateral View 의 정의는 Scalar Inline View 개념으로 FROM절에서 다른 테이블들과 참조하는 관계를 나타낼수 있는 Inline View라고 할수있습니다. 이 뷰는 결과 건수에는 영향이 없으며, 조인 건수에만 영향을 미칩니다. 이 이야기는 아래 예제를 봐야 쉽게 이해할수 있습니다.

가. SQL형태

가. ANSI SQL형태
SELECT a.empno, a.ename, b.loc, b.deptno
FROM EMP A LEFT OUTER JOIN DEPT B
ON A.deptno = B.deptno
and A.empno > 7400
order by 4; 

나. 오라클 SQL 형태
select a.empno, a.ename, b.loc, b.deptno
from EMP A,
     lateral (select b.loc, b.deptno
              from DEPT B
              where a.empno > 7400
                and b.deptno=a.deptno)(+) B;

나. 결과값

아래의 값을 살펴보면 인라인뷰안에서 EMPNO가 7400이상인 사람만 조회하도록 되어 있으나, 결과값은 7369 번도 같이 조회되고 있습니다. 왜 이럴까? Wrong Results(잘못된 값)인가? 아니다. Lateral View는 결과값에는 영향을 미치지 않지만 조인에는 영향을 미치는 뷰입니다. 그래서 Out Join과 비슷하게 결과값이 나온것입니다.

EMPNO	ENAME	LOC	DEPTNO
7782	CLARK	NEW YORK	10
7839	KING	NEW YORK	10
7934	MILLER	NEW YORK	10
7788	SCOTT	DALLAS	20
7876	ADAMS	DALLAS	20
7902	FORD	DALLAS	20
7566	JONES	DALLAS	20
7900	JAMES	CHICAGO	30
7844	TURNER	CHICAGO	30
7698	BLAKE	CHICAGO	30
7654	MARTIN	CHICAGO	30
7521	WARD	CHICAGO	30
7499	ALLEN	CHICAGO	30
7369	SMITH

2. 오라클 Lateral View 예제

가. 예제 1

아래의 예는 Left Outer Join을 하면서 ON절에 EMPNO > 7700이라는 조건을 넣었습니다. 오라클 Outer Join은 내부적으로 Lateral View를 이용하여 처리합니다. ON절에 조건을 넣게되면 나항과 같은 개념의 오라클 SQL이 되는데 실제 12C DB에서 이 SQL을 수행해보면 에러없이 값이 잘 조회됩니다.

가. ANSI SQL 형태
SELECT a.empno, a.ename, b.loc, b.deptno
FROM EMP A LEFT OUTER JOIN DEPT B
ON A.deptno = B.deptno
and A.empno > 7700
order by 4; 

나. 오라클 SQL 형태
select a.empno, a.ename, b.loc, b.deptno
from EMP A,
     lateral (select b.loc, b.deptno
              from DEPT B
              where a.empno > 7700
                and b.deptno=a.deptno)(+) B;

EMPNO	ENAME	LOC	DEPTNO
7782	CLARK	NEW YORK	10
7839	KING	NEW YORK	10
7934	MILLER	NEW YORK	10
7788	SCOTT	DALLAS	20
7876	ADAMS	DALLAS	20
7902	FORD	DALLAS	20
7566	JONES	DALLAS	20
7900	JAMES	CHICAGO	30
7844	TURNER	CHICAGO	30
7698	BLAKE
7654	MARTIN
7521	WARD
7499	ALLEN
7369	SMITH

나. 예제 2

이번에는 LEFT Outer JOIN을 이용하여 아래 표와 같이 최종값을 구해봅니다. EMPNO가 7700이상인 값을 구하게 되는데 WHERE절에 이조건을 기술하니 우리가 흔히 생각하는 형태의 최종값이 조회되었습니다. 위의 가 예제와 비교해보면 쉽게 Lateral View를 이해할수 있을것입니다.

SELECT a.empno, a.ename, b.loc, b.deptno
FROM EMP A LEFT OUTER JOIN DEPT B
ON A.deptno = B.deptno
where A.empno > 7700
order by 4; 

EMPNO	ENAME	LOC	DEPTNO
7782	CLARK	NEW YORK	10
7839	KING	NEW YORK	10
7934	MILLER	NEW YORK	10
7788	SCOTT	DALLAS	20
7876	ADAMS	DALLAS	20
7902	FORD	DALLAS	20
7566	JONES	DALLAS	20
7900	JAMES	CHICAGO	30
7844	TURNER	CHICAGO	30

3. 기타 Lateral View 특징

기존의 스칼라서브쿼리와 WHERE절에 들어가는 서브쿼리의 특징은 서브쿼리안에서 서브쿼리안에 없는 다른 외부테이블의 칼럼을 참조할수 있습니다. 그러나 FROM절에 들어가는 인라인뷰는 인라인뷰안의 FROM절에 기술되어 있지 않은 인라인뷰 바깥쪽 테이블의 칼럼을 참조할수 없다. 그러나 12C부터는 이것을 가능하게 해주는 뷰가 나왔다. 그것이 바로 Lateral View 입니다.

아래의 예제를 보면 FROM절에 인라인뷰 B가 있는데 B 인라인뷰의 FROM절에 기술안된 EMP 테이블의 a.deptno 칼럼을 WHERE절에 기숧하고 있습니다. 이 방법은 11G DB까지는 사용할수 없는 방법입니다. 12C 새기능으로 FROM절에 Lateral을 선언해주면 사용가능하다. EMP 테이블을 기준으로 한 Left Outer Join과 같은 결과값을 반환합니다.

SELECT a.empno, a.ename, b.dname
FROM       emp a,
   lateral (SELECT dname
            FROM   dept b
            WHERE a.deptno=b.deptno) b
ORDER BY 1, 2; 

4. 결언

오라클의 Lateral View기능은 12C DB부터 명시적으로 기술하여 사용할수 있게 되었습니다. 11G 까지는 내부적으로 동작하는 기능으로 동작되어 명시적으로 개발자들이 사용하기 어려웠습니다. 이번 기회에 Lateral View를 문법을 익혀 필요할때 유용하게 사용해봅니다. 차후에 이 뷰를 이용한 응용방법에 대해 포스팅하도록 하겟습니다.