[Oracle] 11. 서브 쿼리 : 다중 행, 다중 열 서브 쿼리 정리

다중 행 서브 쿼리

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

from 테이블

where 컬럼 다중_행_연산자 (select 문장 : sub query문)

• 다중 행 연산자

• IN : 나열된(검색된) 값 중에 하나만 일치하면 참이다.
• ANY : 나열된(검색된) 값 중에 조건에 맞는 것이 하나 이상 있으면 참이다.
• ALL : 모든 나열된(검색된) 값과 조건에 맞아야 한다.

예제 1. 20번 부서원들과 동일한 관리자로부터 관리 받는 사원을 검색한다.

select eno 사번, ename 이름

from emp

where mgr in(select mgr from emp where dno = '20')

and dno !='20';

#20번부서원이 검색되는것을 막기 위한 조건

예제 2. 10번 부서원들보다 급여가 낮은 사원을 검색한다.

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

from emp

where sal < all (select sal from emp where dno='10');

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

from emp

where sal < (select min(sal) from emp where dno='10');

다중 행 연산자와 그룹함수

다중 행 연사자인 ALL이나 ANY는 다음과 같이 그룹 함수를 이용 표현할 수 있다. 그룹 함수는 그룹 함수는 [19장 그룹 함수]에서 자세히 설명한다.

• 컬럼 > ALL -> 컬럼 > MAX() : 가장 큰 값보다 크다
• 컬럼 < ALL -> 컬럼 > MIN() : 가장 작은 값보다 작다.

• 컬럼 > ANY -> 컬럼 > MIN() : 가장 작은 값보다 크다.
• 컬럼 < ANY -> 컬럼 < MAX() : 가장 큰 값보다 작다.

다중 열 서브 쿼리

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

from 테이블

where (컬럼1, 컬럼2, …) IN (select 문장 : sub query문)

예제 3. 손하늘과 동일한 관리자의 관리를 받으면서 업무도 같은 사원을 검색한다.

select eno 사번, ename 이름, mgr 관리자, job 업무

from emp

where (mgr, job) IN (select mgr, job from emp where ename = '손하늘')

and ename !='손하늘';

손하늘이 두 명 이상이라면 수정이 불가능하다.

select eno 사번, ename 이름, mgr 관리자, job 업무

from emp

where mgr = (select mgr from emp where ename ='손하늘')

and job = (select job from emp where ename = '손하늘')

and ename != '손하늘';

예제 4. 김선유와 부서 및 업무가 동일한 사원을 검색한다.

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

from emp

where (dno, job) in (select dno, job from emp where ename ='김선유') and ename !='김선유';

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

from emp

where dno in (select dno from emp where ename = '김선유')

and job in (select job from emp where ename = '김선유')

and ename !='김선유';

select ename, dno, job from emp where ename = '김선유';

# 다중행 쿼리를 쓰지 않으면 위에 내용처럼 두개의 데이터가 섞여서 조회 될 수 있음

실습

 1. 화학과 학생의 평점이 동일한 학생들을 검색한다.

select *

from student t

where t.avr = any ( select avr from student where major='화학')

and t.major != '화학';

# any를 이용하여 모든 값과 대입할 수 잇다.

 2. 화학과 교수와 부임일이 같은 직원을 검색한다.

select distinct e.ename, e.hdate

from professor p, emp e

where e.hdate=

any(select hiredate from professor where section='화학');

# 교수에 조건이 있으므로 직원고용일에 any 를 이용해 서브쿼리를 해야함.

 3. 화학과 학생과 같은 학년에서 평점이 동일 한 학생들을 검색한다.

select distinct *

from student

where (avr, syear) IN (select avr, syear from student where major = '화학')

and major !='화학';

#avr 평점과 학년을 같이 맞춰야 하기에 다중행 쿼리를 사용하였다.

 4. 10번 부서 사원들보다 년봉을 많이 받는 사원을 검색한다.

select *

from emp

where (sal*12+comm) > any(select (sal*12+comm) from emp where dno = '10')

and dno != '10'

order by (sal*12+comm) desc;

#최저에 맞춰야하는건지 맥스에 맞춰야하는건지 모르겠다

 5. 10번 부서 사원들과 업무와 성별이 동시에 일치하는 사원을 검색한다.

select *

from emp

where (sex, job) IN (select sex, job from emp where dno='10')

and dno!='10';

#동시에 일치하기 위해선 다중행 쿼리를 사용해야 한다.