오라클 서브쿼리 SUBQUERY

Sub Query

하나의 sql 명령문의 처리 결과를 다른 sql 명령문에 전달하기 위하여 두 개 이상의 sql 명령문을 하나의 sql 명령문으로 연결한다.

서브 쿼리를 포함하는 명령문을 메인쿼리( mian query)라고 하며 서브 쿼리는 select 명령문의 시작과 끝에 괄호를 묶어서 메인쿼리와 구분한다.

서브쿼리는 메인쿼리의 select 명령문과 DML에서 사용 할 수 있다.

SELECT 문에서는 SELECT 절, FROM 절, WHERE 절, HAVING절에서 사용 할 수 있다.

서브쿼리의 종류

단일행 서브쿼리(single row), 다중행(multi row) 서브쿼리, 다중 칼럼(multi column) 서브쿼리, 상호연관(corelated) 서브쿼리가 있다.

1) 단일행 서브쿼리

하나의 행만 검색하여 메인쿼리에 반환하는 쿼리이다. 그래서 메인쿼리의 조건절에서 서브쿼리 결과와 비교할 경우에는 반드시 단일행 비교 연산자(>,<,=,>=,<=,<>)중 하나를 사용해야 한다.

예) 아이디가 '12345'인 학생과 같은 학년인 학생의 학번, 이름, 학년을 출력하라

1. SELECT studno, name, grade
2. FROM student
3. WHERE grade = (select grade
4. from student
5. where userid=12345);

2) 다중행 서브쿼리

다중해 비교 연산자(IN, ANY, SOME, ALL, EXISTS)를 사용해 메인쿼리 절과 비교할 수 있고, 단일행 비교 연산자와 결합하여 사용할 수 있다.

종류	의미
IN	서브쿼리의 결과 값이 메인쿼리의 결과와 하나라도 일치하면 참, '=' 비교만 가능
ANY, SOME	서브쿼리의 결과 값과 메인쿼리와 하나 이상 일치하면 참, '='과 '<','>' 범위 비교도 가능
ALL	서브쿼리의 결과 중에서 모든 값이 일치하면 참
EXISTS	서브쿼리의 결과가 메인쿼리의 비교 조건에 존재하면 참

(1) IN 연산자

'='연산자를 OR로 연결한 것과 같은 의미이다.

(2) ANY 연산자

> ALL: 서브쿼리 결과 값중 최대 값 보다 크면 메인쿼리의 비교 조건이 참

> ANY: 서브쿼리 결과 중 최소 값 보다 크면 메인쿼리의 비교 조건이 참

> ANY	> ALL
학생중 4학년 학생중에서 키가 작은 학생 보다 큰 학생들의 집합	4학년 학생들 중에서 키가 가장 큰 학생 보다 큰 학생들의 집합
SELECT studno, name, height FROM student WHERE height > any ( select height from student where grade = 4 );	SELECT studno, name, height FROM student WHERE height > ALL ( select height from student where grade = 4 );
SELECT studno, name, height FROM student WHERE height > ( select min(height) from student where grade = 4 );	SELECT studno, name, height FROM student WHERE height > ( select max(height) from student where grade = 4 );

(3) ALL 연산자

(4) EXISTS 연산자

1. SELECT profno, name, sal, comm, sal+nvl(comm,0) S
   FROM professor
   WHERE EXISTS (
   SELECT profno FROM professor
   WHERE comm IS NOT NULL );

3) 다중 칼럼 서브쿼리

다중 칼럼 서브쿼리는 서브쿼리의 여러 컬럼 결과 값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리이다.

(1) PAIRWISE 비교

칼럼을 쌍으로 묶어서 행별로 비교하는 방식. 메인쿼리와 서브쿼리에서 비교하는 칼럼의 수는 반드시 같아야 한다.

1. 사용법
2. SELECT column_list
3. FROM table1
4. WHERE (column1,column2,...) IN (SELECT column1, column2,...
5. FROM table2 WHERE conditions);

1. -- PAIRWISE에 의해, 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력하라.
   select name, grade, weight
   from student
   where (grade,weight) IN (
   select grade,min(weight)
   from student GROUP BY grade );

(2) UPPAIRWISE 비교

칼럼 별로 나누어 비교한 후 AND 연산을 하는 방식, 각 칼럼이 동시에 만족하지 않아도 개별적으로 만족하는 경우에 비교 조건이 참이 되어 결과를 출력.

1. -- UNPAIRWISE에 의해, 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력하라.
   select name, grade, weight
   from student
   where grade IN (
   select grade
   from student GROUP BY grade )
   AND weight IN (
   SELECT MIN(weight)
   FROM student
   GROUP BY grade);

4) 상호연관 서브쿼리

메인쿼리와 서브쿼리의 결과를 교환하기 위하여 서브쿼리의 WHERE 조건절에서 메인쿼리의 테이블과 연결한다. 하지만 행을 비교할 때마다 결과를 메인으로 반환하는 관계로 처리 성능이 저할 될 수 있다.

1. -- 각 학과 학생의 평균 키보다 키가 큰 학생의 이름, 학과번호, 키를 출력하라.
2. SELECT name, deptno, height
3. FROM student s1
4. WHERE height > ( SELECT AVG(height)

5. FROM student s2

6. WHERE s2.deptno = s1.deptno)

7. ORDER BY deptno;

5) 서브쿼리 이용시 잦은 오류

(1) 다중행 출력 서브쿼리와 '='같은 단일행 연산자로 비교하는 오류

(2) 다중 칼럼 비교시 메인쿼리와 서브쿼리의 칼럼의 수가 일치 않는 오류

(3) 서브쿼리에 ORDER BY 절을 사용

(4) 서브쿼리의 결과가 NULL인 경우, 메인쿼리의 WHERE 조건절 결과는 '선택된 레코드가 없음'