데이터베이스 - 서브쿼리 기초1

서브 쿼리

 - 단일 행 서브 쿼리

 

서브 쿼리란?
단일 열, 단일 행 서브 쿼리 : 
 - 서브 쿼리가 하나의 컬럼에서 하나의 행을 검색한다. 

단일 열, 다중 행 서브 쿼리
 - 서브 쿼리가 하나의 컬럼에서 여러 개의 행을 검색한다. 

다중 열 서브 쿼리
 - 서브 쿼리가 여러 개의 컬럼을 검색한다. 

 

서브 쿼리는 WHERE절, HAVING절과 같이 조건 절에 주로 쓰이고 FROM절에 쓰이는 경우도 있다.

특히 FROM절에 쓰인 서브 쿼리는 인라인 뷰(Inline View)라고 부른다. 

 

단일 행 서브 쿼리
SQL> SELECT [DISTINCT | ALL] 컬럼, 컬럼 ...
 2 FROM 테이블
 3 WHERE 컬럼 단일_행_연산자 (SELECT 문장 : Sub query문)
 - 단일 행 연산자가 사용됨으로 반드시 서브 쿼리의 결과 값은 한 개만 검색돼야 한다.
 - 단일 행 연산자 오른쪽에 기술한다.
 (=, <, >, <=, >=, !=)
 - WHERE절에 기술된 열의 숫자와 타입은 SELECT절과 1:1 대응 관계가 되어야 한다. 

 

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

SQL> SELECT eno 사번, ename 이름
 2 FROM emp 
 3 WHERE sal > (SELECT sal 
 4                         FROM emp
 5                         WHERE ename = '남궁연호');

 

사번 이름
---- ----------
0001 안영희
1001 문시현
1002 김주란
.............
............. 

 

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

SQL> SELECT eno 사번, ename 이름

 2 FROM emp 
 3 WHERE sal > (SELECT sal 
 4                         FROM emp
 5                         WHERE ename = '김선유');

WHERE sal > (SELECT sal
                         *

SELECT sal FROM emp WHERE ename = '김선유';

 900 

 3200

 

SQL> select eno 사번, ename 이름
  2  from emp
  3  where sal > 900, 3200;  -> sal은 단일 행 연산자이기 때문이다. 계산할려면 다중 행 연산자로 바꿔줘야한다.

3행에 오류:
ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

 

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

 ① '=' 연산자는 'IN' 연산자로 바꾼다.
 ② 부등호 ('<', '>', '<=', '>=')는 any, all 연산자를 추가한다.
 ③ Max(), Min()과 같은 그룹 함수를 사용 한다. 

 

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

SQL> SELECT eno 사번, ename 이름, dno 부서번호, job 업무
 2 FROM emp 
 3 WHERE dno != (SELECT dno FROM emp WHERE ename = '문시현')
 4 AND job = (SELECT job FROM emp WHERE ename = '문시현'); 

 

사번 이름 부서번호 업무
---- ---------- -------- -------
1002 김주란 20 모델링
1003 양선호 30 모델링

 

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

SQL> SELECT eno 사번, ename 이름
 2 FROM emp 

 3 WHERE dno = (SELECT dno
 4                          FROM dept

 5                         WHERE loc = '부산');

 

사번 이름
---- ------
1002 김주란
2002 제갈민
3002 권아현
0120 김경현

 

SQL> SELECT eno 사번, ename 이름
 2 FROM emp e, dept d 
 3 WHERE e.dno = d.dno
 4 AND loc = '부산';

 

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

    SELECT SNO 학번, SNAME 이름, AVR 평점

 2 FROM STUDENT

 3 WHERE AVR > (SELECT AVR 평점 FROM STUDENT WHERE SNAME = '김혁윤');

 

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

    SELECT SNO 학번, SNAME 이름, SYEAR 학년, AVR 평점

 2 FROM STUDENT

 3 WHERE SYEAR = (SELECT SYEAR FROM STUDENT WHERE SNAME = '권현')

 4 AND AVR = (SELECT AVR FROM STUDENT WHERE SNAME = '강은혜');

 

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

    SELECT CNO 과목번호, CNAME 과목이름, ST_NUM 학점

 2 FROM COURSE

 3 WHERE ST_NUM = (SELECT ST_NUM FROM COURSE WHERE CNAME = '이산수학');

 

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

    SELECT PNO 교수번호, PNAME 교수이름, SECTION 담당학과, ORDERS 직위

 2 FROM PROFESSOR 

 3 WHERE ORDERS = (SELECT ORDERS FROM PROFESSOR WHERE PNAME = '송강');

 

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

    SELECT PNO 교수번호, PNAME 교수이름, HIREDATE 부임일

 2 FROM PROFESSOR, EMP

 3 WHERE HIREDATE < (SELECT HDATE FROM EMP WHERE ENAME = '제갈민');

 

6. 강태용보다 일반 화학 과목의 학점(A,B,..)이 더 낮은 학생의 명단을 학점과 검색한다. 

    SELECT s.sno, sname, cname, grade
 2 FROM student s, course c, score r, scgrade g
 3 WHERE s.sno=r.sno AND r.cno=c.cno AND result BETWEEN loscore AND hiscore
 4 AND cname='일반화학' AND grade > (SELECT grade FROM student s, course c, scgrade g,

 5 score r WHERE s.sno=r.sno AND r.cno=c.cno AND result BETWEEN loscore AND hiscore
 6 AND sname='강태용' AND cname='일반화학');

 

 

서브 쿼리 : 다중 행, 다중 열 서브 쿼리

다중 행 서브 쿼리 
SQL> SELECT [DISTINCT | ALL] 컬럼, 컬럼 ...
 2 FROM 테이블
 3 WHERE 컬럼 다중_행_연산자 (SELECT 문장 : Sub query문)

 

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

 

EX) 

sal in (1000,20000,200)

 

sal > any (100,200,300) 어떤것보다 작다.  ---->  99

sal > all (100,200,300) 전체보다 작다.    -------> 299

 

sal < any (100,200,300) 어떤것보다 크다.   ---> 301

sal < all (100,200,300) 전체보다 크다.     ----> 101

 

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

SQL> SELECT eno 사번, ename 이름 

 2 FROM emp 
 3 WHERE mgr IN (SELECT mgr 

 4                            FROM emp 
 5                            WHERE dno = '20')
 6 AND dno != '20'; 

 

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

 

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

SQL> SELECT eno 사번, ename 이름, dno 부서번호
 2 FROM emp 
 3 WHERE sal < ALL (SELECT sal 

 4                                FROM emp
 5                               WHERE dno = '10');

 

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

0309 김선유 02

 

SQL> SELECT eno 사번, ename 이름, dno 부서번호
 2 FROM emp 
 3 WHERE sal < (SELECT MIN(sal) 4 FROM emp
 5 WHERE dno = '10');

 

* 다중 행 연산자와 그룹함수
다중 행 연산자인 ALL이나 ANY는 다음과 같이 그룹 함수를 이용 표현할 수 있다. 

그룹 함수는 [19장 그룹 함수]에서 자세히 설명한다.
 * 컬럼 > ALL → 컬럼 > MAX() : 가장 큰 값보다 크다
 * 컬럼 < ALL → 컬럼 < MIN() : 가장 작은 값보다 작다.
 * 컬럼 > ANY → 컬럼 > MIN() : 가장 작은 값보다 크다.
 * 컬럼 < ANY → 컬럼 < MAX() : 가장 큰 값보다 작다

 

다중 열 서브 쿼리
SQL> SELECT [DISTINCT | ALL] 컬럼, 컬럼 ...
 2 FROM 테이블
 3 WHERE (컬럼1, 컬럼2, ... ) IN (SELECT 문장 : Sub query문)

 

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

SQL> SELECT eno 사번, ename 이름, mgr 관리자, job 업무
 2 FROM emp 
 3 WHERE (mgr, job) IN (SELECT mgr, job 

 4                                     FROM emp
 5                                     WHERE ename = '손하늘')
 6 AND ename != '손하늘';

 

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

 

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

 

SQL> SELECT eno 사번, ename 이름, mgr 관리자, job 업무 

 2 FROM emp 
 3 WHERE mgr = (SELECT mgr FROM emp WHERE ename = '손하늘')

 4 AND job = (SELECT job FROM emp WHERE ename = '손하늘')
 5 AND ename != '손하늘'; 

 

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

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

 

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

 

SQL> SELECT eno 사번, ename 이름, dno 부서번호, job 업무 
 2 FROM emp 
 3 WHERE dno in (SELECT dno FROM emp WHERE ename = '김선유')
 4 AND job in (SELECT job FROM emp WHERE ename = '김선유')
 5 AND ename != '김선유'; 

 

사번 이름 부서번호 업무
---- ---------- -------- -----
0801 천유정 02 분석

0301 이승철 02 회계
0302 박선경 02 회계
0702 김민지 02 회계
0269 권나현 10 분석

0401 김진성 10 회계
6 개의 행이 선택되었습니다.

 

SQL> SELECT ename, dno, job 
 2 FROM emp
 3 WHERE ename = '김선유';

 

ENAME DN JOB
---------- -- -------- 

김선유 02 회계
김선유 10 분석

 

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

    SELECT SNO, SNAME, AVR, MAJOR

 2 FROM STUDENT

 3 WHERE AVR IN (SELECT AVR FROM STUDENT WHERE MAJOR = '화학')

 4 AND MAJOR != '화학';

 

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

    SQL> SELECT ENO, ENAME, HDATE
  2  FROM EMP
  3  WHERE HDATE IN (SELECT HIREDATE FROM PROFESSOR WHERE SECTION = '화학');

 

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

SQL> SELECT SNO, SNAME, SYEAR, MAJOR, AVR
  2  FROM STUDENT
  3  WHERE AVR IN (SELECT AVR FROM STUDENT WHERE MAJOR = '화학')
  4  AND SYEAR IN (SELECT SYEAR FROM STUDENT WHERE MAJOR = '화학')
  5  AND MAJOR != '화학';

 

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

SQL> SELECT ENO, ENAME, SAL*12+NVL(COMM,0), DNO
  2  FROM EMP
  3  WHERE SAL*12+NVL(COMM,0) > ALL(SELECT SAL*12+NVL(COMM,0) FROM EMP WHERE DNO = '10')
  4  AND DNO != '10';

 

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

SQL> SELECT ENO, ENAME, JOB, SEX
  2  FROM EMP
  3  WHERE (JOB, SEX) IN (SELECT JOB, SEX FROM EMP WHERE DNO = '10')
  4  AND DNO != '10';

 

집합연산자

*** 같은 데이터 유형이어야한다.

UNION

 - 검색된 결과의 합으로 중복은 제거된다.

 - 정렬을 유발 할수 있다.

UNION ALL

 - 중복을 포함한 결과의 합을 검색한다.

 - 그냥 다 띄움

INTERSECT

 - 양쪽 모두에서 포함된 행을 검색한다.

 MINUS

 - 첫 번째 검색된 결과에서 두 번째 검색 결과를 제외한 나머지를 검색한다.

 

집합 연산
SQL> SELECT ...
 2 [UNION ALL | UNION | INTERSECT | MINUS]
 3 SELECT ... 

 

예제 1. 2000년 이후에 입사한 사원과 부임한 교수의 명단을 검색한다. 

SQL> SELECT pno 번호, pname 이름, hiredate 입사일_부임일 
 2 FROM professor
 3 WHERE hiredate >= '2000/01/01' 

 4 UNION ALL
 5 SELECT eno, ename, hdate
 6 FROM emp
 7 WHERE hdate >= '2000/01/01';

 

번호 이름 입사일_부
-------- ---------- ----------
1001 송강 2002/08/12
1006 장청아 2003/05/20
1007 이초아 2010/07/06
.............
............. 

 

SQL> SELECT pno 번호, pname 이름, hiredate 입사일_부임일 
 2 FROM professor
 3 WHERE hiredate > '2000/01/01'

 4 UNION 
 5 SELECT eno, ename, hdate
 6 FROM emp
 7 WHERE hdate > '2000/01/01'; 

 

번호 이름 입사일_부
-------- ---------- ----------
0269 권나현 2015/05/21
0309 김선유 2011/01/03
0401 김진성 2008/03/13
.............
.............

 

예제 2. 제갈씨 성을 가진 사원 중에 지원 업무를 하지 않는 사원을 검색한다.

 SQL> SELECT eno, ename, job 
 2 FROM emp
 3 WHERE ename LIKE '제갈%' 

 4 MINUS
 5 SELECT eno, ename, job
 6 FROM emp
 7 WHERE job = '지원';

 

ENO ENAME JOB
---- ---------- ----
2002 제갈민 개발

 

예제 3. 화학, 물리학과 학생들 중에 학점이 3.0 이상인 학생을 검색한다.
 SQL> SELECT sno 학번, sname 이름, major 학과, avr 학점 FROM student 

 2 WHERE major IN ('화학','물리')
 3 INTERSECT
 4 SELECT sno, sname, major, avr FROM student 5 WHERE avr >= 3;
학번 이름 학과 학점
-------- ---------- ---------- ----------
905301 유태지 화학 3.28
914502 문수보 물리 3.33
914504 심빈경 물리 3.33
.............
.............

 

실습
1. 화학과 학생과 교수를 검색한다. (집합 연산자를 이용한다.)

    SQL> SELECT SNO, SNAME, MAJOR
2  FROM STUDENT

3  WHERE MAJOR = '화학'
4  UNION
5  SELECT PNO, PNAME, SECTION

6  FROM PROFESSOR
7  WHERE SECTION = '화학';

 

2. 정교수의 명단과 모델링이 업무인 직원의 이름, 입사일(부임일)을 검색한다. - UNION사용

   SELECT PNAME, ORDERS, HIREDATE

2 FROM PROFESSOR

3 WHERE ORDERS = '정교수'

4 UNION

5 SELECT ENAME, JOB, HDATE

6 FROM EMP

7 WHERE JOB = '모델링';