[Oracle] 오라클 #15, 서브쿼리에 대해서(예제 및 응용)

서브쿼리, Sub Query

- 하위쿼리, 부분쿼리

- SQL 안에 또 다른 SQL이 들어있는 형태

- 서브쿼리를 사용할 수 있는 위치(어디든지 데이터가 필요한 곳이면 다 사용 가능)

    a. where절 : 서브쿼리의 결과셋을 조건으로 사용한다.

    b. 컬럼리스트 : 서브쿼리의 결과셋을 하나의 컬럼값으로 사용한다.

    c. from절 : 서브쿼리의 결과셋을 하나의 테이블로 이용. (=뷰 , 인라인뷰

    d. order by절

    e. group by절

목적 : tblhousekeeping 테이블에서 가장 비싼 아이템을 가져오려고한다.

select item from tblhousekeeping where max(price) = price; 이게 안됨

서브쿼리사용

서브쿼리가 먼저 질의한 후에 반환되는 결과값을 원래 쿼리에 사용하고자 할때...

select item from tblhousekeeping where price = (select max(price) from tblhousekeeping); 

>>> 여기있는 서브쿼리에서 price의 최대값이 반환되면 그것을 각각의 price와 비교한 후 price값이 max 값인 컬럼과 같은 값일때 반환하는 원리

select avg(basicpay) from tblinsa; --평균급여 : 155만원

--기본급이 155만원(평균)보다 큰 사람들

select * from tblinsa where basicpay > (select avg(basicpay) from tblinsa); 서브쿼리 사용

 

--영업부 직원중 가장 적은 급여를 받는 사람보다 더 많은 급여를 받는 직원이 전체 직원중 몇명입니까??

select count(*) from tblinsa where basicpay >= (select min(basicpay) from tblinsa where buseo='영업부');

--영업부에서 가장 날짜가 적은사람(고참인사람 - 유닉스시간으로 계산하나봄.) 보다 더 많이 받는 사람은??

select count(*) from tblinsa where basicpay >= (select min(basicpay) from tblinsa where ibsadate = (select min(ibsadate) from tblinsa where buseo='영업부'));

>>> 입사날짜가 가장 적은(최고참 - 유닉스시간으로 치면 가장 최근이 제일 크게나옴.) 사람의 입사년도를 가져오고 그 입사년도인 사람의 급여중 가장 작은 것을 가져오는 형태이다. 그렇게 반환받은 basicpay값으로 처리하면 된다.

hr 계정(hr 계정 로그인해서 예제 확인해보시기 바랍니다.)

select * from employees;

select * from departments;

select * from locations;

직원중 steven king이 소속된 부서(departments)가 어느지역에 있는지 주소를 알려주세요.

조건1 select department_id from employees where first_name = 'Steven' and last_name = 'King';

>> 이름이 steve king인 사람의 부서 id 를 알아야함.

조건2 select location_id from departments where department_id =90;  

>> steve king이라는 사람이 소속된 부서의 위치 id 값을 알아와야함.

최종SQL 

select * from locations where location_id=(select location_id from departments

    where department_id =(select department_id from employees

        where first_name = 'Steven' and last_name = 'King'));

>>> 두가지 조건을 조합하여 만든 sql쿼리

컬럼리스트에 서브쿼리 사용하기

--모든 직원들 중 '홍길동'보다 얼마를 더 받거나 덜 받는지 궁금

select basicpay from tblinsa where name ='홍길동';  --2610000

select name ,basicpay,basicpay-2610000 as "차액" from tblinsa where name <>'홍길동';

select 

name ,basicpay, basicpay-(select basicpay from tblinsa where name ='홍길동') as "차액"

,(select basicpay from tblinsa where name ='홍길동') 

from tblinsa where name <>'홍길동';

>>> 컬럼리스트에서 사용한 서브쿼리는 단하나의 레코드만 나오도록 해야함. 이 쿼리에서 name 컬럼은 PK값이 아니므로 올바르지 못한 쿼리이기는 함.

집계함수

1. where절에서 사용불가> where 절에서 서브쿼리를 사용하면 가능

2. 컬럼리스트에 일반컬럼과 집계함수를 동시에 못쓴다. > 컬럼리스트에 서브쿼리를 쓰면 가능.

select round(avg(basicpay)) as "평균급여" from tblinsa; --155만원

select name,basicpay from tblinsa;

select 

name,basicpay

,basicpay-(select round(avg(basicpay))from tblinsa) as"평균급여" 

,basicpay-(select round(max(basicpay))from tblinsa) as"최대급여" 

,basicpay-(select round(min(basicpay))from tblinsa) as"최소급여"

from tblinsa;

from절에서 서브쿼리를 사용하기

select * from tblname where gender = 'f';

select * from (select * from tblname where gender = 'f');--인라인뷰

select name,직위,city,이름 from (select name,substr(name,2,2) as "이름",jikwi as"직위", city from tblinsa where buseo = '영업부');

>>> select from 테이블, 에서 구해서 나온 값을 일종의 테이블로 보고 결과셋(나온 뷰)에서 또한번 select로 값을 얻어오는 방식이다.

where절에서의 서브쿼리

--서브쿼리의 결과가 단일행인 경우 -> 비교 연산자 사용.

--서브쿼리의 결과가 다중행인 경우 -> in을 사용한다. (열거형)

--서브쿼리의 결과가 단일컬럼일경우 : 값으로 취급

--서브쿼리의 결과가 다중컬럼일경우

--> =연산자와 ()를 사용해서 다대다 배교를 만들어서 사용한다(순서와 개수가 다르면 안된다***)

--홍길동과 이순애가 속한 부서의 직원명단을 가져오시오.

--ORA-01427: single-row subquery returns more than one row

select * from tblinsa 

where buseo = (select buseo from tblinsa where name = '홍길동' or name = '이순애');

select * from tblinsa 

where buseo in (select buseo from tblinsa where name = '홍길동' or name = '이순애');

select * from tblinsa 

where buseo in (select buseo,city from tblinsa where name = '홍길동' or name = '이순애');

select * from tblinsa where name = '엄용수';

--직원중 이름 나윤균, 나윤균과 같은 부서에 소속되어 있으며 같은 직위를 가지는 직원들은 누구누구있나...?

select * from tblinsa 

where buseo = (select buseo from tblinsa where name = '엄용수') 

and 

jikwi = (select jikwi from tblinsa where name = '엄용수');

--다중컬럼일때

select * from tblinsa 

where (buseo,jikwi,to_char(ibsadate,'yyyy')) = (select buseo,jikwi,to_char(ibsadate,'yyyy') from tblinsa where name = '엄용수');

 

컬럼리스트에서의 서브쿼리

-- : 서브쿼리가 무조건 단일값 반환해야 한다. 나머지는 사용 불가능하다.

--1. 단일컬럼 + 단일행 -> 얘만 가능.

select first,last,(select avg(weight) from tblname) as"평균몸무게" from tblname;

--2. 단일컬럼 + 다중행 : 100% 불가능

select first,last,(select height from tblname where height>170) from tblname;

--3. 다중컬럼 + 단일행 : 불가능

select first,last,(select height,weight from tblname where first='재석') from tblname;

from절에서의 서브쿼리

--1~4 번까지 다 사용 가능

--where절(값의역할), 컬럼리스트(단일컬럼 값의 역할), from(테이블의 역할)

--1. 단일컬럼 + 단일행

--2. 단일컬럼 + 다중행

--3. 다중컬럼 + 단일행

--4. 다중컬럼 + 다중행