SQL-서브쿼리

* 서브 쿼리

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

- FROM절에 쓰인 서브 쿼리는 인라인 뷰라고 부른다.

 

단일 행 서브 쿼리 : 서브 쿼리가 하나의 컬럼에서 하나의 행을 검색

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

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

 

* 단일 행 서브 쿼리

SELECT [DISTINCT | ALL] 컬럼, 컬럼 ...

FROM 테이블

WHERE 컬럼 단일_행_연산자 (SELECT 문장)

 

- 단일 행 연산자가 사용됨으로 반드시 서브 쿼리의 결과 값은 한 개만 검색

- 단일 행 연산자 오른쪽에 기술(=, >, <, >=, <=, !=)

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

 

[예제 1]

SELECT eno 사번, ename 이름

FROM emp

WHERE sal > (SELECT sal

                  FROM emp

                  WHERE ename = '남궁연호');

-> 남궁연호보다 급여를 많이 받는 사원을 검색. 괄호 친 서브 쿼리 문을 먼저 실행한 값으로 SELECT문을 실행한다.

 

[예제 2]

SELECT eno 사번, ename 이름

FROM emp

WHERE sal > (SELECT sal

                  FROM emp

                  WHERE ename = '김선유');

-> 김선유가 두명이 있어서 연산을 하지 못함. 이렇게 값이 여러개 나올 경우를 대비해서 다중 행 서브쿼리 문을 사용.

 

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

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 = '부산');

 

[실습]

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

SELECT sno 학번, sname 이름

FROM student

WHERE avr > (SELECT avr FROM student WHERE sname = '김혁윤'); 

 

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

SELECT sno 학번, sname 이름

FROM student

WHERE syear = (SELECT syear FROM student WHERE sname = '권현')

AND  avr = (SELECT avr FROM student WHERE sname = '강은혜');

 

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

SELECT cno 과목번호, cname 이학수학과_동일한_학점수인_과목

FROM course

WHERE st_num = (SELECT st_num FROM course WHERE cname='이학수학')

AND cname != '이학수학';

 

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

SELECT pno 교수번호, pname 교수이름

FROM professor

WHERE section != (SELECT section FROM professor WHERE pname = '송강')

AND orders = (SELECT orders FROM professor WHERE pname = '송강');

 

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

SELECT pno 교수번호, pname 교수이름

FROM professor

WHERE hiredate > (SELECT hiredate FROM professor WHERE pname = '제갈민');

 

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

SELECT student.sno 학번, student.sname 학생이름, course.cname 과목명, scgrade.grade 학점

FROM student, score, course, scgrade

WHERE grade > (SELECT grade

                       FROM student , score , course , scgrade

                       WHERE student.sno = score.sno AND score.cno= course.cno

                       AND result BETWEEN loscore AND hiscore 

                       AND sname = '강태용' and cname = '일반화학')

AND cname = '일반화학'

AND student.sno = score.sno AND score.cno= course.cno

AND result BETWEEN loscore and hiscore;

 

 

* 다중 행 서브 쿼리

SELECT [DISTINCT | ALL] 컬럼, 컬럼...

FROM 테이블

WHRE 컬럼 다중_행_연산자 (SELECT 문장 : Sub query문)

 

다중 행 연산자(ANY와 ALL에 대한 개념을 확실히 잡을 것)

IN : 검색 값 중에 하나만 일치하면 참

ANY : 검색 값 중에 조건에 부합하는 값이 하나 이상 있으면 참.

ALL : 모든 검색 값과 조건에 맞아야 한다.

 

[예제 1] 20번 부서원들과 동일한 관리자에게 관리 받는 사원을 검색

SELECT eno 사번, ename 이름

FROM emp

WHERE mgr IN (SELECT mgr

                     FROM emp

                     WHERE dno = '20')

AND dno != '20';

-> 서브쿼리문의 mgr이 여러명이 나올 가능성이 있으니 IN을 사용.

 

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

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

FROM emp

WHERE sal < ALL (SELECT sal

                        FROM emp

                        WHERE dno = '10');

-> 10번 부서원의 가장 급여가 낮은 사람보다 낮은 사람을 찾아야 하므로 사실상 모든 사람의 급여보다 낮아야하므로 ALL 사용. 

 

* 다중 행 연산자와 그룹 함수 

- 컬럼 > ALL : 전체 보다 커야하기 때문에 컬럼 > MAX( )

- 컬럼 < ALL : 전체 보다 작아야하기 때문에 컬럼 < MIN( )

- 컬럼 > ANY : 단 한개의 값보다만 크면 되기때문에 컬럼 > MIN( )

- 컬럼 < ANY : 단 한개의 값보다만 작으면 되기때문에 컬럼 < MAX( )

 

* 다중 열 서브 쿼리

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

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

FROM emp

WHERE (mgr, job) IN (SELECT mgr, job

                             FROM emp

                             WHERE ename = '손하늘')

AND ename != '손하늘';

 

[실습]

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

SELECT student.sno 학번, sname 이름, avr 평점

FROM student 

WHERE avr IN (SELECT avr

                    FROM student

                    WHERE major = '화학')

AND major != '화학';

  

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

SELECT eno 사원, ename 이름

FROM emp

WHERE hdate IN (SELECT hiredate

                     FROM professor

                     WHERE section = '화학');

 

3. 화학과 학생과 평점, 학년이 모두 동일한 학생을 검색

SELECT sno, sname, major, syear, avr

FROM student

WHERE (syear, avr) IN (SELECT syear, avr

                              FROM student

                             WHERE major = '화학')

AND major != '화학';

 

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

SELECT eno 사원번호, ename 사원이름 

FROM emp

WHERE sal*12+NVL(comm, 0) > ALL (SELECT sal*12+NVL(comm, 0)

                                               FROM emp

                                               WHERE dno = '10')

AND dno != '10';                                      

 

5. 10번 부서의 사원들과 업무와 성별이 모두 일치하는 사원을 검색

SELECT eno 사원번호, ename 사원이름

FROM emp

WHERE (job, sex) IN (SELECT job, sex

                            FROM emp

                            WHERE dno = '10')

AND dno != '10';