[SQL] Sub Query

◆ 서브쿼리 정의
하나의 SQL명령문의 처리 결과를 다른 SQL명령문에 전달하기 위하여 두개 이상의 SQL문을 하나의 SQL명령문으로 연결한다.
서브쿼리를 포함한 SQL명령문을 메인 쿼리라고 하며, 서브쿼리는 select문의 시작과 끝에 괄호를 묶어서 메인쿼리와 구분한다.
서브쿼리의 거의 대부분은 서브쿼리가 먼저 수행되고나서 그 결과를 메인쿼리에서 이용하는 흐름이다.
그러나 가끔씩 메인쿼리를 먼저 수행하고 나온 그 결과를 서브쿼리에 던져주고 그것을 이용해서 나온 결과를 메인쿼리에 던져주는 상호관련 서브쿼리도 있다.
우리가 서브쿼리를 쓰는 이유는 I/O 속도를 줄이기 위해서이다.


◆ 서브쿼리 사용시 주의사항
  - 반드시 where절에, 비교연산자의 오른쪽에 위치해야 함.
  - 서브쿼리라는 것을 알리기 위해 괄호로 묶어야 함.
  - 서브쿼리 절 안에 order by절이 들어가면 안됨.


◆ 서브쿼리 종류
  - 단일행 서브쿼리(스칼라 서브쿼리) : 서브쿼리 검색결과로 하나의 행만 메인쿼리로 반환.
  - 다중행 서브쿼리 : 하나 이상의 행을 메인쿼리로 반환.
  - 다중컬럼 서브쿼리 : 하나 이상의 컬럼을 메인쿼리로 반환.
  - 상호관련 서브쿼리 : 서브쿼리와 메인쿼리 간에 결과값을 서로 주고받는 식으로 처리되는 서브쿼리.

---

◆ 단일행 서브쿼리(스칼라 서브쿼리)
가장 기본적인 서브쿼리로서, '=' 연산자를 사용한 서브쿼리.

[문제] 학생테이블에서 이광훈 학생과 같은 학과의 학생들의 이름과 학과이름을 출력하시오.

SCOTT> select s.name, d.dname
  2   from student s, department d
  3   where s.deptno = d.deptno
  4   and s.deptno = (select deptno
  5     from student
  6     where name = '이광훈');

NAME    DNAME
---------- ----------------
류민정    컴퓨터공학과
이광훈    컴퓨터공학과
서재진    컴퓨터공학과
임유진    컴퓨터공학과
지은경    컴퓨터공학과
김영균    컴퓨터공학과
박미경    컴퓨터공학과
전인하    컴퓨터공학과

8 rows selected.

[문제] 101번 학과 학생들의 평균몸무게보다 적은 학생의 이름, 학과번호, 몸무게를 출력하시오.

SCOTT> select name, deptno, weight
  2   from student
  3   where weight < (select avg(weight)
  4     from student
  5     where deptno = 101);

NAME        DEPTNO   WEIGHT
---------- ---------- ----------
이동훈    201       64
박미경    101       52
김진영    102       48
지은경    101       42
임유진    101       54
김진경    201       51
조명훈    201       62

7 rows selected.

[문제] 이광훈 학생의 학과의 평균몸무게보다 작은 학생들의 학생이름과 학생의 몸무게, 각 학생들의 학과이름과 지도교수이름을 출력하시오.

SCOTT> select s.name "학생이름",
  2   s.weight "몸무게",
  3   d.dname "학과이름",
  4   p.name "지도교수이름"
  5   from student s, department d, professor p
  6   where s.deptno = d.deptno
  7   and s.profno = p.profno(+)
  8   and s.weight < (select avg(weight)
  9     from student
 10     where deptno = (select deptno
 11       from student
 12      where name = '이광훈'))

학생이름       몸무게     학과이름            지도교수이
---------- ---------- ---------------- ----------
김진경                   51 전자공학과          이재우
김진영                   48 멀티미디어학과    권혁일
지은경                   42 컴퓨터공학과       전은지
임유진                   54 컴퓨터공학과       전은지
이동훈                   64 전자공학과
조명훈                   62 전자공학과
박미경                   52 컴퓨터공학과

7 rows selected.

◆ 다중행 서브쿼리(멀티로우 서브쿼리)
서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리. 다중행 비교 연산자 사용 가능.

◇ 다중행 비교 연산자

종류	의미
IN	메인쿼리의 비교조건이 서브쿼리의 결과 중에서 하나라도 일치하면 참.  '=' 비교만 가능.
ANY, SOME	메인쿼리의 비교조건이 서브쿼리의 결과 중에서 하나 이상 일치하면 참.  '='과 '<', '>' 범위 비교도 가능.
ALL	메인쿼리의 비교조건이 서브쿼리의 결과 중에서 모든값이 일치하면 참.
EXISTS	메인쿼리의 비교조건이 서브쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참.

◇ IN 연산자를 이용한 예

[문제] temp, tdept 테이블을 사용하여 인천에 근무하는 직원의 사번과 성명을 구하세요.

SCOTT> select emp_id, emp_name
  2   from temp
  3   where dept_code in (select dept_code
  4      from tdept
  5     where area = '인천')

    EMP_ID EMP_NAME
---------- ----------
  19930331 정도령
  19950303 이순신
  19966102 지문덕
  20000334 박지리
  20000305 정북악
  20006106 유도봉

6 rows selected.

[문제] temp, tdept, tcom 테이블을 참고하여 부서별로 commission을 받는 인원수를 세는 쿼리를 작성하시오.

<조인만 이용>
SCOTT> select  b.dept_name,
  2   count(*)
  3   from temp a, tdept b, tcom c
  4   where a.dept_code = b.dept_code
  5   and a.emp_id = c.emp_id
  6   and c.comm is not null
  7  group by b.dept_name

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

<조인과 서브쿼리를 이용>
SCOTT> select  b.dept_name,
  2   count(*)
  3   from temp a, tdept b
  4   where a.dept_code = b.dept_code
  5   and a.emp_id in (select emp_id
  6      from tcom
  7      where comm is not null)
  8  group by b.dept_name

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

◇ ANY, ALL을 이용한 예
ANY, ALL은 MAX, MIN보다 속도가 훨씬 빠르다.

> any : 최소값
< any : 최대값

< all : 최소값
> all : 최대값

where sal > any (100, 200, 300)
최소값인 100을 넘긴다. 즉 where sal > 100

where sal < any (100, 200, 300)
최대값인 300을 넘긴다. 즉 where sal < 300

where sal < all (100, 200, 300)
최소값인 100을 넘긴다. 즉 where sal < 100

where sal > all (100, 200, 300)
최대값인 300을 넘긴다. 즉 where sal > 300

위 예에서 현재 sal 컬럼에 들어있는 값이 50, 100, 200, 300, 400, 500이 있다고 가정할 경우에
첫번째 예(> any)를 적용하면 4건(200, 300, 400, 500)이 나오고,
세번째 예(< all)를 적용하면 1건(50)이 나온다.

[문제] temp 테이블에서 과장 중 가장 급여를 작게 받는 사람보다 많이 받는 사원들의 사번, 이름, 급여를 출력하세요.

SCOTT> select emp_id,
  2   emp_name,
  3   salary
  4   from temp
  5   where salary > any (select salary
  6         from temp
  7        where lev = '과장')

    EMP_ID EMP_NAME   SALARY
---------- ---------- ----------
  19970101 김길동        100000000
  19960101 홍길동         72000000
  19930331 정도령         70000000
  19930402 강감찬         64000000
  19950303 이순신         56000000
  19970201 박문수         50000000
  19966102 지문덕         45000000
  19970112 연흥부         45000000

8 rows selected.

◇ EXISTS를 이용한 예
EXISTS는 튜닝시 좀 더 자세히 들어가보도록 한다.

---

[문제] temp에서 부서별 최고 연봉을 확인하고
사원 중에 해당 부서와 최고 연봉금액이 일치하는 사원의
사번, 성명, 연봉을 출력하세요.

SCOTT> select emp_id,
  2   emp_name,
  3   salary
  4   from temp
  5   where (dept_code, salary) in (select dept_code, max(salary)
  6             from temp
  7            group by dept_code)

    EMP_ID EMP_NAME   SALARY
---------- ---------- ----------
  19970101 김길동         100000000
  19960101 홍길동          72000000
  19970201 박문수          50000000
  19930331 정도령          70000000
  19950303 이순신          56000000
  19966102 지문덕          45000000
  19930402 강감찬          64000000
  19960303 설까치          35000000
  19970112 연흥부          45000000
  19960212 배뱅이          39000000

10 rows selected.

 

---

any, all 예제 정리 by 기성.txt