오라클 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 #서부쿼리 #스칼라서브쿼리

오라클 서브쿼리, 상관관계 서브쿼리(Corelated Subquery)

오라클 서브쿼리, 상관관계 서브쿼리(Corelated Subquery)

상관관계 서브쿼리(correlated subquery)는서브쿼리와 메인쿼리가 서로 참조하는 쿼리입니다. 서브쿼리에서 메인쿼리의 컬럼을 이용하는 경우로 상관 서브쿼리, 상호연관 서브쿼리 등으로도 부릅니다.

일반적인 쿼리는 서브쿼리 결과를 메인쿼리에서 단순히 이용만 합니다. 반면 상관관계 서브쿼리에서는 서브쿼리가 메인쿼리의 값을 이용하여 값을 구하면 ➝ 그 값을 다시 메인쿼리에서 이용합니다. 이때 서브쿼리는 값을 확인하는 확인자 역할을 하게 됩니다. 

예를 들어 서브쿼리에서 10번, 20번, 30번 부서에서 가장 많은 급여를 받는 사람을 구하고자 한다면 부서 외에 다른 코드는 같습니다. 이때 상관관계 서브쿼리로 만들어 처리하면 편리합니다.

상관관계 서브쿼리 실행 순서

1. 바깥쪽 메인쿼리를 실행하여 행을 하나 읽습니다. 

2. 1에서 읽은행의 값을 이용하여 서브쿼리에서 필요한 값을 넣고 서브쿼리를 수행합니다.

3. 2의 결과값으로 메인쿼리의 WHERE 절을 평가하여 읽은 행의 선택여부를 결정 합니다. 참이면 데이터 추출, 아니면 버리고 메인쿼리의 다음 레코드를 읽습니다.

4. 메인쿼리의 테이블에 행이 없을 때까지 1~3을 반복 수행합니다.

부서별 최대급여 받는 직원 정보를 추출하는데 상관관계 서브쿼리를 이용한 방법과 페어와이즈 서브쿼리를 이용한 방법으로 구현해 보겠습니다. 상관관계 서브쿼리는 바깥쪽 메인을 하나씩 읽어 안쪽 서브쿼리에 넣고 서브쿼리를 실행하여 그 결과랑 WHERE절을 판단하는 반면  페어와이즈 서브쿼리를 이용하는 방법은 서브쿼리를 전체 먼저 실행하고 그 결과를 가지고 메인쿼리를 한번 실행 합니다..

5.4.1 부서별 최대 급여를 받는 직원 정보 출력하기 : 상관관계 서브쿼리

상관관계 서브쿼리로 구현한 예문을 살펴봅시다.

부서별 최대 급여를 받는 직원의 부서코드, 이름, 급여를 출력합니다. 이때 부서코드순으로 오름차순 정렬하여 출력하세요.

SELECT DEPTNO, ENAME, SAL  ❶ ➍ ➎ FROM    EMP e1 WHERE ❸SAL = (SELECT MAX(SAL)                             FROM    EMP e2                           WHERE ❷e1.DEPTNO = e2.DEPTNO ) ORDER BY DEPTNO;

<실행 결과>

	DEPTNO	ENAME	SAL
1	10	CLARK	2450
2	20	FORD	3000
3	20	SCOTT	3000
4	30	BLAKE	2850

<실행 순서>

❶ EMP 테이블에서 맨 처음 행을 하나 읽습니다. 

❷ 읽은 직원의 부서코드를 서브쿼리로 넣고 서브쿼리를 먼저 실행합니다.

❸ WHERE 절에서 서브쿼리가 구한 최소급여를 메인쿼리에서 읽은 직원과 비교하여 즉 메인쿼리에서 읽은 직원의 급여가 자신이 속한 부서의 최소 급여인지를 WHERE 절을 비교하여 조건에 맞으면 보관하고 아니면 버립니다.

➍ 메인쿼리에서 다음 직원을 읽고 동일한 방식으로 진행하여 최종 결과를 만들어냅니다.

➎ 메인쿼리의 테이블에 행이 없을 때까지 ❶~❸을 반복 수행합니다.

5.4.2 부서별 최대 급여를 받는 직원 정보 출력하기 : 페어와이즈 서브쿼리

위 쿼리문을 페어와이즈 서브쿼리로 구현해봅시다.

부서별 최대 급여를 받는 직원의 부서코드, 이름, 급여를 출력합니다. 이때 부서코드순으로 오름차순 정렬하여 출력하세요.

SELECT DEPTNO, ENAME, SAL  FROM     EMP  WHERE (DEPTNO, SAL ) IN (SELECT DEPTNO, MAX(SAL)                                                  FROM    EMP                                                  GROUP BY DEPTNO) ORDER BY DEPTNO;

<실행 결과>

	DEPTNO	ENAME	SAL
1	10	CLARK	2450
2	20	FORD	3000
3	20	SCOTT	3000
4	30	BLAKE	2850

<실행 순서>

❶ 서브쿼리를 먼저 실행합니다.

<실행 결과>

	DEPTNO	MAX(SAL)
1	30	2850
2	10	2450
3	(null)	5000
4	20	3000

❷ 서브쿼리의 결과를 받아서 메인쿼리가 실행되어 최종 결과를 반환합니다.

SELECT DEPTNO, ENAME, SAL

FROM    EMP 

WHERE (DEPTNO, SAL ) IN ( (30, 2850), (10, 2450), (null, 5000), (20, 3000))

ORDER BY DEPTNO;

3번째 DEPTNO가 null인 데이터는 연산 결과도 null이 되므로 출력되지 않습니다.

5.4.3 직무별 최대 급여를 받는 직원 정보 출력하기 : 상관관계 서브쿼리

직무(JOB)별로 최대 급여를 받는 직원의 직무, 이름, 급여를 출력합니다. 직무명으로 오름차순 정렬하여 출력하는 예문입니다.

SELECT JOB, ENAME, SAL  ❶ ➍ ➎ FROM    EMP e1 WHERE SAL = (SELECT MAX(SAL)                            FROM    EMP e2                           WHERE ❷e1.JOB = e2.JOB) ORDER BY DEPTNO;

<실행 결과>

	JOB	ENAME	SAL
1	CLERK	MILLER	1300
2	MANAGER	JONES	2975
3	ANALYST	SCOTT	3000
	ANALYST	FORD	3000
	SALESMAN	ALLEN	1600
	PRESIDENT	KING	5000

❶ 메인쿼리를 통해 EMP 테이블에서 행 하나를 읽습니다.

❷ 읽은 행의 데이터 중 직무(JOB) 컬럼을 서브쿼리로 넣어주고 서브쿼리를 실행합니다.

❸ WHERE 절에서 서브쿼리가 구한 직무의 최대급여를 메인쿼리에서 읽은 직원의 급여와 비교하여, 즉 메인쿼리에서 읽은 직원의 급여가 자신이 속한 직무의 최대 급여인지를 WHERE 절을 비교하여 조건에 맞으면 보관하고 아니면 버립니다.

➍ 메인쿼리에서 다음 직원 데이터를 읽어 동일한 순서대로 작업을 진행하여 최종 결과 리스트를 만들어 반환합니다.

➎ 메인쿼리의 테이블에 행이 없을 때까지 ❶~❸을 반복 수행합니다.

 

#오라클 #ORACLE #서브쿼리 #Subquery #상관서브쿼리​

오라클 SQL, 서브쿼리, 복수행 서브쿼리(Multi-Row Subquery)

오라클 SQL, 서브쿼리, 복수행 서브쿼리(Multi-Row Subquery) 복수행 서브쿼리 복수행 서브쿼리(multi-row subquery)는 서브쿼리에서 여러 결과가 반환되는 구조입니다. IN, ANY, SOME(ANY와 동일), ALL, EXISTS 같은 복수행 연산자를 사용합니다(2.5절 ‘연산자’ 참조). 예문을 따라 하면서 알아봅시다. 5.3.1 부서별로 급여를 가장 적게 받는 직원 정보 출력 : IN IN 연산자는 오른쪽 서브쿼리에서 반환되는 행(ROW)과 컬럼값을 비교하는 데 사용합니다. IN 연산자를 사용한 복수행 서브쿼리를 만들어 봅시다. 직원(EMP) 테이블에서 부서별로 급여를 가장 적게 받는 직원의 부서, 이름, 급여를 출력 하는 예문을 만들어 봅시다. IN 연산자 오른쪽 서브쿼리에서 반환되는 행(ROW)은 부서별로 가장 적은 급여가 추출되어 메인쿼리와 IN 연산자에 의해 비교됩니다. 결국 부서별로 급여를 가장 적게 받는 직원을 선택하게 됩니다. SELECT DEPTNO, ENAME, SAL ❷ FROM EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MIN(SAL) ❶ FROM EMP GROUP BY DEPTNO); <실행 결과> 　 DEPTNO ENAME SAL 1 30 JAMES 950 2 10 MILLER 1300 3 20 SMITH 800 ❶ 서브쿼리가 먼저 실행이 되어 아래의 결과를 반환합니다. 　 DEPTNO MIN(SAL) 1 30 950 2 10 1300 3 (null) 5000 4 20 800 ❷ 메인쿼리의 WHERE 절에 있는 IN 연산자가 위 결과를 바탕으로 실행되어 최종 결과를 반환합니다. SELECT DEPTNO, ENAME, SAL FROM EMP WHERE (DEPTNO, SAL) IN ( (30, 950), (10, 1300), (NULL, 5000), (20, 800) ) DEPTNO가 NULL인 3번째 데이터는 연산 결과도 NULL이 되므로 출력에서 제외되었습니다. 위와 같은 WHERE 비교를 페어와이즈(pairwise) 방식이라 합니다. 컬럼을 쌍으로 묶어서 비교하는 것인데 만약 아래처럼 쿼리문을 작성한다면 예상치 못한 결과가 나올 수도 있습니다. 현재 EMP 테이블은 다행히도 최소급여인 1300, 950, 800을 다른 부서직원들이 가지고 있지 않아서 페어와이즈 방식으로 하지 않더라도 결과는 같지만 다른 부서원이 어떤 부서의 최소값을 가지면 출력됩니다. SELECT DEPTNO, ENAME, SAL FROM EMP WHERE SAL IN (SELECT MIN(SAL) ❶ FROM EMP GROUP BY DEPTNO) AND DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP); ❷ <실행 결과> 　 DEPTNO ENAME SAL 1 20 SMITH 800 2 30 JAMES 950 3 10 MILLER 1300 ❶ : 서브쿼리의 결과로 800, 950, 1300, 5000 이 나옵니다. ❷ : 서브쿼리의 결과로 10, 20, 30, null 이 나옵니다. 결국 위 쿼리는 SAL IN (800, 950, 1300, 5000) AND DEPTNO IN (10, 20, 30, null) 로 조건절이 실행되고, null은 비교대상에서 제외되므로 급여가 5000인 데이터는 부서코드가 null 이기에 제외 되었습니다. 만약 30번 부서원중 급여가 20번 부서의 최소값인 800을 가진 사원이 있었다면 출력이 되었겠죠? 결국 부서별 최소 급여의 조건에 맞지 않으므로 위 쿼리문은 잘못 사용된 것 입니다. 5.3.2 특정 부서의 최소 급여보다 급여를 많이 받는 직원정보 출력 : ANY ANY 연산자는 조건을 만족하는 값이 하나라도 있으면 결과를 리턴하는 SQL 함수 입니다. 이를 이용하여 직원의 급여가 10번 부서원들의 최소급여 보다 많이 받는 직원의 정보를 출력해 봅시다. ANY 연산자를 이용하여 10번 부서 급여의 최소값보다 많은 급여를 받는 직원정보를 쿨력해 봅시다. SELECT ❸ENAME, SAL FROM EMP WHERE❷SAL > ANY (SELECT SAL FROM EMP ❶ WHERE DEPTNO = 10); <실행 결과> 　 ENAME SAL 1 KING 5000 2 FORD 3000 3 SCOTT 3000 4 JONES 2975 5 BLAKE 2850 6 CLARK 2450 7 ALLEN 1600 8 TURNER 1500 <실행 순서> ❶ EMP 테이블에서 10번 부서 직원들의 급여 목록을 확인합니다. <실행 결과> 　 SAL 1 2450 2 1300 ❷ 조건절이 실행됩니다. 10번 부서의 급여 목록을 받아왔으니, 결과를 반영하여 WHERE SAL > ANY (1300, 2450)가 실행됩니다. > ANY 연산자이므로 최솟값인 1300보다 큰 행을 추출합니다. 즉 직원 테이블의 모든 행을 하나씩 읽어 들여 그 직원의 급여가 1300 보다 큰지, 2450 보다 큰지 비교하여 둘중 하나라도 만족하면 참이 되고 추출 됩니다. ❸ 서브쿼리 결과에서 이름과 급여 정보를 출력합니다. SAL < ANY(1300, 2450) 조건으로도 출력해봅시다. SELECT ENAME, SAL FROM EMP WHERE ❶SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 10); <실행 결과> 　 ENAME SAL 1 SMITH 800 2 JAMES 950 3 ADAMS 1100 4 WARD 1250 5 MARTIN 1250 6 MILLER 1300 7 TURNER 1500 8 ALLEN 1600 직원 테이블의 모든 행을 탐색 하면서 급여가 1300 보다 적은지, 급여가 2450 보다 적은지를 비교하여 둘 중 하나라고 만족하면 참이 되어 추출 됩니다. 즉 ❶ SAL값이 서브쿼리 결과의 최댓값보다 적어야 조건을 만족하므로 급여가 2450보다 적은 직원의 이름과 급여 정보를 출력합니다. ​ #오라클 #서브쿼리 #subquery #복수행서브쿼리