서브쿼리

1. SUB-QUERY 정의
하나의 SQL 명령문의 처리 결과를 다른 SQL 명령문에 전달하기 위하여 두개 이상의 SQL문을 하나의 SQL 명령문으로 연결한다.
서브쿼리를 포함한 SQL명령문을 메인 쿼리 라고 하며 서브쿼리는 SELECT 문의 시작과 끝에 ( ) 를 묶어서 메인 쿼리 구분한다.

 

사용예)
서브쿼리를 이용하여 ‘전은지’ 교수와 동일한 직급의 교수 이름을 검색하세요.
 SQL>   SELECT    name, position
 FROM      professor
 WHERE    position = ( SELECT position
           FROM professor
           WHERE name = ‘전은지’);
출력결과)
NAME       POSITION
-------  --------
염일웅      전임강사
전은지      전임강사

 

2. 서브 쿼리 종류
단일행 서브쿼리, 다중 행 서브쿼리, 다중 칼럼 서브쿼리, 상호관련 서브쿼리

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

 

2.1 단일행 서브쿼리 
  ‘=‘ 연산자를 사용한 단일행 서브쿼리

사용예) 사용자 아이디가 ‘jun123’인 학생과 같은 학년인 학생의 학번, 이름, 학년을 출력하세요.

SQL>    SELECT studno, name, grade
 FROM    student
 WHERE  grade = (SELECT grade
              FROM   student
              WHERE userid = ‘jun123’);
출력결과)
STUDNO   NAME    GRADE
-------  ------  ------
    10101   전인하             4
    10202   오유석             4
    10107   이광훈             4

 

문제 1

학생테이블에서 이광훈 학생과 같은 학과의 학생들의 이름과 학과이름을 출력하세요.

select s.name "이름", d.dname "학과이름"
from student s, department d
where s.deptno=d.deptno
and s.deptno = (select deptno
                       from student
                      where name='이광훈')
SQL> /

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

 

문제2

101번 학과 학생들의 평균몸무게보다 적은 학생의 이름,학과번호,몸무게를 출력하세요

 select name, deptno,weight
 from student
 where weight < (select avg(weight)
                         from student
                         where deptno=101)
SQL> /

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

 

2.2 ‘<‘ 연산자를 사용한 단일행 서브 쿼리
사용예) 101번 학과 학생들의 평균 몸무게보다 몸무게가 적은 학생의 이름, 학과 번호, 몸무게를 출력하여라.

SQL>    SELECT name, deptno, weight
            FROM student
            WHERE   weight < ( SELECT AVG(weight)
                            FROM student
             WHERE deptno = 101)
             ORDER BY deptno;
출력결과)
NAME      DEPTNO   WEIGHT
------  -------  -------
박미경            101           52
지은경            101           42
………..
7개의 행이 선택되었습니다.

문제

이광훈 학생의 학과의 평균몸무게보다 작은 학생들의 학생이름과 학생의 몸무게, 각 학생들의 학과이름과 지도교수 이름을 출력하세요.

select s.name, s.weight, d.dname, p.name
from student s, department d, professor p
where s.deptno=d.deptno and s.profno=p.profno(+)
and weight < (select avg(weight)
                     from student
                    where deptno=(select deptno
                                          from student
                                          where name='이광훈'))
SQL> /

NAME           WEIGHT DNAME            NAME
---------- ---------- ---------------- ----------
김진경             51 전자공학과       이재우
김진영             48 멀티미디어학과   권혁일
지은경             42 컴퓨터공학과     전은지
임유진             54 컴퓨터공학과     전은지
이동훈             64 전자공학과
조명훈             62 전자공학과
박미경             52 컴퓨터공학과

7 rows selected.

 

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

답) SQL>   SELECT name, grade, height
     FROM   student
     WHERE  grade = ( SELECT grade
          FROM    student
          WHERE  studno = 20101)
     AND       height > ( SELECT   height
                      FROM student
           WHERE studno = 20101);
출력결과)
NAME       GRADE        HEIGHT
------- --------   -------
박동진                 1            182
서재진                 1            186
조명훈                 1            184

 

2.4 다중행 서브 쿼리
 
 서브쿼리에서 반환되는 결과 행이 하나 이상일 때 사용하는 서브쿼리.
 다중 행 비교 연산자를 사용 가능.

 

2.4.1 IN 연산자
 메인 쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인 쿼리 조건 절이 참이 되는 연산자임. ‘=‘연산자를 OR로 연결한 것과 동일

사용예)
정보 미디어학부(부서번호:100)에 소속된 모든 학생의 학번, 이름, 학과번호를 출력하여라.

 SQL>   SELECT name, grade, deptno
 FROM student
 WHERE deptno IN ( SELECT deptno
                             FROM department
                            WHERE  college = 100);

 

위 서브쿼리를 실행하면 2개의 결과가 나오며 이 두 개의 결과값이 IN 연산자를 통해 메인 쿼리에게 전달된다.

문제 1

Temp , tdept 테이블을 사용하여 인천에 근무하는 직원의 사번과 성명을 구하세요
select emp_id "사번", emp_name "성명"
from temp
where dept_code in(select dept_code
                             from tdept
                            where area='인천')
SQL> /

      사번 성명
---------- ----------
  19930331 정도령
  19950303 이순신
  19966102 지문덕
  20000334 박지리
  20000305 정북악
  20006106 유도봉

6 rows selected.

 

문제 2

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

 

select b.dept_name "부서", count(*) "명수"
from temp a, tdept b
where b.dept_code =a.dept_code
and a.emp_id in(select emp_id
                        from tcom)
                        group by b.dept_name
SQL> /

부서                       명수
-------------------- ----------
영업기획                      2
영업1                         2
영업2                         2
영업                          2 

 

 2.4.2 ANY/ALL

 ANY 연산자
 메인 쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인쿼리 조건 절이 참이 되는 연산자이다.

 

 ALL 연산자 사용
 ALL 연산자는 메인쿼리의 비교조건에서 서브쿼리의 검색 결과와 모두 일치하면 메인쿼리 조건절이 참이 되는 연산자임.

<any : 최대값
>any : 최소값
>all 최대값
<all 최소값
두 가지는 부등호가 다르기 때문에 조심

 

문제 1

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

select emp_id "사번", emp_name "이름", salary "급여"
from temp
where salary >any (select salary
                            from temp
                           where lev='과장')
SQL> /

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

 

2.4.3 EXISTS 연산자
  서브쿼리에서 검색된 결과가 하나라도 존재하면 메인쿼리 조건절이 참이 되는 연산자임.

 

3. 다중 칼럼 서브쿼리
서브쿼리에서 여러 개의 칼럼 값을 검색하여 메인쿼리의 조건절과 비교하는 서브 쿼리이다. PAIRWISE 방식과 UNPAIRWISE 방식이 있다.

 

4. 상호 연관 서브쿼리
메인쿼리와 서브쿼리간에 검색 결과를 교환하는 서브쿼리이다

 

5. 서브쿼리 사용시 오류사항
 * 단일행 서브쿼리오류 ? 서브쿼리 리턴값이 1개가 아닌경우
 * 메인쿼리와 서브쿼리 칼럼의 수가 일치하지 않을 경우
 * 서브쿼리내에 ORDER BY 절을 사용할 경우
 * 서브쿼리 검색 결과가 NULL일 경우- NULL과 연산결과는 모두 NULL 됨

 

문제

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

select emp_id "사번", emp_name "이름", salary "연봉"
 from temp
 where (dept_code,salary) in(select dept_code, max(salary)
                                          from temp
                                          group by dept_code)
SQL> /

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