[Oracle] 10. 서브 쿼리 : 단일 행 서브 쿼리 정리

서브 쿼리란?

단일 행 서브 쿼리

• 서브 쿼리가 하나의 컬럼에서 하나의 행을 검색

다중 행 서브 쿼리

• 서브 쿼리가 하나의 컬럼에서 여러 개의 행을 검색

다중 열 서브 쿼리

• 서브 쿼리가 여러 개의 컬럼을 검색

서브 쿼리는 WHERE절, HAVING절과 같이 조건 절에 주로 쓰이고 from절에 쓰이는 우도 있다. 특히 from절에 쓰인 서브 쿼리는 인라인 뷰(inline view)라고 부른다.

단일 행 서브 쿼리

select [distinct | all] 컬럼, 컬럼…

from 테이블

whre 컬럼 단일_행_연산자 (select 문장 : sub query 문)

• 단일 행 연산자가 사용됨으로 반드시 서브 쿼리의 결과 값은 한 개만 검색돼야 한다.
• 단일 행 연산자 오른쪽에 기술한다.

(=, <, >, <=, >=, !=)

• where절에 기술된 열의 숫자와 타입은 select절과 1:1 대응 관계가 되어야 한다.

예제 1. 남궁연호보다 급여를 많이 받는 사원을 검색한다.

select eno 사번, ename 이름

from emp

where sal > (select sal from emp where ename ='남궁연호');

예제 2. 김선유보다 급여를 많이 받는 사원을 검색한다.

select eno 사번, ename 이름

from emp

where sal > (select sal from emp where ename='김선유');

오류 남 2개 동명이인이 있음

• 예측하기 힘든 단 일 행 서브 쿼리를 수정하는 방법

1. '=' 연산자는 'IN' 연산자로 바꾼다.
2. 부등호('<', '>', '<=', '>=')는 any, all 연산자를 추가한다.
3. max(), min()과 같은 그룹 함수를 사용 한다.

예제 3. 문시현과 부서가 다르고 동일한 업무를 하는 사원의 정보를  검색한다.

select eno 사번, ename 이름, dno 부서번호, job 업무

from emp

where dno != (select dno from emp where ename = '문시현')

and job = (select job from emp where ename = '문시현');

예제 4. 부산에서 근무하는 사원의 정보를 검색한다.

select eno 사번, ename 이름

from emp

where dno= ( select dno from dept where loc = '부산');

select eno 사번, ename 이름

from emp e, dept d

where e.dno = d.dno

and loc = '부산';

서브쿼리보다 자기참조조인이 훨씬 짧게 끝날 수 있음

실습

 1. 권혁윤보다 평점이 우수한 학생의 학번과 이름을 검색한다.

select sname 이름, sno 학번

from student

where avr > ( select avr from student where sname = '김혁윤');

# 서브쿼리를 이용하여 김혁윤의 점수를 빼낸다.

 2. 권현과 동일한 학년 학생 중에 평점이 강은혜와 동일한 학생을 검색한다.

select *

from student

where syear = (select syear from student where sname ='권현')

and avr = (select avr from student where sname = '강은혜');

# 권현과 강은혜의 학년과 평점을 서브쿼리를 이용하여 빼낸다.

 3. 이학수학 과목과 동일한 학점수인 과목을 검색한다.

select *

from course

where st_num=(select st_num from course where cname = '이학수학');

 4. 타 학과에 송강 교수와 동일한 지위의 교수 명단을 검색한다.

select pname

from professor

where section != (select section from professor where pname='송강') and orders = (select orders from professor where pname='송강');

 5. 제갈민보다 나중에 부임한 교수의 명단을 검색한다.

select pname, hiredate

from professor

where hiredate > (select hdate from emp where ename='제갈민');

 6. 강태용보다 일반 화학 과목의 점수가 더 낮은 학생의 명단을 학점과 검색한다.

select t.sname, s.result

from student t, course c, score s

where s.sno=t.sno

and c.cno = s.cno

and c.cname ='일반화학'

and s.result <

(select s.result

from score s, student t, course c

where s.sno=t.sno

and c.cno = s.cno

and t.sname='강태용'

and c.cname ='일반화학');

# 조건의 일반화학은 course 테이블의 cname에 있으므로

# 다같이 이어주고 서브쿼리에서도 학생이름과 과목명이 필요하다

# 서브쿼리안에서도 조인을 해준다.