[Oracle] 서브쿼리

서브쿼리

SQL 명령문의 결과를 다른 SQL 명령문에 전달하여 사용하는것.

 

필요성

※ 서브쿼리 없이 검색

'전은지' 교수와 직급이 동일한 모든 교수를 검색할때

서비쿼리가 없다면 아래의 순서로 검색해야할 것이다.

1. '전은지'의 직급을 검색 => '전은지'의 직급: 전임강사

2. 직급이 '전임강사'인 교수 검색

-- 1. 직급 검색
SELECT position
FROM professor
WHERE name='전은지';

-- 2. '전임강사' 검색
SELECT name, position
FROM professor
WHERE position='전임강사';

 

※ 서브쿼리 이용하여 검색

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

노란색 부분이 서브쿼리이며 초록색 부분이 메인쿼리이다.

예시에서 서비쿼리의 결과로 '전은지'의 직급(position)이 나와 메인쿼리의 조건으로 사용하고있다.

서브쿼리는 메인쿼리가 실행되기전에 실행된다.

 

단일행 서브쿼리

서브쿼리에서 하나의 행만을 검색하여 반환한다.

단일행 비교 연산자 ( =, >, >=, <. <>, <= ) 를 사용해야 함.

 

EX) 20101(studno)번 학생과 학년(grade)이 같고, 키(height)는 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);

 

다중행 서브쿼리

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

메인쿼리의 WHERE절에서 서브쿼리의 결과와 비교할때 다중행 비교 연산자를 사용한다.

다중행 비교 연산자 : IN, ANY, SOME, ALL, EXISTS

 

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

 

IN 연산자 다중행 서브쿼리

메인 쿼리의 비교 조건에서 서브 쿼리의 결과와 하나라도 일치하면 메인 쿼리의 조건절이 참이 된다.

 

EX) 정보미디어학부(부서 번호: 100)에 소속된 모든 학생의 학번, 이름, 학과번호 출력

SELECT studno, name, deptno
FROM student
WHERE deptno IN (SELECT deptno
                 FROM department
                 WHERE college=100);

서브 쿼리의 결과: 101, 102로 다중 행이 나온다.

 

ANY 연산자 다중행 서브쿼리

메인쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인쿼리 조건절이 참이된다.

'=', '>', '<' 범위 비교도 가능하다.

 

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

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

 

ALL 연산자 다중행 서브쿼리

메인 쿼리의 비교 조건에서 서브 쿼리의 검색 결과와 모두 일치하면 메인쿼리 조건절이 참이 된다.

 

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

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

 

" > ANY " 와 " > ALL " 의 결과 차이점

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

 

EXISTS 연산자 다중행 서브쿼리

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

<-> NOT EXISTS : 하나도 존재하지 않으면 참이 되는 연산자

 

EX) 보직수당(comm)을 받는 교수가 한명이라도 있으면 모든 교수의 이름을 출력

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

 

EX) 학생중에서 'goodboy'라는 사용자 아이디가 없으면 1을 출력

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

 

다중 칼럼 서브쿼리

서브쿼리에서 여러개의 칼럼값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리

메인쿼리의 조건절에도 서브쿼리의 칼럼 수만큼 지정해야 한다.

 

종류

PAIRWISE	칼럼을 쌍으로 묶어서 동시에 비교
UNPAIRWISE	칼럼별로 나누어서 비교한 후, AND 연산

출처 : 오라클 중심의 SQL 배움터(교재)

 

PAIRWISE 다중 칼럼 서브쿼리

대상 칼럼을 (column1, column2) 로 쌍으로 묶어서 비교하는 방법. (칼럼수는 동일해야함)

SELECT column_list
FROM table1
WHERE (column1, column2, ...) IN (SELECT column1, column2, ...
                                  FROM table2
                                  WHERE condition);

 

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

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

 

UNPAIRWISE 다중 칼럼 서브쿼리

대상 칼럼을 개별적으로 비교하여 AND 연산함.

동시에 만족하지 않더라도 개별적으로 만족하는 경우 참이 된다.

SELECT column_list
FROM table1
WHERE column1 IN (SELECT column1
                  FROM table2
                  WHERE condition)
AND column2 IN (SELECT column2
                FROM table2
                WHERE condition);

 

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

 

상호연관 서브쿼리

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

 

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

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