[Oracle] 서브 쿼리

// 서브쿼리

- 하나의 문장(select|insert|update|delete) 안에 또다른 문장(select)이 들어있는 쿼리
- 삽입 위치 > 거의 대다수의 절(select절, from절, where절 :: group by절, having절, order by절
  
- 장점
    1. 2개의 SQl을 1개의 SQL로 줄일 수 있다.
    2. 변화에 강하다.(변하는 값을 실시간으로 적용할 수 있음.) > 개발자 편의성 향상

---

- tblCountry. 인구수가 가장 많은 나라의 이름

select 
    name
from tblCountry
    where population = (select max(population) from tblCountry);

---

- tblComedian. 체중이 가장 많이 나가는 사람

select 
    *
from tblComedian
    where weight = (select max(weight) from tblComedian);

---

SELECT * FROM (SELECT * FROM MEMBER ORDER BY REGDATE DESC) WHERE ROWNUM BETWEEN 1 AND 3;

---

- 평균 나이 이상인 회원 목록 조회

SELECT * FROM MEMBER WHERE AGE >= (SELECT AVG(AGE) FROM MEMBER);

---

- 가입일자 순으로 정렬한 후 rownum을 매기고, 그 rownum을 3개씩 페이징

SELECT * FROM(
    SELECT ROWNUM NUM, N.* FROM
        (SELECT * FROM NOTICE ORDER BY REGDATE DESC ) N
    ) WHERE NUM BETWEEN 1 AND 3;
    
SELECT * FROM(
    SELECT ROWNUM NUM, N.* FROM
        (SELECT * FROM NOTICE ORDER BY REGDATE DESC ) N
    ) WHERE NUM BETWEEN 4 AND 6;

---

// 삽입 위치

1. 조건절
2. 컬럼리스트
3. from절

---

1. 조건절

    - 비교대상(비교값) 역할 > 값

    - where절, having절, case문
            
    a. 반환값이 1행 1열 > 단일값 반환 > 값 1개로 취급
    b. 반환값이 n행 1열 > 다중값(같은 성질의 여러 개 데이터) 반환 > 값 N개로 취급
    c. 반환값이 1행 n열 > 다중값(서로 다른 성질의 여러 개 데이터) 반환 >
    d. 반환값이 n행 n열 > 다중값 반환

---

a. 반환값이 1행 1열

select * from tblInsa
    where basicpay >= (select avg(basicpay) from tblInsa);

---

select 
    buseo,
    avg(basicpay)
from tblInsa
    group by buseo
        having avg(basicpay) > (select avg(basicpay) from tblInsa where buseo = '영업부');

---

 b. 반환값이 n행 1열

select * from tblInsa
    where buseo in (select buseo from tblInsa where basicpay >= 2600000);

---

select * from tblInsa
    where buseo in (select buseo from tblInsa 
                        where city = (select city from tblInsa where name='홍길동') 
                            and jikwi = (select jikwi from tblInsa where name='홍길동')
                                and name <> '홍길동');

---

 c. 반환값이 1행 n열

select * from tblInsa
    where (city, jikwi) = (select city, jikwi from tblInsa where name = '홍길동');

---

d. 반환값이 n행 n열 

select * from tblInsa
    where (city, buseo) in (select city, buseo from tblInsa where basicpay >= 2600000);

---

2. 컬럼리스트

- 단일 데이터(값) > 원자값 > 관계형 데이터베이스 > 표 > 속성(컬럼)의 값은 원자값이어야 한다.

- 서브쿼리의 결과값이 반드시 1행 1열이어야 한다. > 스칼라 쿼리

- 정적쿼리 > 모든 행에 동일한 값을 반환            

- 상관(상호) 서브 쿼리 > 사용 빈도 높음

    a. 컬럼명        
    b. 상수
    c. 연산
    d. 함수     

---

select 
    name, buseo, basicpay,
    (select round(avg(basicpay)) from tblInsa) as "평균급여"
from tblInsa;

---

select 
    name, age, 
    couple as "여자친구 이름",
    (select age from tblWomen where name = i.couple) as "여자친구 나이"
from tblMen i;

---

3. from절           

- 서브쿼리의 결과셋을 하나의 테이블이라고 생각하고 또다른 select를 실행함.

- 구문을 단순화하기 위해서 사용

---

select
    *
from (select name, buseo, jikwi from tblInsa);

---

select
    이름
from(select name as 이름, buseo, jikwi from tblInsa);