[Oracle] SubQuery & rownum (Day12)

위 글은 해당 카테고리의 수업 강의 자료를 정리한 것입니다. 

 

 

 

1. SubQuery: 하나의 SQL 질의문 속에 다른 SQL 질의문이 포함되어 있는 상태

 

예시: Den보다 급여를 많은 사람의 이름과 급여는?

 

• Den의 급여

SELECT e.SALARY
FROM EMPLOYEES e 
WHERE e.FIRST_NAME ='Den'

결과가 11000

 

• 급여(11000)보다 많이 받는 사람의 이름과 급여

SELECT e.FIRST_NAME, e.SALARY 
FROM EMPLOYEES e 
WHERE e.SALARY > 11000

 

이렇게 풀기 보다는 서브쿼리를 사용하여 한번에 해결할 수 있음

SELECT e.EMPLOYEE_ID, e.FIRST_NAME, e.SALARY  
FROM EMPLOYEES e 
WHERE e.SALARY > (SELECT e2.SALARY 
                  FROM EMPLOYEES e2 
                  WHERE e2.FIRST_NAME= 'Den');

 

 

• 서브쿼리 작성시 주의사항
  • SubQuery 부분은 WHERE 절의 연산자 오른쪽에 위치해야 하며 괄호로 묶어야 함
  • 가급적 ORDER BY를 하지 않음
  • 단일행 SubQuery와 다중행 SubQuery에 따라 연산자를 잘 선택해야 함
• 단일행 SubQuery
  • SubQuery의 결과가 한 row인 경우
  • 연산자: =, >, >=, <, <=, <>(같지 않다)

예제1: 급여를 가장 적게 받는 사람의 이름, 급여, 사원번호

select first_name, salary, employee_id
from employees
where salary = (select min(salary)
                from employees);

 

예제2: 평균 급여보다 적게 받는 사람의 이름, 급여

select first_name, salary, employee_id
from employees
where salary = (select min(salary)
                from employees);

 

 

• 다중행 SubQuery
  • SubQuery의 결과가 여러 row인 경우
  • 연산자: ANY, ALL, IN, ...

 

예시 ⭕️

select first_name, salary
from employees
where salary IN (select salary
                 from employees
                 where department_id = 110);

 

예시 ❌

select first_name, salary
from employees
where salary > (select salary
                from employees
                where department_id = 110);

 

 

 

• 다중행 SubQuery: IN 연산자

select first_name, salary
from employees
where salary IN (select salary
                 from employees
                 where department_id = 110);

 

• 다중행 SubQuery: ANY 연산자 (or)

select first_name, salary
from employees
where salary > ANY (select salary
                    from employees
                    where department_id = 110);

 

 

 

 

• 다중행 SubQuery: ALL 연산자 (and)

select first_name, salary
from employees
where salary > ALL (select salary
                    from employees
                    where department_id = 110);

 

 

 

예제: 각 부서별로 최고 급여를 받는 사원 (조건절에서 비교)

SELECT e.DEPARTMENT_ID, e.EMPLOYEE_ID, e.FIRST_NAME, e.SALARY  
FROM EMPLOYEES e 
WHERE (e.DEPARTMENT_ID, e.SALARY) IN (SELECT e2.DEPARTMENT_ID, MAX(e2.SALARY) 
                                      FROM EMPLOYEES e2 
                                      GROUP BY DEPARTMENT_ID);

 

 

예제: 각 부서별로 최고 급여를 받는 사원 (테이블에서 조인)

select e.department_id, e.employee_id, e.first_name, e.salary
from employees e, (SELECT DEPARTMENT_ID, max(SALARY) SALARY 
				   FROM EMPLOYEES  
				   GROUP BY DEPARTMENT_ID) s
where e.department_id = s.department_id and e.salary = s.salary;

서브쿼리도 임시 테이블이 만들어지기 때문에 하나의 테이블로 봄... 그래서 as도 가능하고 조인도 가능함

 

 

 

 

2. rownum: 질의의 결과에 가상으로 부여되는 Oracle의 가상(Pseudo)의 Column (일렬번호)

 

예제: 급여를 가장 많이 받는 3명의 직원의 이름 출력

select rownum, first_name, salary
from employees;

 

 

 

이미 rownum이 부여되었는데 ORDER BY 할 경우 결과 값이 뒤섞임

select rownum, first_name, salary
from employees
order by salary desc;

 

 

이와 같은 현상은 서브 쿼리로 해결해야 함

 

select rownum, first_name, salary
from (select first_name, salary
      from employees
      order by salary desc);

 

rownum 같은 경우 자를 수 있음

 

 

 

 

그러나 rownum을 생성한 뒤에 where 절을 들어가기 때문에 이와 같은 코드는 불가능함

select rownum, first_name, salary
from ( select first_name, salary
       from employees
       order by salary desc)
where rownum >= 3;

 

따라서 이 같은 경우 rownum 값을 생성한 후에 where 절을 실행해야 함

select rn, first_name, salary
from ( select rownum rn, first_name, salary
       from ( select first_name, salary
              from employees
              order by salary desc)
      )
where rn >= 11 and rn <= 20;

 

 

 

예제: 2007년에 입사한 사람들의 (급여순) 3위부터 7위까지의 이름과 급여 출력

 

• 입사년도를 글자로 변환

SELECT e.HIRE_DATE 
FROM EMPLOYEES e 
WHERE TO_CHAR(e.HIRE_DATE, 'YYYY') = '2007';

• 2007년에 입사한 사람들의 급여를 내림차순하여 rownum 부여

SELECT rownum rn,
       first_name , 
       salary , 
       hire_date 
FROM (SELECT e.first_name , e.salary , e.hire_date 
      FROM EMPLOYEES e 
      WHERE to_char(e.HIRE_DATE , 'YYYY') = '2007'
      ORDER BY e.SALARY desc);

• 2007년에 입사한 사람들의 급여 3위부터 7위까지

SELECT rn, 
       first_name , 
       salary , 
       hire_date 
FROM ( SELECT rownum rn,
       first_name , 
       salary , 
       hire_date 
       FROM (SELECT e.first_name , e.salary , e.hire_date 
             FROM EMPLOYEES e 
             WHERE to_char(e.HIRE_DATE , 'YYYY') = '2007'
             ORDER BY e.SALARY desc)
     )
WHERE rn >= 3 AND   rn <= 7;