Sub Query 문법 :
SELECT select_list
FROM Table 또는 View
WHERE 조건 연산자 ( SELECT select_list
FROM Table
WHERE 조건) ;
위 문법에서 괄호 안에 있는 쿼리를 Sub Query 라고 부르고 괄호 밖에 있는 쿼리를 Main Query 라고 부른다
Sub Query 의 종류
1) 단일 행 Sub Query
서브 쿼리의 결과가 1 개의 행만 나오는 것
WHERE 절에서 사용되는 연산자
연산자 | 의미 |
= | 같다 |
<> | 같지 않다 |
> | 크다 |
<= | 크거나 같다 |
< | 작다 |
<= | 작거나 같다 |
(1) 단일 행 Sub Query 연습 문제 1 :
Student 테이블과 department 테이블을 사용하여 이윤나 학생과 1 전공이 동일한 학생들의 이름과 1전공 이름을 출력
SCOTT> SELECT s.name , d.dname
FROM studnet s , department d
WHERE s.deptno1 = d.deptno
AND deptno1 = ( SELECT deptno1
FROM student
WHERE name = '이윤나') ;
(2) 단일 행 Sub Query 연습 문제 2 :
Professor 테이블과 department 테이블을 조회하여 입사일이 송도권 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력
SCOTT> SELECT p.name , p.hiredate, d.dname
FROM professor p , department d
WHERE p.deptno = d.deptno
AND hiredate > ( SELECT hiredate
FROM professor
WHERE name = '송도권' ) ;
(3) 단일 행 Sub Query 연습 문제 3 :
Student 테이블에서 1 전공이 101번인 학과의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게를 출력
SCOTT> SELECT name , weight
FROM student
WHERE weight > (SELECT avg(weight)
FROM student
WHERE deptno1 = 101) ;
(4) 단일 행 Sub Query 연습 문제 3 :
Professor 테이블에서 심슨 교수와 같은 입사일에 입사한 교수 중에서 조인형 교수보다 월급을 적게 받는 교수의 이름과 급여, 입사일을 출력
SCOTT> SELECT name , hiredate
FROM professor
WHERE hiredate = (SELECT hiredate
FROM professor
WHERE name = '심슨' )
WHERE pay < (SELECT pay
FROM professor
WHERE name = '조인형' ) ;
2) 다중 행 Sub Query
서브 쿼리의 결과가 2건 이상 출력되는 것
(1) 다중 행 Sub Query 예 1 :
emp2 테이블과 dept2 테이블을 참조하여 근무지역이 서울 지사인 모든 사원들의 사번과 이름, 부서번호를 출력
SCOTT> SELECT empno, name, deptno
FROM emp2
WHERE deptno IN (SELECT dcode
FROM dept2
WHERE area = '서울지사' ) ;
위 서브 쿼리의 결과가 1000,1001,1002,1010 4건의 결과가 나옴. 그래서 다중행 서브쿼리 사용
(2) 다중 행 Sub Query 예 2 - Exists 연산자 사용
Sub Query 의 내용을 먼저 수행해서 그 결과가 1건이라도 나오면 메인쿼리를 수행하고 결과가 한 건도 나오지 않으면 메인쿼리를 아예 실행하지 않는다.
Exists 연산자와 IN 연산자의 차이점
위 결과를 보면 EXISTS 연산자는 서브쿼리의 결과가 있으면 그 결과와 관계없이 메인쿼리를 수행하는 연산자
IN 연산자는 서브쿼리의 결과가 있으면 그 결과에 해당되는 메인쿼리를 수행
(3) 다중 행 Sub Query 연습문제 1 :
emp2 테이블을 사용하여 전체 직원 중 과장 직급의 최소 연봉자보다 연봉이 높은 사람의 이름과 직급, 연봉을 출력
SCOTT> SELECT name , position , to_char(pay,'999,999,999')
FROM emp2
WHERE pay > any ( SELECT pay
FROM emp2
WHERE position = '과장' ) ;
(4) 다중 행 Sub Query 연습문제 2 :
student 테이블을 조회하여 전체 학생 중에서 체중이 4학년 학생들의 체중에서 가장 적게 나가는 학생보다 몸무게가 적은 학생의 이름과 학년과 몸무게를 출력
SCOTT> SELECT name , grade , weight
FROM student
WHERE weight < all ( SELECT avg(weight)
FROM student
WHERE grade = 4 ) ;
(5) 다중 행 Sub Query 연습문제 3 :
emp2 테이블과 dept2 테이블을 조회하여 각 부서별 평균 연봉을 구하고 그 중에서 평균 연봉이 가장 적은 부서의 평균 연봉보다 적게 받는 직원들의 부서명, 직원명, 연봉을 출력
SCOTT> SELECT d.dname , e.name , to_char(e.pay,'999,999,999')
FROM dept2 d , emp2 e
WHERE d.dcode = e.deptno
AND pay < all ( SELECT avg(pay)
FROM emp2
GROUP BY deptno)
ORDER BY 3 ;
3) 다중 컬럼 SUB Query
서브 쿼리의 결과가 여러 컬럼인 경우를 말합니다.
(1) 다중 컬럼 Sub Query 예 1 :
student 테이블을 조회하여 각 학년별로 최대키를 가진 학생들의 학년과 이름과 키를 출력
(2) 다중 컬럼 Sub Query 연습문제 1 :
professor 테이블과 department 테이블을 조회하여 각 학과별로 입사일이 가장 오래된 교수의 교수번호와 이름, 학과명을 출력 ( 입사일 순으로 오름차순 정렬)
SCOTT> SELECT p.profno , p.name , p.hiredate, d.dname
FROM professor p , department d
WHERE p.deptno = d.deptno
AND (p.profno,p.hiredate) IN ( SELECT profno , MIN(hiredate)
FROM professor
GROUP BY deptno)
ORDER BY 3 ;
(3) 다중 컬럼 Sub Query 연습문제 2 :
emp2 테이블을 조회하여 직급별로 해당 직급에서 최대 연봉을 받는 직원의 이름과 직급, 연봉을 출력. 연봉순으로 오름차순 정렬
SCOTT> SELECT name, position, pay
FROM emp2
WHERE (position,pay) IN (SELECT position,MAX(pay)
FROM emp2
GROUP BY position)
ORDER BY 3 ;
4) 상호 연관 Sub Query
메인 쿼리 값을 서브 쿼리에 주고 서브쿼리를 수행 한 후 그결과를 다시 메인 쿼리로 반환해서 수행하는 서브쿼리
(1) 상호연관 Sub Query 예 1:
emp2 테이블을 조회해서 직원 들 중에서 자신의 직급의 평균연봉과 같거나 많이 받는 사람들의 이름과 직급, 현재 연봉을 출력
