오라클 SQL, 서브쿼리, 스칼라 서브쿼리(scalar subquery)

 

오라클 SQL, 서브쿼리, 스칼라 서브쿼리(scalar subquery)

오라클 9i에서 도입된 스칼라 서브쿼리scalar subquery는 하나의 단일 값이 출현할 수 있는 자리에 사용하는 서브 쿼리로 거의 모든 위치에서 사용할 수 있습니다. 다음은 스칼라 서브쿼리를 사용할 수 있는 곳 입니다.  만약 스칼라 서브 쿼리가 행을 반환하지 못하면 출력에 NULL 값을 사용하며 GROUP BY 절에서는 사용할 수 없습니다.

SELECT 문

CASE 표현식

INSERT 문의 VALUES 절

WHERE 절

ORDER BY 절

함수의 매개 변수

하나의 단일값을 선택하기 때문에 반드시 단일 값을 반환해야 합니다.

SELECT절의 컬럼 위치에  사용되는 경우 데이터 건수가 적을 때 조인 방식보다 유리합니다. 

다음은 부서별 사원수를 스칼라 서브쿼리로 구하는 예문 입니다.

부서(DEPT) 테이블의 행들을 하나씩 읽으면서 부서코드(DEPTNO), 부서명(DNAME)을 출력하고 그 행의 부서코드 값을 안쪽 서브쿼리에 넣어 서브 쿼리를 실행하여 해당 직원이 속한 부서의 사원수를 COUNT 합니다.

위의 쿼리를 인라인뷰, 조인을 이용하여 다시 작성하면 다음과 같습니다.

부서(DEPT) 테이블에는 40번 부서가 있지만 직원(EMP) 테이블에는 40번 부서원이 없으므로 인라인뷰 서브쿼리의 결과에서는 40번 부서가 없습니다.  그 결과와 DEPT 테이블이 조인을 하므로 40번 부서는 조회되지 않습니다.

5.5.1 직원 정보 추출하기 : 조인

조인을 이용하려면 양쪽 테이블에 조인키에 해당하는 컬럼이 있어야 합니다.조인은 조건이 양쪽 테이블 모두에서 정확히 일치하는 데이터만 추출됩니다. 조인을 사용하여 직원 정보를 추출해봅시다.

직원 테이블과 부서 테이블을 조인하여 사번, 직원명, 부서코드, 부서명을 추출합니다.

SELECT EMPNO              , ENAME              , EMP.DEPTNO              , DNAME FROM    EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; ❶

<실행 결과>

	EMPNO	ENAME	DEPTNO	JOB
1	7782	CLARK	10	ACCOUNTING
2	7934	MILLER	10	ACCOUNTING
3	7788	SCOTT	20	RESEARCH
…..	…...	…...	…...	…...
13	7521	WARD	30

부서명은 EMP 테이블에 없고 DEPT 테이블에 있습니다. 부서명을 얻기 위해 ❶에서부서코드 DEPTNO 컬럼을 조인키로 사용합니다. EMP 테이블에서 부서코드(DEPTNO)가 NULL인 KING 직원은 출력되지 않습니다. 

5.5.2 직원 정보 추출하기 : 스칼라 서브쿼리

위 쿼리를 스칼라 서브쿼리를 사용해서 수정 합시다.

 스칼라 서브쿼리는 SELECT되는 행이 없을 때 NULL을 출력으로 합니다. 그래서 부서코드가 NULL인 “KING” 직원은 부서명이 NULL로출력됩니다. 

아래 쿼리는 EMP 테이블의 전체 데이터가 기본적으로 출력됩니다.

SELECT EMPNO              , ENAME              , DEPTNO              , (SELECT DNAME FROM DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO) FROM    EMP;

<실행 결과>

	EMPNO	ENAME	DEPTNO	JOB
1	7369	SMITH	20	RESEARCH
2	7499	ALLEN	30	SALES
3	7521	WARD	30	SALES
4	7566	JONES	20	RESEARCH
5	7654	MARTIN	30	SALES
6	7698	BLAKE	30	SALES
7	7782	CLARK	10	ACCOUNTING
8	7788	SCOTT	20	RESEARCH
9	7839	KING	(null)	(null)
10	7844	TURNER	30	SALES
11	7876	ADAMS	20	RESEARCH
12	7900	JAMES	30	SALES
13	7902	FORD	20	RESEARCH
14	7934	MILLER	10	ACCOUNTING

5.5.3 부서별 직원 수 출력하기 : XXX

부서별로 직원 수를 출력해보겠습니다. 부서 직원 수가 NULL이면 0으로 대체하는 NVL() 함수를 사용하세요.

SELECT DEPTNO              , DNAME              , (SELECT ❶NVL(COUNT(*),0) FROM EMP                  WHERE EMP.DEPTNO = DEPT.DEPTNO)  AS EMP_CNT  FROM DEPT;

<실행 결과>

	DEPTNO	DNAME	EMPCNT
1	10	ACCOUNTING	2
2	20	RESEARCH	5
3	30	SALES	6
4	40	OPERATIONS	0

❶ NVL() 함수로 COUNT(*) 결과가 NULL이면 0으로 대체합니다. 

DEPT 테이블의 행들을 하나씩 읽어들이면서 읽은 DEPTNO 값을 서브쿼리에 넣어 EMP 테이블에서 해당 부서원들의 수를  카운트 하여 결과를 반환합니다. 이러한 스칼라 서브쿼리 는 DEPT 테이블의 건수가 아주 많다면 성능상 좋지 않을 수 있지만 건수가 얼마 되지 않는 다면 조인보다 성능이 좋을 수 있습니다.

5.5.4 INSERT절에 서브쿼리 사용하기 : 스칼라 서브쿼리

INSERT 절에 스칼라 서브쿼리를 사용한 예문입니다.

CREATE TABLE EMP_SUMMARY ( ❶    SUM_SAL NUMBER,    AVG_SAL NUMBER,    MAX_SAL NUMBER,    MIN_SAL NUMBER ); INSERT INTO EMP_SUMMARY (           SUM_SAL ,           AVG_SAL ,           MAX_SAL ,           MIN_SAL )    VALUES (             (SELECT SUM(SAL) FROM EMP), ❷             (SELECT ROUND(AVG(SAL), 0) FROM EMP),             (SELECT MAX(SAL) FROM EMP),             (SELECT MIN(SAL) FROM EMP)           );            COMMIT; SELECT * FROM EMP_SUMMARY ;

<실행 결과>

	SUM_SAL	AVG_SAL	MAX_SAL	MIN_SAL
1	29025	2073	5000	800

 

❶ 4개의 컬럼을 가지는  EMP_SUMMARY 테이블을 생성 합니다.

❷ INSERT절의 VALUES구에서 스칼라 서브쿼리를 사용하여 단일 값을 조회하여 삽입 합니다.​

 

#오라클 #SQL #서부쿼리 #스칼라서브쿼리