DataBase/Oracle
SQL - SUBQUERY
jddng
2022. 1. 21. 11:22
728x90
반응형
SUBQUERY
- 하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문장
- 서브쿼리는 메인쿼리가 실행되기 이전에 한번만 실행된다.
- 비교연산자의 오른쪽에 기술해야하며, 반드시 괄호로 묶어야 한다.
- SELECT, FROM, WHERE, HAVING, ORDER BY 절에서 사용할 수 있다.
단일행 서브쿼리
- 서브쿼리의 조회 결과 값이 1개의 행일 때
- 보통 단일행 서브쿼리 앞에는 일반 비교 연산자를 사용한다.
- >, <, >=, <=, =, !=
-- 단일행 코드
-- 사원명이 노옹철인 사람의 부서 조회
SELECT
DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철';
-- 부서코드가 D9인 직원을 조회
SELECT
EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';
-- 위의 두 쿼리를 하나로
SELECT
EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT
DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철'
);
다중행 서브쿼리
- 서브쿼리의 조회 결과 값이 여러개의 행일 때
- 서브쿼리 앞에서는 일반 비교 연산자를 사용할 수 없다.
- 다중행 서브쿼리에 사용되는 연산자
IN : 여러 개의 결과 값 중에서 메인 쿼리와 한 개라도 일치하는 값이 존재하는지 판별
ANY : 여러 개의 결과 값 중에서 메인 쿼리가 한 개라도 큰 값이 존재하는지 판별
ALL : 여러 개의 결과 값이 메인 쿼리가 모두 큰 값인지를 판별
EXIST : 서브쿼레만 사용하는 연산자로 값이 존재하는지 판별
IN 연산자 사용
-- 부서별 최고 급여를 받는 직원의 이름, 직급, 부서, 급여 조회
SELECT
E.DEPT_CODE
, MAX(E.SALARY)
FROM EMPLOYEE E
GROUP BY E.DEPT_CODE;
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
);
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 = '과장';
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 = '대리';
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 = '과장'
);
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 = '차장'
);
EXISTS 사용
-- 관리자 사번이 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
);
다중행, 다중열 서브쿼리
- 서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개 일 때
SELECT
E.EMP_ID
, E.EMP_NAME
, E.JOB_CODE
, E.SALARY
FROM EMPLOYEE E
WHERE (E.JOB_CODE, E.SALARY) IN (SELECT
E2.JOB_CODE
, TRUNC(AVG(E2.SALARY), -5)
FROM EMPLOYEE E2
GROUP BY E2.JOB_CODE
);
다중열 서브쿼리
- 서브쿼리의 조회 결과 컬럼의 개수가 여러 개 일 때
-- 퇴사한 여직원과 같은 부서, 같은 직급에 해당하는
-- 사원의 이름, 직급, 부서, 입사일을 조회
SELECT
E.EMP_ID
, E.EMP_NAME
, E.DEPT_CODE
, E.JOB_CODE
FROM EMPLOYEE E
WHERE SUBSTR(E.EMP_NO, 8, 1) = 2
AND E.ENT_YN = 'Y';
-- 다중열을 이용하지 않고 쿼리 작성
SELECT
E.EMP_ID
, E.JOB_CODE
, E.DEPT_CODE
, E.HIRE_DATE
FROM EMPLOYEE E
WHERE E.DEPT_CODE = (SELECT E2.DEPT_CODE
FROM EMPLOYEE E2
WHERE SUBSTR(E2.EMP_NO, 8, 1) = 2
AND E2.ENT_YN = 'Y'
)
AND E.JOB_CODE = (SELECT E3.JOB_CODE
FROM EMPLOYEE E3
WHERE SUBSTR(E3.EMP_NO, 8, 1) = 2
AND E3.ENT_YN = 'Y'
)
AND E.EMP_ID != (SELECT E4.EMP_ID
FROM EMPLOYEE E4
WHERE SUBSTR(E4.EMP_NO, 8, 1) = 2
AND E4.ENT_YN = 'Y'
);
-- 다중열 서브쿼리로 변경
SELECT
E.EMP_ID
, 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'
);
인라인 뷰(INLINE VIEW)
- FROM 절에서 서브쿼리를 사용(테이블 대신 사용)
- 서브쿼리가 만든 결과 집합에 대한 출력
- 인라인뷰를 이용한 TOP-N 분석이 가능하다.(ROWNUM의 이용)
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;
SELECT
V.EMP_NAME
, V.부서명
, V.직급이름
FROM (SELECT EMP_NAME
, DEPT_TITLE AS 부서명
, JOB_NAME AS 직급이름
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
) V
WHERE V.부서명 = '인사관리부';
인라인뷰를 이용한 ROWNUM의 이용
-- 행 번호를 ROWNUM을 이용하여 붙임
-- 행 번호가 붙여진후 ORDER BY 정렬이 이루어 지므로 순서대로 출력이 안됨
SELECT
ROWNUM
, E.EMP_NAME
, E.SALARY
FROM EMPLOYEE E
ORDER BY E.SALARY DESC;
-- 행 번호 1~5 출력
-- 인라인뷰를 이용하여 정렬을 한 후 행 번호를 붙임
SELECT
ROWNUM
, V.EMP_NAME
, V.SALARY
FROM (SELECT E.*
FROM EMPLOYEE E
ORDER BY E.SALARY DESC
) V
WHERE ROWNUM <= 5;
-- 6~10까지 조회(잘못 된 예)
SELECT
ROWNUM
, V.EMP_NAME
, V.SALARY
FROM (SELECT E.*
FROM EMPLOYEE E
ORDER BY E.SALARY DESC
) V
WHERE ROWNUM BETWEEN 6 AND 10;
-- 6위부터 10위까지 조회
SELECT
V2.RNUM
, V2.EMP_NAME
, V2.SALARY
FROM (SELECT
ROWNUM RNUM
, V.EMP_NAME
, V.SALARY
FROM (SELECT E.*
FROM EMPLOYEE E
ORDER BY E.SALARY DESC
) V
) V2
WHERE RNUM BETWEEN 6 AND 10;
728x90
반응형