오라클 서브쿼리

9. 서브쿼리

하나의 테이블에서 검색한 결과를 다른 테이블에 전달하여 새로운 결과를 검색하는 경우 사용.(서브쿼리 내에서 ORDER BY절 사용 불가)

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

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

서브쿼리를 이용하여 전은지 교수와 동일한 직급의 교수 이름을 검색하여라

SELECT name, position

FROM professor

WHERE position =(SELECT position FROM professor WHERE name='전은지');

단일행 서브쿼리

서브쿼리에서 하나의 행만 검색하여 메인쿼리에 반환하는 질의문

따라서 메인쿼리의 조건절에서 서브쿼리의 결과와 비교할 경우에는 반드시 단일행 비교연산자(>, =, >=, <, <>, <=)중 하나를 사용해야 함

사용자 아이디가 jun123인 학생과 같은 학년인 학생의 학번, 이름, 학년을 출력하여라.

SELECT studno, name, grade

FROM student

WHERE grade = (SELECT grade FROM student WHERE userid = 'jun123');

20101번 학생과 학년이 같고 키는 20101번 학생보다 큰 학생의 이름, 학년, 키 를 출력하여라

SELECT name, grade, height

FROM student

WHERE grade = (SELECT grade FROM student WHERE studno=20101)

AND height > (SELECT height FROM student WHERE studno=20101);

다중행 서브쿼리

: 서브쿼리에서 반환되는 결과 행이 하나 이상일때 사용하는 서브쿼리.

검색 결과는 다중행 비교 연산자(IN, ANY, SOME, ALL, EXISTS)를 사용하여 메인쿼리 절과 비교 할 수 있다.

IN연산자

: 메인쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인쿼리 조건절이 참이되는 연산자

부서번호 100에 소속된 모든 학생의 학번,이름,학과번호 출력

SELECT name, grade, deptno

FROM student

WHERE deptno IN (SELECT deptno FROM department WHERE college=100);

ANY연산자

:메인쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인쿼리 조건절이 참이되는 연산자(IN연산자는 = 비교만 가능하지만, ANY연산자는 >, < 등과 같은 범위 비교도 가능하다)

모든 학생중 4학년 학생중키가제일 작은 학생보다 키가 큰 학생의 학번 , 이름 , 키 출력

SELECT studno, name, height

FROM student WHERE height > ANY (SELECT height FROM student WHERE grade = '4');

ALL 연산자

: 메인쿼리의 비교 조건에서 서브쿼리의 검색 결과와 모두 일치하면 메인쿼리 조건절이 참이 되는 연산자

모든 학생중 4학년 학생중키가 가장 큰 학생보다 키가 큰 학생의 학번, 이름, 키 출력

SELECT studno, name, height

FROM student

WHERE height > ALL (SELECT height FROM student WHERE grade = '4');

EXISTS 연산자

: 서브쿼리에서 검색된 결과가 하나라도 존재하면 메인쿼리 조건절이 참이 되는 연산자.

NOT EXIST는 서브쿼리에서 검색된 결과가 하나도 존재하지 않으면 메인쿼리 조건절은 참이 된다.

보직수당을 받는 교수가 한명이라도 있으면 모든 교수의 교수번호 이름 급여 보직수당 그리고 급여와 보직수당의 합을 출력.

SELECT profno, name, sal, comm, SAL+NVL(comm,0)

FROM professor

WHERE EXISTS(SELECT profno FROM professor WHERE comm IS NOT NULL);

학생중에서 ‘goodstudent’이라는 사용자 아이디가 없으면 1을 출력

SELECT 1 userid_exist FROM dual WHERE NOT EXISTS (SELECT userid FROM student WHERE userid='goodstudent');

다중 칼럼 서브쿼리

서브쿼리에서 여러 개의 칼럼 값을 검색하여 메인쿼리의 조건절과 비교하는 서브퀄이다. 메인쿼리의 조건절에서도 서브쿼리의 칼럼 수만큼 지정해야 한다.

다중 칼럼 서브쿼리에서 메인쿼리와 서브쿼리의 조건절을 비교하는 방법은 PAIRWISE와 UNPAIRWISW 비교 방법이 있다.

PAIRWISW 비교 방법

메인쿼리와 서브쿼리의 비교 대상 칼럼을 쌍으로 묶어서 행별로 비교(메인 쿼리와 서브쿼리에서 비교하는 칼럼의 수는 반드시 같아야 함)

사용법

SELECT column_list

FROM table1

WHERE(column1, column2,...n개) IN (SELECT column1, column2,...n개 FROM table2 WHERE condition);

PAIRWISE 비교 방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력

SELECT name, grade, weight FROM student WHERE (grade, weight) IN (SELECT grade, MIN(weight) FROM student GROUP BY grade);

UNPAIRWISW 비교 방법

: 메인쿼리와 서브쿼리의 비교 대상칼럼을 분리하여 개별적으로 비교한 후 AND 연산에 의해 최종 결과를 출력하는 방법

각 칼럼이 동시에 만족하지 않더라도 개별적으로 만족하는 경우에는 비교 조건이 참이 되어 출력 결과를 출력 할 수 있다

사용법

SELECT column_list

FROM table1

WHERE column1 IN (SELECT column1 FROM table2 WHERE condition)

AND column2 IN (SELECT column2 FROM table2 WHERE condition)

PAIRWISE 비교 방법에 의해 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력

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);

상호연관 서브쿼리

메인쿼리절과 서브쿼리간에 검색 결과를 교환하는 서브쿼리이다.

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

각 학과 학생의 평균 키보다 키가 큰 학생의 이름, 학과번호, 키 출력

SELECT name, deptno, height

FROM student s1

WHERE height>(SELECT AVG(height) FROM student s2 WHERE s2.deptno = s1.deptno)

ORDER BY deptno;