서브쿼리(SUBQUERY)
개념
쿼리 안에 또다른 쿼리(SELECT)가 있는 것입니다. 메인 SQL문을 위해 보조 역할을 하는 쿼리를 의미합니다.
간단한 서브 쿼리 예시를 살펴보겠습니다.
예시 1
노옹철 사원과 같은 부서에 속한 사원들을 조회하는 쿼리를 작성
위 예시는 두 가지로 구성되어 있는 것을 알 수 있습니다.
- 노옹철 사원의 부서 코드를 조회하기 - D1이라 가정
- 부서코드가 D1인 사원들 조회
각각을 쿼리로 작성하면 다음과 같습니다.
- 노옹철 사원의 부서 코드를 조회하기
SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철';- 부서코드가 D1인 사원들 조회
SELECT EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = 'D1';이 둘을 합치려면 서브쿼리를 사용해야됩니다. 이는 아래와 같습니다.
SELECT EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철');즉 'D1' 이 1번 쿼리로 대치된 것을 알 수 있습니다. 예시 하나를 더 살펴보도록 하겠습니다.
예시 2
전 직원의 평균급여보다 더 많은 급여를 받는 사원들의 사번, 이름, 직급코드, 급여 조회
- 전직원의 평균급여 - 3000000이라 가정
SELECT ROUND(AVG(SALARY))
FROM EMPLOYEE;- 3000000보다 더 많은 급여를 받는 사원들의 사번, 이름, 직급코드, 급여 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= 3000000;서브쿼리를 통한 통합
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY >= (SELECT AVG(SALARY)
FROM EMPLOYEE);서브쿼리의 구분
서브쿼리를 수행한 결과값이 몇행 몇열로 나오냐에 따라서 분류합니다.
크게 정리하면 아래와 같습니다.
단일행 서브쿼리 : 서브쿼리의 조회 결과값이 갯수가 오로지 1개일 때
다중행 서브쿼리 : 서브쿼리의 조회 결과값이 여러행일 때(여러행 한열)
다중열 서브쿼리 : 서브쿼리의 조회 결과값이 한 행이지만 컬럼이 여러개일 때
다중행 다중열 서브쿼리 : 서브쿼리의 조회 결과값이 여러행 여러열일 때
이 때, 서브쿼리의 종류가 뭐냐에 따라서 서브쿼리 앞에 붙는 연산자 달라집니다.
단일행 서브쿼리
- 서브쿼리의 조회 결과값이 갯수가 오로지 1개일 때(1행 1열)
- 일반 비교연산자(
= != > <= ...) 사용 가능
예를 들어 노옹철 사원의 급여보다 많이받는 사원들의 사번, 이름, 부서코드, 급여조회를 할 경우 아래와 같습니다.
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT SALARY
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철');이 때 서브쿼리의 결과 노옹철 사원의 급여에 해당하는 단 1행 1열의 결과를 사용하기 때문에 이를 단일행 서브쿼리라 합니다.
다중행 서브쿼리
- 서브쿼리의 조회 결과값이 여러 행일 때(여러행 한열)
- 아래와 같은 연산자를 활용합니다.
'IN (서브쿼리)' : 여러개의 결과값 중에서 한개라도 일치하는 값이 있다면 조회
'> ANY (서브쿼리)' : 여러개의 결과값 중에서 한개라도 클 경우
'< ANY (서브쿼리)' : 여러개의 결과값 중에서 한개라도 작을 경우 조회
비교대상 > ANY (서브쿼리의 결과값 => 값1, 값2, 값3...)
비교대상 > 값1 OR 비교대상 > 값2 OR 비교대상 > 값3
'> ALL (서브쿼리)' : 여러개의 모든 결과값들 보다 클 경우 조회
'< ALL (서브쿼리)' : 여러개의 모든 결과값들 보다 작을 경우
비교대상 > ALL (서브쿼리의 결과값 => 값1, 값2, 값3...)
비교대상 > 값1 AND 비교대상 > 값2 AND 비교대상 > 값3...- 유재식 또는 윤은해 사원과 같은 직급인 사원들의 사번, 사원명, 직급코드, 급여 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE JOB_CODE IN (SELECT JOB_CODE
FROM EMPLOYEE
WHERE EMP_NAME IN ('윤은해', '유재식'));서브쿼리의 결과 행은 2개(유재식, 윤은해의 JOB_CODE) 열은 1개(JOB_CODE 열) 이므로 다중행 서브쿼리입니다.
- 대리 직급임에도 과장 직급 급여들 중 최소 급여보다 많이 받는 사원들 조회(사번, 이름, 직급, 급여)
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
WHERE JOB_NAME = '대리'
AND SALARY > ANY (SELECT SALARY
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE)
WHERE JOB_NAME = '과장');다중열 서브쿼리
- 서브쿼리의 조회 결과값이 한 행이지만 컬럼이 여러개일 때
- 여러 열일 정우 조건문을 아래와 같이 작성이 가능합니다.
WHERE (열1, 열2, ...) = (조건)- 하이유 사원과 같은 부서코드, 같은 직급코드에 해당하는 사원들 조회
(사원명, 부서코드, 직급코드, 입사일)
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE) = (SELECT DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '하이유');다중행 다중열 서브쿼리
- 서브쿼리의 조회 결과값이 여러행 여러열일 때, 이 때까지 쓴거 다 합쳐봅니다.
- 각 부서별 최고급여를 받는 사원들의 사번, 사원명, 부서코드, 급여
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE (DEPT_CODE, SALARY) IN (SELECT DEPT_CODE, MAX(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE);인라인 뷰
- FROM절에 서브쿼리를 작성한 것
- 서브쿼리를 수행한 결과를 마치 테이블처럼 사용
- 실제 물리적으로 DB에 저장되지 않으므로 '테이블(Table)'이 아닌 '뷰(View)'라 합니다.
- 가장 최근에 입사한 사원 5명 조회(사원명, 급여, 입사일)
SELECT EMP_NAME, SALARY, HIRE_DATE
FROM (SELECT EMP_NAME, SALARY, HIRE_DATE
FROM EMPLOYEE
ORDER BY HIRE_DATE DESC)
WHERE ROWNUM <= 5;순위를 매기는 방법
ROWNUM: 조회된 순서대로 1부터 순번을 부여해주는 컬럼입니다.
RANK() OVER(정렬기준): 공동 순위가 있으면 건너뛰고 순위 계산합니다.
DENSE_RANK() OVER(정렬기준): 공동 순위 상관없이 순위 계산합니다.
(중요)서브쿼리 내에서 함수를 사용할 경우 메인쿼리에서는 이를 컬럼명으로 제대로 인식하지 못합니다. 반드시 별칭을 지정하도록 합니다.
- 오류 발생
SELECT *
FROM (SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC)
FROM EMPLOYEE)
WHERE RANK() OVER(ORDER BY SALARY DESC) <= 5; -- 오류 발생- 별칭 지정
SELECT *
FROM (SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) AS "순위"
FROM EMPLOYEE)
WHERE 순위 <= 5;