데이터베이스 서브쿼리

 

 

★ 한줄 조언 : 서브쿼리에서 = 사용하지 말고, in으로 사용하자 (다중행일 경우 오류생기기에 사전에 방지)

 

서브쿼리(Subquery)
: subquery의 결과를 이용해서 mainquery(주쿼리)를 수행

select * from sawon

-- 장동건과 동일한 부서 직원 조회
1. 장동건부서 구한다
   select deptno
   from sawon
   where saname = '장동건'
2. 장동건부서 조회
   select * from sawon
   where deptno = 40

   select * from sawon
   where deptno in (select deptno from sawon where saname = '장동건') ;

-- 안재욱과 직급이 동일한 직원 조회
1. 안재욱직급 구한다
   select sajob
   from sawon
   where saname = '안재욱' -- 여기서 부장이 출력된다.

   select * from sawon
   where sajob in (select sajob from sawon where saname = '안재욱')
   -- sajob이 부장인 애들만 출력한다.

 

 

-- 사원테이블에서 최대급여자
   select * from sawon
   where sapay = (select max(sapay) from sawon)

-- 사원테이블에서 최초입사자 조회
   select * from sawon
   where to_char(sahire,'YYYYMMDD') = (select to_char(min(sahire),'YYYYMMDD') from sawon)
-- 이렇게 되면, 날짜가 가장 오래된 사람에 대한 전체 레이블을 출력한다.

-- 사원테이블 급여평균보다 많이 받는 직원 조회
   select * from sawon
   where sapay > (select avg(sapay) from sawon)

 

 

 

 

 

- 문제를 풀어봅시다.

Q1. 고객테이블에서 강철과 동일한 지역의 사는 고객을 조회해보세요.
select * from gogek
where goaddr in (select goaddr from gogek where goname = '강철')

Q2. 고객테이블에서 류민과 동일한 지역(시도)에 사는 고객 조회
select * from gogek
where substr(goaddr,1,2) in (select substr(goaddr,1,2) from gogek where goname = '류민')

Q3. 사원테이블에서 장동건과 입사년도가 동일한 직원 조회
select * from sawon
where to_char(sahire,'YYYY') in (select to_char(sahire,'YYYY') from sawon where saname = '장동건')

Q4. 사원테이블에서 안재욱보다 먼저 입사한 직원 조회
select * from sawon
where to_char(sahire,'YYYYMMDD') < (select to_char(sahire,'YYYYMMDD') from sawon where saname = '안재욱')

Q5. 사원테이블에서 이미자와 동일한 월에 입사한 직원 조회
select * from sawon
where to_char(sahire,'MM') in (select to_char(sahire,'MM') from sawon where saname = '이미자')

-- 10번 부서에서 최대급여자 조회
select * from sawon
where sapay = (select max(sapay) from sawon where deptno = 10) and deptno = 10

-- 10,20 부서의 최대급여자
방법1)
select * from sawon
where sapay = (select max(sapay) from sawon where deptno = 10) and deptno = 10
   or sapay = (select max(sapay) from sawon where deptno = 20) and deptno = 20
방법2)
select * from sawon
where sapay = (select max(sapay) from sawon where deptno = 10) and deptno = 10
union -- 위 sql문과 아래 sql문을 합칩니다. (만약 똑같은 데이터가 있으면 중복 데이터가 하나만 출력된다)
select * from sawon
where sapay = (select max(sapay) from sawon where deptno = 10) and deptno = 10

 

- 사실상 아래코드도 부서가 새로 추가 되면, deptno가 10~40 범위를 초과하게 된다.

- 내가 휴가중이라면, 이 코드는 일일이 추가 하기전에 이 범위를 받지 못한다.

- 그리고 부서 개편으로 인한 번호 수정 및 부서가 100개 추가 등과 같은 상황이 발생시에 일일이 수정해야한다.

-- 각 부서별 최대급여자 조회
select * from sawon
where sapay = (select max(sapay) from sawon where deptno = 10) and deptno = 10
   or sapay = (select max(sapay) from sawon where deptno = 20) and deptno = 20
   or sapay = (select max(sapay) from sawon where deptno = 30) and deptno = 30
   or sapay = (select max(sapay) from sawon where deptno = 40) and deptno = 40
order by deptno -- 오름차순

 

- 위의 코드를 개편시키기 위해서, 아래와 같이 상관쿼리를 사용한다. (얘는 한 번 실행하면 주쿼리를 실행한다)

- 서브쿼리는 단방향으로 진행한다. 서브쿼리(하위쿼리)에서 얻어온 값을 주쿼리에서 실행시킨다.

- 상관쿼리는 서브쿼리(하위쿼리)와 서로 주고받는다.(상관 관계), 주쿼리의 데이터를 가지고 서브쿼리에서 수행한다.

 

주쿼리정보 -> 서브쿼리에 전달 -> 결과 얻어내고 -> 주쿼리 수행

-- 상관쿼리
: 주쿼리정보 -> 서브쿼리에 전달 -> 결과 얻어내고 -> 주쿼리 수행
select * from sawon s1 -- sawon 뒤에 s를 선언하면 앞으로는 s1라는 테이블명만 사용해야 한다.
where sapay = (select max(sapay) from sawon where deptno = s1.deptno)
order by deptno

 

- 가급적이면 사용하지 말라고 하는데, 저 위에 상황에서는 대처할 수 있는 상관쿼리 밖에 없기 때문에, 사용해야 한다.

 

 

 

Multiple-Row Subquery

 

 

장동건이 동일 인물이 데이터에 추가되어버렸다.

Q3. 사원테이블에서 장동건과 입사년도가 동일한 직원 조회
select * from sawon
where to_char(sahire,'YYYY') = (select to_char(sahire,'YYYY') from sawon where saname = '장동건')

-- new data
insert into sawon values(21,'장동건','남자',20,'과장','2005-1-1',2,3800);

 

- 이런식으로 코드 사용하면 오류가 발생한다.

 

Q3. 사원테이블에서 장동건과 입사년도가 동일한 직원 조회
select * from sawon
where to_char(sahire,'YYYY') in (select to_char(sahire,'YYYY') from sawon where saname = '장동건')

 

in을 사용해주면 두명의 동명이인인 장동건이 포함이 된다.

(서브쿼리에서 단일형인지 다중행인지 가늠이 안잡히면 =을 사용하지말고 in으로 잡아라. 그러면 오류는 발생하지 않는다.)

 

-- all 모두 만족해야한다.(sahire<1988 && sahire<2024)
select * from sawon
where to_char(sahire,'YYYYMMDD') < ALL (select to_char(sahire,'YYYYMMDD') from sawon where saname = '안재욱')	
-- any : 또는 or 이기 때문에, 동명이인의 해당 정보 두개 다 포함한다.
select * from sawon
where to_char(sahire,'YYYYMMDD') < ANY (select to_char(sahire,'YYYYMMDD') from sawon where saname = '안재욱')

 

 

- distinct는 중복 되는 데이터값이 있으면 그 중복 데이터를 하나만 출력한다.

-- 서브쿼리이용시 다중행 여부 체크후에 작성해라
select distinct deptno from sawon -- 중복 데이터 제거

 

 

 

Multiple-Column Subquery

 

- 서브쿼리의 조건을 가져와서 두개의 값에 적합한 것을 가져온다.

 

 

 

Scalar Subquery

-- Scalar Subquery
select 
   sabun, saname, deptno, sapay,
   (select round(avg(sapay),0) from sawon) as total_avg,
   (select round(avg(sapay),0) from sawon where deptno = s.deptno) as dept_avg
from sawon s
order by deptno

 

 

 

UNION

 

- union (중복데이터 출력안됨) sawon deptno 21개, deptno 5개

-- Union / Union All
select deptno from sawon
union
select deptno from dept

select deptno from sawon
union all
select deptno from dept

 

 

Intersect

-- intersect : 공통데이터만 출력(결합)
select deptno from sawon
intersect
select deptno from dept

 

 

- 서로에게 공통되어 있는 데이터만 중복이 되지 않게, 출력이 된다.

 

 

Minus

-- minus : 차집합 (
select deptno from dept
minus
select deptno from sawon

 

 

Q1. 사원테이블에서 최불암과 같은 계절에 입사한 직원 조회
1) 입사계절 포함한 뷰 생성

create or replace view sawon_view
as 
select
s.*,
floor(months_between(sysdate,sahire)/12) as 근무년수,
TRUNC(mod(months_between(sysdate,sahire),12),0) as 근무월수,
round(sapay/12,0) as 월급,
round((months_between(sysdate,sahire)*(sapay/12))/12,0) as 퇴직금,
case
   when to_char(sahire,'MM') in (3,4,5) then '봄'
   when to_char(sahire,'MM') in (6,7,8) then '여름'
   when to_char(sahire,'MM') in (9,10,11) then '가을'
   else '겨울'
end   
as 입사월
from (select * from sawon) s

2) 
select * from sawon_q1_view
where 입사월 = (select 입사월 from sawon_q1_view where saname = '최불암')

Q2. 이미지와 같은연대에 입사한 직원 조회
    1988 -> 1980년대
: substr(sahire,'YYYY') -> '1998'
  substr(to_char(sahire,'YYYY'),1,3) -> '199'
  select * from sawon
  where substr(to_char(sahire,'YYYY'),1,3) in (select substr(to_char(sahire,'YYYY'),1,3) from sawon where saname = '이미자')

 

Q3. 김용만보다 퇴직금을 많이 받는 직원 조회
select * from sawon_view
where 퇴직금 > (select 퇴직금 from sawon_view where saname = '김용만')

Q4. 고객테이블에서 류민보다 나이가 많은 고객 조회

select * from gogek_view2
where substr(to_char(birth_year),3,2) < (select substr(to_char(birth_year),3,2) from gogek_view2 where goname = '마징가')

create or replace view gogek_view2
    as
select gobun, goname, goaddr, godam,
    concat(substr(gojumin,1,8),'******') as gojumin,
    (
    to_number(substr(gojumin,1,2)) + 
    case
       when to_number(substr(gojumin,8,1)) in (1,2,5,6) then 1900
       when to_number(substr(gojumin,8,1)) in (3,4,7,8) then 2000
       else 1800
    end
    ) as birth_year,
    to_number(substr(gojumin,9,2)) as local
from gogek

Q5. 고객테이블에서 영희와 띠가 같은 고객 조회
select * from gogek_view3
where 띠 in (select 띠 from gogek_view3 where goname = '영희')