2012.01.09 SQL 5일차 두번째(Sub Query)

Sub Query

; 하나의 SQL명령문의 처리결과를 다른 SQL명령문에 전달하기 위하며 사용하는 것
; 잘 이용하면 속도가 빠릅니다., 이 테이블, 저 테이블 이런 식으로 여러번 왔다갔다 하는걸 줄이기 위해서 이용합니다.

서브쿼리가 올 수 있는 위치(위치에 따라 지칭하는 이름이 다릅니다.)

select ( SELECT ...)  : 스칼라 서브쿼리

from (SELECT ....) : 인라인 뷰

where sal > (SELECT ...) : 서브쿼리


* 서브쿼리 사용시 주의 사항

연산자를 기준으로 오른쪽에 사용하고
사용시 ( )로 묶어서 사용합니다.
서브쿼리에는 order by를 사용하지 않습니다.

서브쿼리의 종류

단일행 서브쿼리 : 서브쿼리의 검색결과가 하나의 행, 하나의 행만 메인쿼리로 반환
다중행 서브쿼리 : 서브쿼리의 검색결과가 여러개의 행
다중 칼럼 서브쿼리 : 하나 이상의 칼럼을 메인 쿼리로 반환
상호 관련 서브쿼리 : 서브쿼리와 메인쿼리간의 결과값을 서로 주고 받는식으로 처리되는 서브쿼리

 

단일행 서브쿼리 사용 예
1. userid가 'jun123'인 학생과 학년이 같은 학생들의 학번, 이름, 학년 출력

SQL> select studno, name, grade
  2  from student
  3  where grade=
  4     (select grade
  5     from student
  6     where userid='jun123');

    STUDNO NAME       G
---------- ---------- -
     10101 전인하     4
     10202 오유석     4
     10107 이광훈     4

 
2. 20101번 학생과 학년이 같고, 키는 20101번 학생보다 큰 학생의 이름,학년,키를 출력하여라.

  SQL> select name, grade, height
  2  from student
  3  where grade=
  4     (select grade from student
  5     where studno=20101)
  6  AND
  7     height >
  8     (select height from student
  9     where studno=20101);

NAME       G     HEIGHT
---------- - ----------
박동진     1        182
서재진     1        186
조명훈     1        184 

다중행 서브쿼리

; 사용시 단순한 비교 연산자는 사용 불가능
예를 들어 서브쿼리의 반환값이 100,200,300 이라면

SAL > (100,200,300) 이런식으로 되어버려서 무엇을 기준으로 해야할지 모른다.

다중행 비교연산자
IN : '=' 비교를 하기 위해 사용
ANY : 범위 비교를 하기 위해 사용, 메인쿼리의 비교조건이 서브쿼리의 결과중에서 하나 이상 일치하면 참
ALL : 범위 비교를 하기 위해 사용, 메인쿼리의 비교조건이 소브쿼리의 결과 중에서 모든값이 일치하면 참

외우세요 : 

< ANY ( A,B,C ) : A,B,C 중 최대값 반환

> ANY ( A,B,C)  : A,B,C 중 최소값 반환

> ALL ( A,B,C )  :  A,B,C 중 최대값 반환

< ALL ( A,B,C )  :  A,B,C 중 최소값 반환

 ( <를 입이라고 생각하고,

< ANY : 조금 잡아먹을땐 다 잡아 먹을 수 있으니까 MAX,
> ANY : 조금 먹지 말라고 해서 소심해져서 MIN,
> ALL  : 하나도 먹지 말라고 해서 엄청 화나서 MAX,
< ALL  : 다 잡아먹으려니까 다 잡아먹지 못하므로 MIN)

IN 의 사용 예

* temp , tdept , tcom 테이블을 참고하여 부서별로 commission 을 받는 인원수를 세는 쿼리를 작성하세요.

  1  select tdept.dept_name, count(emp_id)
  2  from temp, tdept
  3  where temp.dept_code = tdept.dept_code
  4  AND emp_id
  5     IN(select emp_id
  6     from tcom)
  7* group by tdept.dept_name
SQL> /

DEPT_NAME            COUNT(EMP_ID)
-------------------- -------------
영업기획                         2
영업1                            2
영업2                            2
영업                             2
 

ANY, ALL의 사용 예

* 모든 학생 중에서 4학년 학생 중에서 키가 제일 작은 학생보다 키가 큰 학생의 학번, 이름, 키를 출력하여라.

  1  select studno, name, height
  2  from student
  3  where height >
  4     any(select weight
  5     from student
  6*    where grade=4)
SQL> /

    STUDNO NAME           HEIGHT
---------- ---------- ----------
     10106 서재진            186
     20104 조명훈            184
     20102 박동진            182
     10202 오유석            177
     10101 전인하            176
     10107 이광훈            175
     20101 이동훈            172
     10105 임유진            171
     10204 윤진욱            171
     10103 김영균            170
     10102 박미경            168
     20103 김진경            166
     10201 김진영            164
     10108 류민정            162
     10104 지은경            161
     10203 하나리            160

16 rows selected. 

* temp 테이블에서 전체 직원중 급여가 대리 중 가장 급여를 많이 받는 사람보다 많이 받는 사원들의 사번, 이름, 급여 를 출력하세요.

  1  select emp_id, emp_name, salary
  2  from temp
  3  where salary >
  4     all(select salary from temp
  5*    where lev='대리')
SQL> /

    EMP_ID EMP_NAME       SALARY
---------- ---------- ----------
  19970101 김길동      100000000
  19960101 홍길동       72000000
  19930331 정도령       70000000
  19930402 강감찬       64000000 

123