서브쿼리(SubQuery)
서브쿼리(SubQuery)
- 하위 쿼리, 부분 쿼리
- SQL 안에 또 다른 SQL이 들어있는 형태
- 서브쿼리를 사용할 수 있는 위치(어디든지 데이터가 필요한 곳이면 다 사용이 가능하다.)
a. where 절 : 서브쿼리의 결과셋을 조건값으로 사용한다.
b. 컬럼리스트 : 서브쿼리의 결과셋을 하나의 컬럼값으로 사용한다.
c. from 절 : 서브쿼리의 결과셋을 하나의 테이블로 사용 > 인라인 뷰(Inline View)
d. order by 절
c. group by 절
ex)단가가 가장 높은 물건의 이름?
-- 1.가장 높은 단가? 얼마?
select max(price) from tblhousekeeping;
--2. 모든 항목 중 단가가 15000원인 물건?
select item from tblhousekeeping where price = 15000;
--ORA-00934: group function is not allowed here
--집계함수는 where 절에서 사용이 불가능하다.(***)
select item from tblhousekeeping where price = max(price);
- 서브쿼리가 먼저 질의한 후에 반환되는 결과값을 원래 쿼리에 사용하고자 할때
select item from tblhousekeeping where price = (select max(price) from tblhousekeeping);
select item from tblhousekeeping where price = (select min(price) from tblhousekeeping);
select item from tblhousekeeping where price > (select avg(price) from tblhousekeeping);
select item from tblhousekeeping where price < (select avg(price) from tblhousekeeping);
-컬럼리스트에서 서브쿼리 사용하기
--모든 직원들이 '홍길동'보다 얼마를 더 받거나 덜받는지 궁금?
select basicpay from tblinsa where name ='홍길동'; --2871000
select name,basicpay, basicpay -(select basicpay from tblinsa where name ='홍길동') as "차액"
,(select basicpay from tblinsa where name ='홍길동') as "홍길동 급여"
from tblinsa
where name <> '홍길동';
-집계 함수
--컬럼리스트에 일반 컬럼과 집계 함수를 동시 사용 불가 > 컬럼리스트에 서브쿼리를 사용하면 가능하다.
select round(avg(basicpay)) as "평균 급여" from tblinsa;
select name,basicpay from tblinsa;
select name,basicpay
, (select round(avg(basicpay)) from tblinsa) as "평균 급여"
, (select round(max(basicpay)) from tblinsa) as "최대 급여"
, (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,jikwi,city from (select name,jikwi as "직위",city from tblinsa where buseo ='영업부'); --임시 영업부 테이블
select name,직위,city,firstname from (select name,substr(name,2,2) as firstname,jikwi as "직위",city from tblinsa where buseo ='영업부'); --임시 영업부 테이블
1.서브쿼리의 결과가 단일행인 경우
- 비교 연산자를 사용한다.(단일값)
2.서브쿼리의 결과가다중행인 경우
- in을 사용한다.(다중값 & 열거값)
3.서브쿼리의 결과가 단일 컬럼인 경우
- 값으로 취급
4.서브쿼리의 결과가 다중 컬럼인 경우
- = 연산자와 ()를 사용해서 다대다 비교를 만들어서 사용한다.(순서와 갯수가 다르면 안된다.****)
- 컬럼 리스트에 서브쿼리를 사용하는 경우에만..
- 서브쿼리가 무조건 단일값 반환해야 한다.(1번만 가능) 나머지는 사용 불가능하다.
-------------------------------------------------------------------------------------------------------------------
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,wieght from tblname where first ='재석') from tblname;
4.from 절에서 사용하는 경우에만.
- 1~4까지 모두 다 사용 가능
- where 절(값의 역할), select 절(단일 컬럼값의 역할), from 절(테이블의 역할)
--------------------------------------------------------------------------------------------------------------------------------
//상관 서브쿼리, Correlated Sub Query
- 서브쿼리 <-> (연관) <-> 바깥쪽 쿼리
-*와 일반컬럼 같이 사용불가
select *, last || first as fullname from name;
해결방안
=>select n.*, last || first as fullname from name n;
-바깥쪽의 나머지 컬럼의 값과 서브쿼리의 값들 그다지 연관이 없다.(서로 독립적인 값이다.)
select v.*,(select count(*) from tblVideo) from tblVideo v;
-바깥쪽의 나머지 컬럼의 값과 서브쿼리의 값을 연관시킨다. >상관 서브쿼리
select v.*,(select price from tblGenre where seq=v.genre) as "가격" from tblVideo v;