[Oracle] 오라클 서브쿼리(Sub Query)

1. 서브쿼리란?
2. 서브쿼리 활용
3. ANY, ALL
4. 상관서브쿼리(서브상관쿼리)

---

서브쿼리란?

- SELECT 문 속에 또다른 SELECT 문이 포함되어져 있을 때 포함되어진 SELECT문을 Sub Query(서브쿼리) 라고 부른다.

 

- Inline View도 서브쿼리의 일종이다. From 절에 서브쿼리가 쓰인다면 Inline View 라고 부른다.

 

-   select ....
      from ....   ==> Main Query(메인쿼리 == 외부쿼리)
      where ... in(select ...
                   from ....) ==> Sub Query (서브쿼리 == 내부쿼리)

 

Sub Query를 이용한 예제코드,

-- 문제를 풀기위한 테이블 생성과 데이터 넣기

create table tbl_authorbook
   (bookname       varchar2(100)
   ,authorname     varchar2(20)
   ,loyalty        number(5)
   );
   
   
   insert into tbl_authorbook(bookname, authorname, loyalty)
   values('자바프로그래밍','이순신',1000);
   
   insert into tbl_authorbook(bookname, authorname, loyalty)
   values('로빈슨크루소','한석규',800);
   
   insert into tbl_authorbook(bookname, authorname, loyalty)
   values('로빈슨크루소','이순신',500);
   
   insert into tbl_authorbook(bookname, authorname, loyalty)
   values('조선왕조실록','엄정화',2500);
   
   insert into tbl_authorbook(bookname, authorname, loyalty)
   values('그리스로마신화','유관순',1200);
   
   insert into tbl_authorbook(bookname, authorname, loyalty)
   values('그리스로마신화','이혜리',1300);
   
   insert into tbl_authorbook(bookname, authorname, loyalty)
   values('그리스로마신화','서강준',1700);

   insert into tbl_authorbook(bookname, authorname, loyalty)
   values('어린왕자','김유신',1800);
   
   commit;
   
   
   select * 
   from tbl_authorbook;
   
   
   
 --- [문제]아래와 같이 tbl_authorbook 테이블에서 공저(도서명은 동일하지만 작가명이 다른 도서)로 지어진 도서정보를 나타내세요... ---
   /*
       ---------------------------------
         도서명         작가명    로얄티
       ---------------------------------  
         로빈슨크루소    한석규	 800
         로빈슨크루소    이순신	 500
         그리스로마신화  유관순	1200
         그리스로마신화  이혜리	1300
         그리스로마신화  서강준	1700
       ---------------------------------  
   */
   
   from tbl_authorbook
   where bookname in( 공저로 지어진 도서명 ); 
   
   --공저로 지어진 도서명 ==> 도서명은 동일하지만 작가명이 다른 도서명
   
   select bookname, count(*)
   from tbl_authorbook
   group by bookname
   having count(*) > 1;
   /*
      로빈슨크루소	2
      그리스로마신화	3
   */
   
   select bookname
   from tbl_authorbook
   group by bookname
   having count(*) > 1;
   /*
      로빈슨크루소
      그리스로마신화
   */
   -- having 절에 count(*) > 1 조건을 주면,
   -- 도서명으로 묶여진 그룹에, 작가가 한명이상인 수를 보여준다.
   
   select * 
   from tbl_authorbook
   where bookname in( select bookname
                      from tbl_authorbook
                      group by bookname
                      having count(*) > 1 );
   -- 위의 select 문을 where 절의 서브쿼리로 활용하였다.

 

Sub Query에서 사용되어지는 ANY, ALL

- Sub Query(서브쿼리)에서 사용되어지는 ANY 는 OR 와 흡사하고,
- Sub Query(서브쿼리)에서 사용되어지는 ALL 은 AND 와 흡사하다.

 

ANY 예제코드

 select department_id AS 부서번호
       , employee_id AS 사원번호
       , first_name || ' ' || last_name AS 사원명
       , salary AS 기본급여
  from employees
  where NVL(department_id, -9999) != 30 AND
        salary =ANY (select distinct salary
                     from employees
                     where department_id = 60)
  order by 1, 4;

- Sub Query 절에서 사용하는 ALL 은 사용시 주의를 요한다.

- SELECT 결과물에서 NULL은 존재하지 않도록 만들어야 한다.

 

잘못된 코드 예시

select employee_id AS 사원번호
        , first_name || ' ' || last_name AS 사원명
        , salary * commission_pct AS 수당
from employees
where salary * commission_pct >= ALL (select salary * commission_pct from employees);

 

 

올바른 코드 예시

select employee_id AS 사원번호
    , first_name || ' ' || last_name AS 사원명
    , salary * commission_pct AS 수당
from employees
where salary * commission_pct >= ALL (select salary * commission_pct 
                                     from employees
                                     where salary * commission_pct is not null);

- 이처럼 서브쿼리의 WHERE 절에 IS NOT NULL 조건을 주면서, 결과물이 아무것도 나오지 않는 일을 방지해야한다.

 

 

상관서브쿼리

- 상관서브쿼리(서브상관쿼리) 이라함은 Main Query(외부쿼리)에서 사용된 테이블(뷰)에 
  존재하는 컬럼이 Sub Query(내부쿼리)의 조건절(where 절, having 절)에 사용되어질때를
  상관서브쿼리(서브상관쿼리) 라고 부른다.

 

-- employees 테이블에서 기본급여에 대해 전체등수 및 부서내등수를 구하세요.
 
 select E.department_id AS 부서번호
       , E.employee_id AS 사원번호
       , E.salary AS 기본급여
       
       , (select count(*) + 1  
          from employees
          where salary > E.salary ) AS 전체등수
          
       , (select count(*) + 1  
          from employees
          where department_id = E.department_id and
                salary > E.salary ) AS 부서내등수 
  from employees E
  order by 1, 3 desc;

서브쿼리를 통하여 테이블 생성하기

- CREATE TABLE (테이블이름)

AS

(서브쿼리문)

 

- 서브쿼리문의 SELECT 되어진 결과물들로 새로운 테이블을 생성한 것이다.

 

서브쿼리로 테이블 생성 예제코드

create table tbl_employees_3060
as 
select department_id
    , employee_id
    , first_name || ' ' || last_name AS ENAME
    , nvl(salary + (salary * commission_pct), salary) AS MONTHSAL
    , case when substr(jubun, 7, 1) in('1','3') then '남' else '여' end AS GENDER
    , jubun
from employees
where department_id in (30, 60);
-- Table TBL_EMPLOYEES_3060이(가) 생성되었습니다.