SUBQUERY
하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문장이다.
서브쿼리는 메인쿼리가 실행되기 이전에 한번만 실행되며,
비교연산자의 오른쪽에 기술해야 하며, 반드시 괄호로 묶어야 한다.
또한 서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT한 항목의 개수와 자료형을 일치시켜야 한다
서브쿼리는 SELECT, FROM, WHERE, HAVING, ORDER BY 절에서 사용할 수 있다.
예를 들어 SELECT 해야하는 컬럼에 대한 조건값도 조회해봐야 알 수 있을 때 서브쿼리를 이용한다.
어떠한 사원A가 속한 부서와 같은 부서의 직원을 조회한다고 할 때,
사원 A가 속한 부서가 메인 쿼리에서의 조건이지만 이 역시 SELECT해봐야 알 수 있다.
SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '사원A';
사원A의 부서코드를 알아낸 뒤
SELECT
EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = '사원A의 부서코드';
사원A의 부서코드를 통해 같은 소속의 직원 명단을 조회 할 수 있다.
이 두 쿼리를 하나로 작성하는 것이 바로 SUBGUERY 이다.
SELECT
EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '사원A');
서브쿼리의 결과 값이 한 개이냐 다중 행, 혹은 다중 열 이냐에 따라
서브쿼리에 사용할 수 있는 연산자의 종류가 다르다.
서브쿼리의 여러 유형에 대해 알아보고, EMPLOYEE 테이블에서 여러 RESULT SET을 구하는 예시를 작성해본다.
1. 단일행 서브쿼리
서브쿼리의 조회 결과 값의 개수가 1개 일 때
단일행 서브쿼리 앞에는 일반 비교 연산자 사용
>,<, >=, <=, =, !=/^=,<>
-- 송은희 사원의 급여보다 많이 받는 직원의
-- 사번, 이름, 부서, 직급, 급여를 조회하세요
SELECT
E.EMP_ID
, E.EMP_NAME
, E.JOB_CODE
, E.SALARY
FROM EMPLOYEE E
WHERE E.SALARY > ( SELECT E2.SALARY
FROM EMPLOYEE E2
WHERE E2.EMP_NAME= '송은희')
ORDER BY SALARY DESC;
-- 가장 적은 급여를 받는 직원의
-- 사번, 이름, 직급, 부서, 급여, 입사일을 조회하세요.
SELECT
E.EMP_ID
, E.EMP_NAME
, E.JOB_CODE
, E.DEPT_CODE
, E.SALARY
, E.HIRE_DATE
FROM EMPLOYEE E
WHERE E.SALARY <= ( SELECT MIN(E2.SALARY)
FROM EMPLOYEE E2
);
-- 부서별 급여의 합계 중 가장 큰 부서의 부서명, 급여 합계를 구하세요 (HAVING절 사용)
SELECT
D.DEPT_TITLE
, SUM(E.SALARY)
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
GROUP BY D.DEPT_TITLE
HAVING SUM(E.SALARY) = ( SELECT MAX(SUM(E2.SALARY))
FROM EMPLOYEE E2
GROUP BY E2.DEPT_CODE
);
2. 다중행 서브쿼리
서브쿼리의 조회 결과 값의 행이 여러 개 일 때
다중행 서브쿼리 앞에서는 일반 비교 연산자 사용할 수 없다.
다중행 서브쿼리에서 사용할 수 있는 연산자는 다음과 같다.
1. IN , NOT IN
여러 개의 결과 값 중에서 한 개라도 일치하는 값이 있다면 혹은 없다면의 의미
-- 부서별 최고 급여를 받는 직원의 이름, 직급, 부서, 급여 조회
SELECT
E.EMP_NAME
, E.JOB_CODE
, E. DEPT_CODE
, E.SALARY
FROM EMPLOYEE E
WHERE E.SALARY IN ( SELECT MAX(E2.SALARY)
FROM EMPLOYEE E2
GROUP BY E2.DEPT_CODE
);
SELECT 절에서도 서브쿼리를 사용할 수 있다. (CASE문)
SELECT
E.EMP_ID 사번
, E.EMP_NAME 이름
, D.DEPT_TITLE 부서명
, J.JOB_NAME 직급명
, CASE
WHEN E.EMP_ID IN (SELECT
DISTINCT E2.MANAGER_ID
FROM EMPLOYEE E2
WHERE E2.MANAGER_ID IS NOT NULL )
THEN '관리자'
ELSE '직원'
END AS 구분
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE);
2. >ANY , <ANY
여러 개의 결과 값 중에서 한 개라도 큰 / 작은 경우
가장 작은 값보다 크냐? 가장 큰 값보다 작냐?
-- 대리 직급의 직원들 중에서 과장 직급의 최소 급여보다
-- 많이 받는 직원의 사번, 이름, 직급명, 급여를 조회하세요
SELECT
E.EMP_ID
, E.EMP_NAME
, J.JOB_NAME
, E.SALARY
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
WHERE J.JOB_NAME = '대리'
AND E.SALARY > ANY ( SELECT E2.SALARY
FROM EMPLOYEE E2
JOIN JOB J2 ON (E2.JOB_CODE = J2.JOB_CODE)
WHERE J2.JOB_NAME = '과장'
);
3. >ALL , <ALL
모든 값보다 큰 / 작은 경우
가장 큰 값보다 크냐? 가장 작은 값보다 작냐?
-- 차장 직급의 급여가 가장 큰 값보다 많이 받는 과장 직급의
-- 사번, 이름, 직급, 급여를 조회하세요
SELECT
E.EMP_ID 사번
, E.EMP_NAME 이름
, J.JOB_NAME 직급
, E.SALARY 급여
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
WHERE J.JOB_NAME = '과장'
AND E.SALARY > ALL ( SELECT E2.SALARY
FROM EMPLOYEE E2
JOIN JOB J2 ON (E2.JOB_CODE = J2.JOB_CODE)
WHERE J2.JOB_NAME = '차장'
);
4. EXIST , NOT EXIST
서브쿼리에만 사용하는 연산자로 값이 존재하냐? 존재하지 않냐?
3. 다중열 서브쿼리
- 서브쿼리의 조회 결과 컬럼의 개수가 여러 개 일 때
-- 퇴사한 여직원과 같은 부서, 같은 직급에 해당하는
-- 사원의 이름, 직급, 부서, 입사일을 조회
SELECT
E.EMP_NAME
, E.JOB_CODE
, E.DEPT_CODE
, E.HIRE_DATE
FROM EMPLOYEE E
WHERE (E.DEPT_CODE, E.JOB_CODE) = (SELECT E2.DEPT_CODE
, E2.JOB_CODE
FROM EMPLOYEE E2
WHERE SUBSTR(E2.EMP_NO,8,1) = 2
AND E2.ENT_YN = 'Y'
)
AND E.EMP_ID != (SELECT E3.EMP_ID
FROM EMPLOYEE E3
WHERE SUBSTR(E3.EMP_NO,8,1) = 2
AND E3.ENT_YN = 'Y'
); -- 본인은 제외하는 조건
4. 인라인 뷰 (INLINE VIEW)
FROM절에서 서브쿼리를 사용할 수 있다. => 테이블 대신 사용
서브쿼리가 만든 결과 집합 (RESULT SET)에 대한 출력화면
-- 자기 직급별 평균 급여를 받는 직원의 이름, 직급명, 급여를 조회
SELECT
E.EMP_NAME
, J.JOB_NAME
, E.SALARY
FROM (SELECT E2.JOB_CODE
, TRUNC( AVG(E2.SALARY), -5) AS JOBAVG
FROM EMPLOYEE E2
GROUP BY E2.JOB_CODE
) V
JOIN EMPLOYEE E ON(V.JOBAVG = E.SALARY AND E.JOB_CODE = V.JOB_CODE )
JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
ORDER BY J.JOB_NAME;
FROM절에서 자기 직급별 연봉 테이블이 별칭 V(인라인 뷰도 테이블 별칭을 사용할 수 있다)로 만들어짐.
이 인라인 뷰 테이블과 조인하여 JOBAVG와 급여가 같으면서 직급도 같은 직원을 구할 수 있다.
-- 인사관리부의 직원의 이름과 부서명, 직급이름 조회하세요.
SELECT
V.EMP_NAME
, V.부서명
, V.직급이름
FROM (SELECT EMP_NAME
, DEPT_TITLE 부서명
, JOB_NAME 직급이름
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
JOIN JOB J ON( E.JOB_CODE = J.JOB_CODE)
) V
WHERE V.부서명 = '인사관리부';
인라인 뷰 안에 기술된 컬럼에 대해서만 메인쿼리문에서 조회할 수 있다.
이 때 뷰 안에서 별칭을 사용하면 메인쿼리에서도 별칭으로 조회해야만 한다.
인라인뷰로 쿼리문을 작성해 보면서 느낀 점은,
인라인뷰로 테이블을 만들어 두고 별칭을 정해주었을 때
메인쿼리의 WHERE절에서 코드를 중복 작성하여 조건을 판별하지 않고
별칭으로 불러와 조건을 바로 판단할 수 있어서 편하다고 느꼈다.
위 코드 보다 복잡하게 테이블을 조인하고 함수를 통해 만들었을 때 SELECT절에 코드를 작성하고 별칭을 두어도
WHERE절이 먼저 실해되기 때문에 코드를 다시 가지고 와서 WHERE절에서 비교해야 하는 부분이 해결이 됨.
인라인 뷰를 활용한 TOP-N 분석
ORDER BY 한 결과에 ROWNUM을 붙임
1. ROWNUM은 행번호를 의미함
WHER절에서 붙여지므로 ORDER BY 한 다음에
ROWNUM이 붙게 하려면 서브쿼리(인라인뷰)를 사용해야함
-- 직원 정보에서 급여를 가장 많이 받는 순으로 이름, 급여, 순위 조회
-- 잘못된 예
SELECT
ROWNUM
, E.EMP_NAME
, E.SALARY
FROM EMPLOYEE E
ORDER BY E.SALARY DESC;
-- ORDER BY보다 SELECT가 먼저 실행되어 ROWNUM번호가 이미 부여된 상태
인라인 뷰 안에서 ORDER BY한 상태에서 행번호를 붙여주어야 제대로 된 순위가 나온다.
SELECT
ROWNUM
, V.EMP_NAME
, V.SALARY
FROM ( SELECT E.*
FROM EMPLOYEE E
ORDER BY E.SALARY DESC
) V
WHERE ROWNUM <= 5;
2. RANK() 1 2 2 4
동일한 순위 이후의 등수를 동일한 인원수만큼 건너 뛰고 다음 순위를 계산하는 방식
SELECT
E.EMP_NAME
, E.SALARY
, RANK() OVER(ORDER BY E.SALARY DESC) 순위
FROM EMPLOYEE E;
3. DENSE_RANK() 1 2 2 3
함수는 중복되는 순위 이후의 등수를 이후 등수로 처리
SELECT
E.EMP_NAME
, E.SALARY
, DENSE_RANK() OVER(ORDER BY E.SALARY DESC) 순위
FROM EMPLOYEE E ;
4-1.WITH
WITH 이름 AS (쿼리문)
서브쿼리에 이름을 붙여두고 사용시 이름을 사용하게 됨
인라인뷰로 사용될 서브쿼리에 이용됨
같은 서브쿼리가 여러번 사용될 경우 중복 작성을 줄일 수 있으며 실행속도도 빨라진다는 장점이 있다.
-- 부서별 급여 합계가 전체 급여의 총 합의 20%보다 많은 부서의 부서명과 부서별 급여 합계 조회
WITH
TOTAL_SAL
AS (SELECT D.DEPT_TITLE
, SUM(E.SALARY) SSAL
FROM EMPLOYEE E
JOIN DEPARTMENT D ON(E.DEPT_CODE = D.DEPT_ID)
GROUP BY D.DEPT_TITLE
)
SELECT DEPT_TITLE
, SSAL
FROM TOTAL_SAL
WHERE SSAL > (SELECT SUM(E2.SALARY) * 0.2
FROM EMPLOYEE E2
);
5. 상(호연)관 서브쿼리
일반적으로는 서브쿼리가 만든 결과 값을 메인쿼리가 비교 연산
상관 서브쿼리는 메인쿼리가 사용하는 테이블의 값을 서브쿼리가 이용해서 결과를 만든다.
메인쿼리의 테이블 값이 변경되면, 서브쿼리의 결과값도 바뀌게 됨
-- 관리자 사번이 EMPLOYEE 테이블에 존재하는 직원에 대한 조회
SELECT
E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, E.MANAGER_ID
FROM EMPLOYEE E
WHERE EXISTS (SELECT E2.EMP_ID
FROM EMPLOYEE E2
WHERE E.MANAGER_ID = E2.EMP_ID
);
메인쿼리의 E.MANAGER_ID 컬럼을 서브 쿼리에서 이용하여 E2.EMP_ID와 비교한다.
위 쿼리는 직원의 관리자 사번이 NULL이거나 관리자 사번이 직원의 사번과 다른 경우는 제외가 된다.
보통 서브쿼리문만 긁어서 조회해보면 테이블을 따로 확인해 볼 수 있는데
상관 서브쿼리는 서브쿼리문 안에서 메인쿼리의 값을 쓰기때문에 따로 조회할 수가 없다.
6. 스칼라 서브쿼리
단일행 서브쿼리 + 상관쿼리
-- 동일 직급의 급여 평균보다 급여를 많이 받고있는 직원의 사번, 직급코드, 급여를 조회하세요
SELECT
E.EMP_ID
, E.JOB_CODE
, E.SALARY
FROM EMPLOYEE E
WHERE E.SALARY > (SELECT TRUNC(AVG(E2.SALARY),-5)
FROM EMPLOYEE E2
WHERE E.JOB_CODE = E2.JOB_CODE
);
SELECT절에서 스칼라 서브쿼리 이용
-- 모든 사원의 사번, 이름, 관리자사번, 관리자명을 조회하세요
SELECT
E.EMP_ID 사번
, E.EMP_NAME 이름
, E.MANAGER_ID 관리자사번
, NVL((SELECT E2.EMP_NAME
FROM EMPLOYEE E2
WHERE E.MANAGER_ID = E2.EMP_ID
), '없음') 관리자명
FROM EMPLOYEE E
ORDER BY 1;
ORDER BY절에서 스칼라 서브쿼리이용
-- 모든 직원의 사번, 이름, 소속부서를 조회하세요 단, 부서명 내림차순 정렬
SELECT
E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
FROM EMPLOYEE E
ORDER BY ( SELECT D.DEPT_TITLE
FROM DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_ID
) DESC NULLS LAST;
'백엔드 과정 > Oracle(SQL)' 카테고리의 다른 글
[Oracle] Chap06 테이블 생성 및 제약조건 (0) | 2022.01.24 |
---|---|
[Oracle] SQL문제풀이 / SUBQUERY (0) | 2022.01.22 |
[Oracle] SQL문제풀이 / JOIN 연습문제 (1) | 2022.01.19 |
[Oracle] Chap04 JOIN 조인 (0) | 2022.01.19 |
[Oracle] Chap03 GROUP BY / HAVING (0) | 2022.01.19 |