[SQL]DML(SELECT문)(9) - 서브쿼리(Subquery)

서브쿼리(SUBQUERY)

   : 메인 SQL문을 위해 보조 역할을 하는 SELECT문으로, 하나의 주된 SQL(SELECT, CREATE, INSERT, UPDATE) 안에 포함된 또 하나의 SELECT문을 말한다.

 

 - 전체 직원의 평균 급여보다 많은 급여를 받는 직원들의 직번과 이름을 조회하기

  1) 전체 직원의 평균 급여를 조회하기

SELECT FLOOR(AVG(SALARY))
FROM EMPLOYEE;
-- 전체 직원의 평균 급여 = 3047662

 

  2) 전체 직원의 평균 급여보다 많은 급여를 받는 직원들의 직번과 이름을 조회하기

SELECT EMP_NO, EMP_NAME
FROM EMPLOYEE
WHERE SALARY > 3047662;

전체 직원의 평균 급여 수치를 직접적으로 대입을 한다.

 

  3) 1)과 2)를 합쳐서 조회하기

SELECT EMP_NO, EMP_NAME
FROM EMPLOYEE
WHERE SALARY > (SELECT FLOOR(AVG(SALARY))
                FROM EMPLOYEE);

 전체 직원들의 평균 급여보다 많은 급여를 받는 직원들의 직번과 이름을 조회하는 2)을 메인 SQL문으로 둔다. 그리고 전체 직원의 평균 급여를 구하는 1)을 보조 SELECT문으로 둔다. 왜냐하면 1)은 평균 급여를 담당하기 때문에,  1)을 2)의 WHERE절에 직접적인 평균 급여 수치를 대신하여 넣으면 평균 급여가 올라가거나 내려가도 정확한 값이 조회되기 때문이다. 이때 1) 구문은 해당 위치에 소괄호를 한 후 그 안에 작성해야 한다.

 

 

 

 

서브쿼리 구문

   : 서브쿼리를 수행한 결과값의 각 행과 열의 수에 따라 이와 같이 분류된다.

  - 단일 행(단일 열) 서브쿼리 : 결과값이 단 1개일 때(1칸의 컬럼값으로 나올 때)

  - 다중 행(단일 열) 서브쿼리 : 결과값이 여러 행일 때

  - (단일 행)다중 열 서브쿼리 : 결과값이 여러 열일 때

  - 다중 행 다중 열 서브쿼리 : 결과값이 여러 행과 여러 열일 때

   → 서브쿼리를 수행한 결과값의 각 행과 열의 수에 따라 연산자가 달라진다.

 

 (1) 단일 행(단일 열) 서브쿼리

   : 서브쿼리를 수행한 결과값이 단 1개일 때 일반 연산자(=, !=, >, <, >=, <= 등)를 사용할 수 있다.

 

 - 전 직원들의 평균 급여보다 급여를 적게 받는 사원들의 사원명, 직급코드, 급여를 조회하기

-- 1) 평균 급여
SELECT FLOOR(AVG(SALARY))
FROM EMPLOYEE;

-- 2) 평균 급여보다 급여를 적게 받는 사원들의 이름과 직급코드, 급여
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY < 3047662;

-- 3) 합쳐서 조회하기
SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY < (SELECT FLOOR(AVG(SALARY)) 
                FROM EMPLOYEE);  
                --결과값은 1행 1열 오직 1개의 값

 3)의 WHERE절에서 () 안에 있는 구문이 서브쿼리이다. 이는 평균 급여를 구하는 구문이므로 전체 직원들의 급여들을 합하여 직원 수 만큼 나눈 값, 딱 하나의 값만 나온다.

 

 

 

 (2) 다중 행(단일 열) 서브쿼리

   : 서브쿼리를 수행한 결과값이 여러 행일 경우 'IN()'이나 '>/< ANY()', '>/< ALL'을 사용한다.

 

  - IN(값1, 값2, ...) 서브쿼리 : 여러 개의 결과값 중에 어느 하나라도 일치하는 것이 있을 경우에 사용한다.

    NOT IN(값1, 값2, ...) 서브쿼리 : 여러 개의 결과값 중에 어느 하나라도 일치하는 것이 없을 경우에 사용한다.

 

  - > ANY(값1, 값2, ...) 서브쿼리 : 여러 개의 결과값 중에 어느 하나보다 클 경우에 사용한다.

    < ANY(값1, 값2, ...) 서브쿼리 : 여러 개의 결과값 중에 어느 하나보다 작을 경우에 사용한다.

 

  - > ALL : 여러 결과값 중에서 가장 큰 값보다 클 경우에 사용한다.

    < ALL : 여러 결과값 중에서 가장 작은 값보다 작을 경우에 사용한다.

 

 - 직급이 대리인 사원들 중에서 과장보다 급여를 많이 받는 사원들의 사번과 이름, 직급명, 급여를 조회하기

-- 1) 과장들의 급여를 조회하기
SELECT SALARY
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE
AND J.JOB_NAME = '과장'; 
-- 과장들의 급여 : 2200000 / 2500000 / 3760000 (총 세 개의 값)

-- 2) 대리들의 급여 중에서 과장의 급여 결과값 중 어느 하나보다 큰 경우를 조회하기
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE
AND SALARY >= 2200000
AND JOB_NAME = '대리';

-- 합쳐서 조회하기
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE
AND SALARY > ANY (SELECT SALARY
                  FROM EMPLOYEE E, JOB J
                  WHERE E.JOB_CODE = J.JOB_CODE
                  AND J.JOB_NAME = '과장')
AND JOB_NAME = '대리';

 대리의 급여가 과장들의 급여 중 어느 하나보다 큰 경우를 조회하는 문제이다. 1)에서 과장들의 급여를 조회했을 때 총 세 개의 값이 나왔으므로 3행 1열이다. 이땐 대리의 급여(SALARY)가 서브쿼리보다 커야 하므로 '> ANY() 서브쿼리'를 사용한다. 

 

 

 

 (3) (단일 행)다중 열 서브쿼리

   : 서브쿼리를 수행한 결과값이 하나의 행이지만 컬럼의 개수가 여러 개인 경우이다.

 

 - 최충분 사원과 부서코드랑 직급코드가 같은 사원들의 이름과 부서코드, 직급코드, 입사일을 조회하기

-- 1) 최충분 사원의 부서코드와 직급코드를 조회하기
SELECT DEPT_CODE, JOB_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '최충분';
-- 부서코드는 D5, 직급코드는 J5

-- 2) 부서코드가 D5이면서 직급코드가 J5인 사원들의 이름과 부서코드, 직급코드, 입사일을 조회하기
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' AND JOB_CODE = 'J5';

-- 3) 합치기
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 = '최충분');

  * 다중 열 서브쿼리(비교할 값의 순서를 맞추기)

   : (비교대상 컬럼1, 비교대상 컬럼2) = (비교할 값1, 비교할 값2)

  - 서브쿼리 형식으로만 제시해야 한다.

 

 

 

 (4) 다중 행 다중 열 서브쿼리

   : 서브쿼리를 수행한 결과값이 여러 행 여러 컬럼일 경우이다.

 

 - 각 직급별 최소 급여를 받는 사원들의 사번과 이름, 직급코드, 급여를 조회하기

-- 1) 각 직급별 최소 급여 조회하기
SELECT JOB_CODE, MIN(SALARY)
FROM EMPLOYEE
GROUP BY JOB_CODE;

-- 2) 각 직급별 최소 급여를 받는 사원들의 사번과 이름, 직급코드, 급여 조회하기
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) = ('J2', 3700000)  -- (비교할대상컬럼1, 2) = (비교할값1, 2)
OR (JOB_CODE, SALARY) = ('J7', 1380000)
OR (JOB_CODE, SALARY) = ('J3', 3400000);

-- 3) 합치기
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) = (SELECT JOB_CODE, MIN(SALARY)
                            FROM EMPLOYEE
                            GROUP BY JOB_CODE);

 최소값을 구하는 함수 MIN()로 최소 급여를 조회하면 되고, 각 직급별로 조회해야 하므로 GROUP BY로 JOB_CODE(직급코드)로 묶는다.

 

 

 

 (5) 인라인 뷰(INLINE VIEW)

   : FROM절에 서브쿼리를 제시한다. 즉, FROM절에서 테이블 대신 서브쿼리를 수행한 결과(RESULT SET)를 사용한다.

 

 - 보너스를 포함해 연봉이 3천만 원 이상인 사원들의 이름과 보너스를 포함한 연봉을 조회하기

-- 기존 구문
SELECT EMP_NAME, ((SALARY + (SALARY * NVL(BONUS, 0))) * 12) 연봉
FROM EMPLOYEE
WHERE ((SALARY + (SALARY * NVL(BONUS, 0))) * 12) >= 30000000;

-- 인라인 뷰
-- 보너스 포함 연봉이 3천만 원 이상인 사원들의 이름 조회하기
SELECT EMP_NAME
FROM (SELECT EMP_NAME, ((SALARY + (SALARY * NVL(BONUS, 0))) * 12) 연봉
      FROM EMPLOYEE
      WHERE ((SALARY + (SALARY * NVL(BONUS, 0))) * 12) >= 30000000)
WHERE DEPT_CODE IS NOT NULL;

 부서 배치가 된 사람인 경우에 조회한다. 인라인 뷰는 FROM절에서 서브쿼리를 제시하는데, 테이블 대신에 그 자리에 서브쿼리를 넣는다. 

 

 

  - 인라인 뷰를 주로 사용하는 TOP-N 분석

   : 데이터베이스 상에 있는 자료들 중 최상위 n개의 자료를 조회하기 위해 사용한다.

     이때, 조회된 순서대로 순번을 부여해주는 ROWNUM 컬럼을 사용한다.

 

 - 전 직원 중 급여가 가장 높은 상위 5명의 순위와 이름, 급여를 조회하기

SELECT ROWNUM, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE ROWNUM <= 5
ORDER BY SALARY DESC;
-- 순서가 섞여서 출력될 수 있다. 왜냐하면 조회되는 순서대로 순번을 부여하기 때문이다.
-- 제대로 순서를 부여해주려면 ORDER BY로 정렬된 테이블로 조회해야 한다.

SELECT ROWNUM, EMP_NAME, SALARY
FROM (SELECT *
      FROM EMPLOYEE
      ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;

 인라인 뷰를 통해 ORDER BY로 SALARY(급여)를 내림차순으로 정렬한 테이블로 순번을 부여해야 한다. 인라인 뷰를 하지 않고 그냥 EMPLOYEE 테이블을 사용하면 조회되는 순서대로 순번이 부여된다. 즉, 순서가 섞여서 출력될 수 있다는 뜻이다. 그리고 조건식에서 순번을 부여해주는 ROWNUM으로 뽑아낼 데이터 수 범위를 작성하면 된다.

 

 

 

 (6) 순위를 매기는 함수(WINDOW FUNCTION)

   - RANK() OVER(ORDER BY 정렬 기준 컬럼명 ASC/DESC)

     : 공동 n위가 m명일 때 그 다음 순위는 (n+m)위로 반환한다.

   - DENSE_RANK() OVER(ORDER BY 정렬 기준 컬럼명 ASC/DESC)

     : 공동 n위가 m명일 때 그 다음 순위는 (n+1)위로 반환한다.

   - 순위 함수는 SELECT절에서만 사용할 수 있다.

   - RANK() 함수는 WHERE절에서 사용할 수 없다.

 

 - RANK() OVER()로 사원들의 급여가 높은 순대로 사원명과 급여, 순위를 조회하기

SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) 순위
FROM EMPLOYEE;

공동 19위가 2명일 때 그 다음 순위는 (19+2)위, 즉 21위로 반환한다.

 

 

 

 

 

 - DENSE_RANK() OVER()로 사원들의 급여가 높은 순대로 사원명과 급여, 순위를 조회하기

SELECT EMP_NAME, SALARY, DENSE_RANK() OVER(ORDER BY SALARY DESC) 순위
FROM EMPLOYEE;

공동 19위가 2명일 때 그 다음 순위는 (19+1)위, 즉 20위로 반환한다.

 

 

 

 

 

 - 사원들의 급여 중 상위 4명의 이름과 연봉, 순위를 조회하기

SELECT *
FROM (SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) 순위
      FROM EMPLOYEE)
WHERE 순위 <= 4;

인라인 뷰를 사용하여 상위 N명을 구할 수 있다. 상위 N명일 경우를 구할 땐 조건식을 사용해야 하는데, 순위를 매기는 함수(RANK() / DENSE_RANK())는 WHERE절에서 사용하면 에러가 나서 안된다. 그럴 땐 인라인 뷰를 통해 먼저 순서를 정리한 테이블을 만든 후 그 테이블로 상위 N명을 조회해야 한다.