오라클 SQL, 서브쿼리, 인라인뷰, 래터럴 인라인뷰(lateral inline view)

 

오라클 SQL, 서브쿼리, 인라인뷰, 래터럴 인라인뷰(lateral inline view)

FROM절 다음에는 테이블, 뷰 등이 올수 있습니다. 테이블을 대신해 서브쿼리 형태로 SELECT 구문도 올 있는데 이를 인라인뷰 서브쿼리(inline view subquery)라고 합니다. 

서브쿼리는 FROM 절에서도 사용 가능한데 이를 뷰인데 CREATE VIEW 명령어로 만들지 않고 SQL 문의 해당 라인에 직접 기술한다고 해서 인라인 뷰(inline view)라고 합니다. SQL 문 라인에 직접 기술하니 필요한 시점에만 사용되는 특징이 있습니다.

어떤 테이블의 데이터 건수, 컬럼 개수가 많다고 했을 때 FROM 절 다음에 테이블명을 기술하여 전체 테이블 데이터를 메모리에 올리고 조인 등을 수행하면 쿼리 실행 시 수행 속도에 악 영향을 미칠 수가 있습니다. 

FROM 절 뒤에 테이블 데이터 중 필요한 행과 열만 선택하여 타깃 데이터 자체를 줄이면 더 효율적으로 쿼리할 수 있겠죠. 이때 인라인뷰를 사용하면 조인 작업을 없애거나 복작합 쿼리를 단순화할 수 있습니다. 인라인뷰 자체가 테이블 역할을 하므로 다른 테이블과 조인 연산 등도 가능합니다.

인라인 뷰는 테이블을 대체해 파생테이블 이라고도 합니다.

뷰(view)는 하나 이상의 테이블을 대상으로 일부 컬럼 또는 일부 행만 보여주는 가상 테이블입니다. 실제 저장 공간을 차지하지는 않고 SQL 문만 보관하는 오라클 스키마 객체입니다(13.1절 ‘뷰(View)란?’ 참고).

사원(EMP) 테이블에서 급여가 많은 순으로 3명의 사원의 이름, 급여를 조회하는 쿼리를 인라인뷰를 이용하여 작성해 보겠습니다.

FROM절에서 인라인 뷰를 사용하여 급여 내림차순으로 정렬 후 바깥쪽 SELECT의 WHERE절에서 ROWNUM을 이용하여 3건만 조회 하였습니다. 왜 이런 결과를 만들기 위해 인라인 뷰를 사용했을까요?

다음 쿼리문을 살펴보세요.

위 쿼리문은 EMP 테이블 원본에서 3건을 꺼내서 급여 내림차순으로 

내림차순 정렬하여 결과를 출력 했습니다. 이전 쿼리와는 내용도, 결과도 확연히 다릅니다.

5.6.1 테이블을 조인하여 직원 정보 출력하기 :  인라인뷰

인라인뷰를 이용하여 EMP 테이블과 DEPT 테이블을 조인하여 성명, 부서명을 출력하세요.

SELECT ENAME, DNAME FROM  (                 SELECT ENAME, DEPTNO FROM EMP ❶              ) E             ,(                SELECT DEPTNO, DNAME  FROM  DEPT ❷              ) D WHERE E.DEPTNO = D.DEPTNO ;

<실행 결과>

	ENAME	DNAME
1	CLARK	ACCOUNTING
2	MILLER	ACCOUNTING
...	...	...
13	WARD	SALES

❶❷ EMP, DEPT 테이블의 컬럼 개수가 많아서 필요한 컬럼만 선별 하여 인라인 뷰로 만들었습니다.

5.6.2 부서별 급여 합을 출력하세요 :  인라인뷰

인라인뷰를 이용하여 부서별 급여의 합을 출력하세요. 부서이름, 급여의 합 두 컬럼으로 데이터를 추출하세요.

SELECT DNAME, MAX_SAL FROM  (            SELECT DEPTNO, SUM(SAL) AS MAX_SAL  ❷            FROM   EMP            GROUP BY DEPTNO         ) E         ,(           SELECT DEPTNO, DNAME  FROM  DEPT ❶         ) D WHERE E.DEPTNO = D.DEPTNO ;

<실행 결과>

	DNAME	MAX_SAL
1	ACCOUNTING	3750
2	RESEARCH	10875
3	SALES	9400

❶ 부서명은 DEPT 테이블에, ❷ 부서별 급여합은 DEPTNO를 GROUP BY하여 EMP 테이블을 이용하여 추출 했습니다.  DEPTNO를 조인키로 두 인라인 뷰를  조인하여 부서명과 부서별 급여합을 출력했습니다.

5.6.3 부서별 급여 합을 출력하세요 :  조인

인라인뷰 대신 조인으로 부서별 급여의 합을 출력하세요. 부서이름, 급여의 합 두 컬럼으로 데이터를 추출하세요.

SELECT DNAME, SUM(E.SAL) AS SUM_SAL FROM    EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO ❶ GROUP BY DNAME; ❷

<실행 결과>

	DNAME	SUM_SAL
1	RESEARCH	10875
2	SALES	9400
3	ACCOUNTING	3750

 ❶ EMP, DEPT를 DEPTNO를 조인키로 조인을한 후 ❷ DNAME 컬럼으로 GROUP BY 하여 부서별 급여합을 구했습니다.  앞의 쿼리와 같은 내용 이지만 출력되는 순서가 다릅니다.  두 쿼리의 실행 계획이 달라서 테이블 데이터를 접근하는 방식이나 순서가 다르기 때문입니다. SQL Developer의 쿼리문에서 F10을 눌러 실행계획을 확인해 보세요.

5.6.4 래터럴 인라인뷰

래터럴 인라인뷰(lateral inline view)는 인라인 뷰 안에서 바깥쪽 메인쿼리의 테이블과 조인이 가능한 인라인 뷰 입니다. 기본적으로 인라인 뷰에서는 바깥쪽 메인 테이블과 조인이 불가능 하지만  LATERAL 키워드를 사용하면 가능합니다. 

오라클 12C 이후 지원하는 LATERAL 키워드를 사용하면 다음 예와 같이 인라인뷰가 FROM 절에서 인라인뷰 정의 왼쪽에있는 테이블의 컬럼들을 참조할 수 있습니다. 여기서 주의할 점은 인라인 뷰에서 참조하는 테이블은 왼쪽에 있어야 한다는 겁니다. 

사용 형식은 다음과 같습니다. 

예문를 보면서 래터럴 인라인뷰 사용법을 익혀봅시다. 

직원들의 이름과 부서명을 출력하는데, 다음과 같은 쿼리문은 오류가 발생 합니다.

SELECT ENAME, DNAME FROM EMP E          , (SELECT DNAME FROM DEPT D             WHERE D.DEPTNO = ❶E.DEPTNO) ;

<실행 결과>

ORA-00904: "E"."DEPTNO": 부적합한 식별자

❶ 바깥쪽 테이블인 EMP의 DEPTNO는 인라인뷰에서 참조할 수가 없어서 발생한 오류입니다.

LATERAL 키워드를 사용하면 바깥쪽 테이블인 EMP의 DEPTNO를 참조할 수 있습니다.

SELECT ENAME, DNAME FROM EMP E      ,  LATERAL ❶         (SELECT DNAME FROM DEPT D          WHERE D.DEPTNO = ❷E.DEPTNO) ;

<실행 결과>

	ENAME	DNAME
1	CLARK	ACCOUNTING
2	MILLER	ACCOUNTING
...	...	...
13	WARD	SALES

❶ LATERAL 키워드를 EMP E 다음에 기술하여 ❷ EMP 테이블의  컬럼 DEPTNO를 

인라인 뷰에서 참조했습니다. 

<주의>

EMP E가 LATERAL 키워드보다 오른쪽에 위치하면 오류가 발생됩니다.

SELECT ENAME, dname FROM       LATERAL (SELECT dname FROM dept D                     WHERE D.DEPTNO = E.DEPTNO) D1     ,EMP E

<실행 결과>

ORA-00904: "E"."DEPTNO": 부적합한 식별자

5.6.5 부서원 급여를 업데이트하기 : 인라인뷰

인라인뷰를 DML인 UPDATE 구문에서 사용해보겠습니다.

30번, ‘SALES’ 부서원들의 급여를 10으로 나누고 업데이트하세요.

SELECT ENAME, SAL FROM EMP WHERE DEPTNO = 30;

<실행 결과>

	ENAME	SAL
1	ALLEN	1600
2	WARD	1250
...	...	...
6	JAMES	950

UPDATE         (  ❶             SELECT SAL             FROM   EMP E INNER JOIN DEPT D             USING  (DEPTNO)             WHERE D.DNAME = 'SALES'         ) SET SAL = SAL / 10; ❷

<실행 결과>

6개 행 이(가) 업데이트되었습니다.

 ❶ UPDATE 명령어 다음에는 수정을 원하는 타겟인 테이블명이 와야 하지만 인라인 뷰가 왔습니다. 즉 인라인 뷰가 수정이 되야 하는 타겟이고 대상은 부서명이 SALES인 사원들의 급여 입니다. ❷ SET 다음에 수정을 원하는 컬럼과 수정될 값을 기술하는데 급여를 10으로 나누었습니다.

수행 결과를 확인합시다.

SELECT ENAME, SAL FROM EMP WHERE DEPTNO = 30;

<실행 결과>

	ENAME	SAL
1	ALLEN	160
2	WARD	125
...	...	...
6	JAMES	95

다음 실습을 위해 수정된 데이터를 원래대로 되돌립시다.

ROLLBACK;

5.6.6 특정 직원 데이터를 삭제하기 : 인라인뷰

인라인뷰를 DELETE 구문에서 사용해 보겠습니다.

30번, “SALES” 부서원 중 급여가 1000보다 작은 직원은  “JAMES”입니다. 이 직원을 삭제합시다.

DELETE         ( ❶             SELECT SAL             FROM   EMP E INNER JOIN DEPT D             USING  (DEPTNO)             WHERE D.DNAME = 'SALES'         ) WHERE SAL< 1000; ❷

<실행 결과>

1 행 이(가) 삭제되었습니다.

❶ DELETE 키워드 다음에는 삭제될 데이터를 가지고 있는 테이블명이 오는데 이 SQL문장 에서는 인라인 뷰가 왔습니다.  부서명이 SALES인 직원들의 급여(SAL)가 삭제 대상 입니다. 삭제는 급여(SAL) 컬럼만 하는것이 아니라 그 값을 가지는 행을 삭제합니다. ❷ 급여가 1000 보다 작으면 삭제 됩니다.

수행 결과를 확인합시다.

SELECT ENAME, SAL FROM EMP WHERE DEPTNO = 30;

<실행 결과>

	ENAME	SAL
1	ALLEN	1600
2	WARD	1250
...	...	...
5	TUNNER	1500

#oracle #오라클 #SQL #서브쿼리 #인라인뷰 #래터럴 인라인뷰 #lateralinlineview