💁♀️ 서브쿼리(Subquery)란,
하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문장
- 서브쿼리는 메인쿼리가 실행되기 이전에 한번만 실행
- 비교연산자의 오른쪽에 기술해야 하며, 반드시 괄호로 묶어야함
- 서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT한 항목의 개수와
자료형을 일치시켜야함
👀 서브쿼리의 사용
👉 부서 코드가 노옹철 사원과 같은 소속의 직원 명단 조회
[1] 사원명이 노옹철인 사람의 부서 조회
SELECT
DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철'; >>> 서브 쿼리[2] 부서코드가 D9인 직원 조회
SELECT
EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9'; >>> 메인 쿼리[3] 위의 두 쿼리를 하나로 작성
SELECT
EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT
DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철'
);
>>> 메인쿼리에 소괄호를 넣고 그 안에 서브쿼리를 넣음[4] 전 직원의 평균 급여보다 많은 급여를 받고 있는 직원의 사번, 이름, 직급코드, 급여 조회
SELECT
EMP_ID
, EMP_NAME
, JOB_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT
AVG(SALARY)
FROM EMPLOYEE
); >>> '전 직원의 평균 급여'를 구하는 서브 쿼리👀 서브쿼리의 유형
서브쿼리의 유형에 따라 서브쿼리 앞에 붙는 연산자가 다름
📍 서브쿼리의 유형
1) 단일행
2) 다중행
3) 다중열
4) 다중행 다중열👉 단일행
단일행 서브쿼리는 앞에 일반 비교 연산자를 사용
> < >= <= = != ^= <>
[1] 노옹철 사원의 급여보다 많이 받는 직원의 사번, 이름, 부서, 직급, 급여 조회
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, JOB_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT
SALARY
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철'
);[2] 가장 적은 급여를 받는 직원의 사번, 이름, 부서, 직급, 급여 조회
SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, JOB_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY = (SELECT
MIN(SALARY)
FROM EMPLOYEE
);[3] 부서별 급여의 합계 중 합계가 가장 큰 부서의 부서명, 급여 합계를 조회 (HAVING절에서 사용)
SELECT
DEPT_TITLE
, SUM(SALARY)
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID) >>> LEFT JOIN : EMPLOYEE 기준으로 조인
>>> => EMPLOYEE테이블의 DEPT_CODE 컬럼이 없는 사원도 조회하기 위해
GROUP BY DEPT_TITLE -- : 부서명을 그룹화해서
HAVING SUM(SALARY) = (SELECT
MAX(SUM(SALARY)) -- : 급여의 합계가 가장 큰
FROM EMPLOYEE
GROUP BY DEPT_CODE -- : 부서별 급여의 합계를 구하기 위해 부서코드를 그룹화
);
>>> '그룹 함수의 값'이 조건이 된다면 HAVING절에서 서브쿼리 작성
>>> '부서별 급여의 합계'를 구하기 위한 서브 쿼리👉 다중행
다중행 서브쿼리 앞에는 일반 비교 연산자 사용 불가
IN NOT IN
> ANY < ANY : 여러 개의 결과 값 중에서 한 개라도 큰 or 작은 경우
> ALL < ALL : 모든 값보다 큰 or 작은 경우
EXIST NOT EXIST : 값이 존재하는가? or 존재하지않는가? (서브쿼리에만 사용하는 연산자)
[1] 부서별 최고 급여를 받는 직원의 이름, 직급, 부서, 급여 조회
SELECT
EMP_NAME
, JOB_CODE
, DEPT_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY IN (SELECT
MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE
); >>> 부서별 가장 큰 급여의 금액을 표시하는 서브쿼리[2] 대리 직급의 직원들 중에서 과장 직급의 최소 급여보다 많이 받는 직원의 사번, 이름, 직급명, 급여를 조회
SELECT
EMP_NAME
, JOB_NAME
, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '과장'; >>> 서브쿼리 미리 작성SELECT
EMP_ID
, EMP_NAME
, JOB_NAME
, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '대리'
AND SALARY > ANY (SELECT >>> ANY는 최소값보다 커야함
SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '과장'
);
>>> ANY : 서브쿼리의 값 중 하나라도 SALARY보다 작으면 OK[3] 차장 직급의 급여의 가장 큰 값보다 많이 받는 과장 직급의 사번, 이름, 직급명, 급여를 조회
SELECT
EMP_ID
, EMP_NAME
, JOB_NAME
, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '과장'
AND SALARY > ALL (SELECT >>> ALL은 최대값보다 커야함
SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '차장'
);
>>> ALL: 서브쿼리의 값 모두가 SALARY보다 작아야함👉 다중열
[1] 퇴직한 여직원과 같은 부서, 같은 직급에 해당하는 사원의 이름, 직급, 부서, 입사일 조회
SELECT
EMP_NAME
, JOB_CODE
, DEPT_CODE
, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT >>> '같은 부서 조회' 서브쿼리
DEPT_CODE
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2'
AND ENT_YN = 'Y'
)
AND JOB_CODE = (SELECT >>> '같은 직급 조회' 서브쿼리
JOB_CODE
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2'
AND ENT_YN = 'Y' -- 퇴직함
) ;
>>> 두 개의 서브쿼리가 중복됨 (비효율적)[2] 위의 중복되는 서브쿼리를 다중열 서브쿼리로 정리
SELECT
EMP_NAME
, JOB_CODE
, DEPT_CODE
, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE) = (SELECT >>> WHERE절에 두 개의 컬럼을 묶어 한 번에 조회
DEPT_CODE
, JOB_CODE
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2'
AND ENT_YN = 'Y'
);👀 인라인 뷰(INLINE VIEW)
💁♀️ 인라인 뷰(INLINE VIEW)란,
FROM절에서 서브쿼리를 사용하는 것을 의미하며, 서브 쿼리의 결과(RESULT SET)를 테이블 대신 사용 가능
👉 인라인 뷰의 사용
◼ 인라인뷰로 직급별 평균 급여를 계산한 테이블을 만들고 EMPLOYEE와 JOIN시, 평균 급여가 본인의 급여와 동일하면 JOIN을 줘서 직급별 평균 급여에 맞는 급여를 받고 있는 직원을 조회하는 구문
SELECT
E.EMP_NAME
, J.JOB_NAME
, E.SALARY
FROM (SELECT
JOB_CODE
, TRUNC(AVG(SALARY), -5) AS JOBAVG
FROM EMPLOYEE
GROUP BY JOB_CODE) V >>> V : 테이블의 별칭 (이 RESULT SET도 하나의 테이블처럼 사용 가능)
JOIN EMPLOYEE E ON (V.JOBAVG = E.SALARY AND V.JOB_CODE = E.JOB_CODE)
>>> 직급별 평균 급여에 맞는 급여를 받고 있는 직원을 조회
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
>>> JOB_NAME이 JOB 테이블에 있기 때문에 JOB JOIN
ORDER BY J.JOB_NAME;👉 인라인 뷰 사용 시, 유의할 점 🔥중요🔥
인라인뷰의 결과만이 남아 있으므로 서브쿼리에서 조회에 사용하지 않은 컬럼은 조회할 수 없으며, 별칭을 사용했다면 해당 별칭으로 조회해야함
SELECT
EMP_NAME
, 부서명
, 직급명
FROM (SELECT
EMP_NAME
, DEPT_TITLE 부서명
, JOB_NAME 직급명
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE)
)
WHERE 부서명 = '인사관리부';
>>> DEPT_TITLE이 아닌 부서명을 입력해야함 FROM절에 있는 테이블에 없기 때문에 (DEPT_TITLE가 있다고 착각 X)👉 인라인 뷰를 사용한 TOP-N 분석
[1] TOP-N 조회
SELECT
ROWNUM >>> ORDER BY한 결과에 ROWNUM을 붙이기 (ROWNUM은 행 번호를 의미)
, EMP_NAME
, SALARY
FROM EMPLOYEE >>> 여기서 부여 받은 정렬 순서는 의미 없는 순서
>>> 현재는 WHERE절에서 ROWNUM이 결정되어 급여를 많이 받는 순서와 관계 없는 번호를 가짐
ORDER BY SALARY DESC;
>>> SALARY 기준 내림차순 정렬[2] 인라인 뷰를 활용하여 위의 쿼리 수정
💡 원하는 순서의 ROWNUM이 붙게 하려면 인라인뷰를 활용해야함
SELECT
ROWNUM
, V.EMP_NAME
, V.SALARY
FROM (SELECT E.* -- E.* : EMPLOYEE의 모든 정보
FROM EMPLOYEE E
ORDER BY E.SALARY DESC >>> 여기서 정렬하고 정렬된 순서에 WHERE절에서 ROWNUM 부여
) V -- 테이블의 별칭
WHERE ROWNUM <= 5;[3] 6위에서 10위까지 조회
😈 Warning
1위부터가 아닌 6위부터 10위까지 조회할 경우,
WHERE절에서 ROWNUM은 1로 시작하기 때문에 단순히 1-5위를 구할 때 처럼 'BETWEEN 6 AND 10'을 입력하면
해당 값이 FALSE가 되어 패스. 그리고나서 다음 행을 확인할 때 다시 1로 확인하고 결국 모든 행이 6~10
사이라는 조건을 만족할 수 없어 '결과가 0행 출력됨'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; >>> 행 출력 실패[4] 인라인 뷰에 인라인 뷰를 넣어 위의 쿼리 수정
SELECT
V2.RNUM
, V2.EMP_NAME
, V2.SALARY
FROM (SELECT
ROWNUM RNUM >>> ROWNUM으로 더이상 작동하지않는 RNUM(별칭)
>>> RNUM은 컬럼 (그저 값이 저장되는 용도)
, V.EMP_NAME
, V.SALARY
FROM (SELECT E.*
FROM EMPLOYEE E
ORDER BY E.SALARY DESC >>> SALARY가 정렬된 테이블
) V
) V2
WHERE RNUM BETWEEN 6 AND 10;[5] STOPKEY 사용
쿼리를 더 효율적으로 동작하게끔 범위를 지정하는 조건을 달아줌
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
/*조건추가*/ WHERE ROWNUM < 11 >>> 이 조건이 없을 경우, 23행을 모두 조회
>>> (좀 더 효율적, 우리가 조회하는 건 6에서 10까지이므로)
) V2
WHERE RNUM BETWEEN 6 AND 10;[6] 급여 평균(10만원 단위에서 절삭) 3위 안에 드는 부서의 부서코드, 부서명, 평균 급여 조회
SELECT
V.DEPT_CODE
, V.DEPT_TITLE
, V.평균급여 >>> 함수명은 안됨. 별칭 적어야함
FROM (SELECT
E.DEPT_CODE
, D.DEPT_TITLE
, AVG(E.SALARY) 평균급여
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
GROUP BY E.DEPT_CODE, D.DEPT_TITLE
>>> GROUP BY절에 나열해야 SELECT 컬럼에 쓸 수 있음
ORDER BY AVG(E.SALARY) DESC
) V
WHERE ROWNUM <= 3;👀 RANK() OVER & DENSE_RANK() OVER
💁♀️ RANK() OVER란,
동일한 순위 이후의 등수를 동일한 인원수만큼 건너 뛰고 다음 순위 계산
💁♀️ DENSE_RANK() OVER란,
중복되는 순위 이후의 등수를 이후 등수로 처리
👉 RANK() OVER와 DENSE_RANK() OVER 결과의 차이
◼ RANK() OVER
SELECT
EMP_NAME
, SALARY
, RANK() OVER(ORDER BY SALARY DESC) 순위 -- RANK() OVER(ORDER BY 어떤 순서로 정렬할 것인지)
FROM EMPLOYEE;
>>> 19등이 2명인 상황, 20등은 생략되고 다음 순위가 21등으로 출력◼ DENSE_RANK() OVER
SELECT
EMP_NAME
, SALARY
, DENSE_RANK() OVER(ORDER BY SALARY DESC) 순위
FROM EMPLOYEE;
>>> 19등이 2명인 상황이어도 다음 순위가 20등으로 출력👉 RANK() OVER의 활용
◼ SALARY 기준 순위 5위까지 조회
SELECT
V.*
FROM(SELECT
EMP_NAME
, SALARY
, RANK() OVER(ORDER BY SALARY DESC) 순위
FROM EMPLOYEE) V
WHERE V.순위 <= 5;◼ 보너스를 포함한 연봉 순위 5위까지의 사번, 이름, 부서명, 직급명, 입사일 조회
SELECT
V.EMP_ID
, V.EMP_NAME
, V.DEPT_TITLE
, V.JOB_NAME
, V.HIRE_DATE
, V.연봉
FROM(SELECT
E.EMP_ID
, E.EMP_NAME
, D.DEPT_TITLE
, J.JOB_NAME
, E.HIRE_DATE
, (E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12 연봉
, RANK() OVER(ORDER BY (E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12 DESC) 순위
>>> OVER안에 함수식 사용 가능
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.순위 < 6;👀 WITH 별칭 AS (쿼리문)
💁♀️ WITH 별칭 AS (쿼리문)이란,
서브쿼리에 이름을 붙여주어 다시 사용할 경우, 붙여준 이름을 이용하여 재사용 가능
- 인라인 뷰로 사용 될 서브쿼리에서 이용
- 같은 서브쿼리가 여러번 사용될 경우 중복 작성 불필요
- 실행 속도도 빨라짐
WITH
TOPN_SAL >>> 이 서브쿼리를 여러번 사용해야한다면 이름을 지어주고 테이블로 계속 사용 가능
AS (SELECT
E.EMP_ID
, E.EMP_NAME
, E.SALARY
FROM EMPLOYEE E
ORDER BY E.SALARY DESC
)
SELECT
RONUM
, T.EMP_NAME
, T.SALARY
FROM TOPN_SAL T; >>> 위의 테이블을 사용👀 상관(상호연관) 서브쿼리
💁♀️ 상관(상호연관) 서브쿼리란,
메인 쿼리가 사용하는 테이블의 값을 서브쿼리가 이용해서 결과 생성
- 메인 쿼리 테이블의 값이 변경되면, 서브쿼리의 결과 값도 변경
👉 상관 서브쿼리의 활용
◼ 관리자 사번이 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.MANEGER_ID = E2.EMP_ID
>>> 이 조건에 메인쿼리의 E.MANEGER_ID를 포함하고 있기 때문에
>>> 서브쿼리의 단독실행 불가
);👀 스칼라 서브쿼리
💁♀️ 스칼라 서브쿼리란,
단일행 서브쿼리와 상관 서브쿼리를 합친 개념의 서브쿼리
👉 스칼라 서브쿼리의 활용
◼ 동일 직급의 급여 평균보다 급여를 많이 받고 있는 직원의 직원명, 직급코드, 급여 조회
SELECT
EMP_NAME
, JOB_CODE
, SALARY
FROM EMPLOYEE E >>> FALSE면 이곳에 담기지 않고 TRUE인 값만 담김
WHERE SALARY > (SELECT
TRUNC(AVG(E2.SALARY), -5) >>> 구한 동일 직급의 그 사람들의 샐러리 값 평균 계산
FROM EMPLOYEE E2
WHERE E.JOB_CODE = E2.JOB_CODE >>> : 동일 직급
);◼ 모든 사원의 사번, 이름, 관리자 사번, 관리자명을 조회
SELECT절에서 서브쿼리 사용시 결과 값은 반드시 1행으로 나와야 함
(스칼라 서브쿼리만 사용 가능)
SELECT
EMP_ID
, EMP_NAME
, MANAGER_ID
, NVL((SELECT EMP_NAME
FROM EMPLOYEE E2
WHERE E.MANAGER_ID = E2.EMP_ID
), '없음') 관리자명
FROM EMPLOYEE E
ORDER BY 1;
Tiny little habits make me