SQL 11 - 서브 쿼리 : 다중 행, 다중 열 서브 쿼리

다중 행 서브 쿼리

SELECT [DISTINCT | ALL] 컬럼, 컬럼 ...
FROM 테이블
WHERE 컬럼 다중_행_연산자 (SELECT 문장 : Sub query문)

- 다중 행 연산자
. IN : 나열된(검색된) 값 중에 하나만 일치하면 참이다.
. ANY : 나열된(검색된) 값 중에 조건에 맞는 것이 하나 이

 

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

SELECT eno 사번, ename 이름
FROM emp
WHERE mgr IN (SELECT mgr
		FROM emp
		WHERE dno = '20')
AND dno != '20'; // 20번 부서원이 검색되는 것을 막기 위한 조건

사번    이름
------- ----------
1001    문시현
1003    양선호
2001    남궁연호
2003    정의찬

 

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

SELECT eno 사번, ename 이름, dno 부서번호
FROM emp
WHERE sal < ALL (SELECT sal
		FROM emp
		WHERE dno = '10');

사번    이름       부서번호
------- ---------- ---------
2008    윤고은     40
2007    이초록     30
2002    제갈민     20
0702    김민지     02
0309    김선유     02

 

SELECT eno 사번, ename 이름, dno 부서번호
FROM emp
WHERE sal < ALL (SELECT MIN(sal)
		FROM emp
		WHERE dno = '10');

사번    이름       부서번호
------- ---------- ---------
2002    제갈민     20
2007    이초록     30
2008    윤고은     40
0309    김선유     02
0702    김민지     02

 

* 다중 행 연산자와 그룹함수
다중 행 연산자인 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 != '손하늘';

사번    이름       관리자   업무
------- ---------- -------- -------
0201    안영숙     0001     지원

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

 

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 != '손하늘';

사번    이름       관리자   업무
------- ---------- -------- -------
0201    안영숙     0001     지원

 

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

SELECT eno 사번, ename 이름, dno 부서번호, job 업무
FROM emp
WHERE (dno, job) IN (SELECT dno, job
			FROM emp
			WHERE ename = '김선유')
AND ename != '김선유';

사번    이름       부서번호  업무
------- ---------- --------- -------
0301    이승철     02        회계
0302    박선경     02        회계
0702    김민지     02        회계
0269    권나현     10        분석

 

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 != '김선유';

사번    이름       부서번호  업무
------- ---------- --------- -------
0301    이승철     02        회계
0302    박선경     02        회계
0702    김민지     02        회계
0801    천유정     02        분석
0401    김진성     10        회계
0269    권나현     10        분석

 

SELECT ename, dno, job
FROM emp
WHERE ename = '김선유';

ENAME      DNO     JOB
---------- ------- -------
김선유     02      회계
김선유     10      분석

실습

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

SELECT sno 학번, sname 이름, major 전공
FROM student
WHERE avr IN (SELECT avr FROM student WHERE major = '화학')
AND major != '화학';

학번    이름       전공
------- ---------- -------
948209  유태지     식영
924505  서찬호     물리
913904  이호란     생물
933903  정승동     생물
915605  강은혜     유공
924502  은정현     물리
935604  권석현     유공

학번    이름       전공
------- ---------- -------
945605  오경운     유공
943905  유태지     생물
943903  유지아     생물
944512  서동조     물리
945603  정백영     유공

 

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

SELECT eno 학번, ename 이름, hdate 부임일자
FROM emp
WHERE hdate IN (SELECT hiredate FROM professor WHERE section = '화학');

학번    이름       부임일자
------- ---------- ----------
0201    안영숙     1991/02/01
1001    문시현     1991/02/01

3. 화학과 학생과 같은 학년에서 평점이 동일한 학생들을 검색한다. (다중열 서브쿼리)

SELECT syear 학년, sno 학번, sname 이름, major 전공, avr 평점
FROM student
WHERE (syear, avr) IN (SELECT syear, avr FROM student WHERE major = '화학')
AND major != '화학';

      학년 학번    이름       전공          평점
---------- ------- ---------- ------- ----------
         1 945605  오경운     유공          2.99

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

SELECT eno 사번, ename 이름, sal*12+NVL(comm,0) 연봉, dno 부서번호
FROM emp
WHERE (sal*12+NVL(comm,0)) > (SELECT MAX(sal*12+NVL(comm,0)) FROM emp WHERE dno = 10)
AND dno != 10;

사번    이름             연봉 부서번호
------- ---------- ---------- ---------
0001    안영희          57600 01

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

SELECT eno 사번, ename 이름, job 업무, sex 성별
FROM emp
WHERE (job, sex) IN (SELECT job, sex FROM emp WHERE dno = 10)
AND dno != 10;

사번    이름       업무    성
------- ---------- ------- ---
1003    양선호     모델링  남
2002    제갈민     개발    남
2003    정의찬     개발    남
2007    이초록     개발    남
3002    권아현     분석    여
0801    천유정     분석    여
0301    이승철     회계    남

사번    이름       업무    성
------- ---------- ------- ---
0309    김선유     회계    남
0702    김민지     회계    남