[Oracle] 13. 단일 행 서브쿼리, 다중 행 서브쿼리(IN, ANY, ALL)

서브쿼리(Sub Query)란?

하나의 쿼리에 의해 검색된 결과값이 또 다른 쿼리에 필요한 경우, 두 개의 쿼리문을 합해서 만들어지는 쿼리. 메인 쿼리문 내에 서브 쿼리문이 포함된다. 서브 쿼리문은 조건절인 WHERE절, HAVING절에 주로 쓰이며, FROM절에 쓰이는 경우에는 '인라인 뷰(Inline View)', SELECT절에 쓰이는 경우 '스칼라 서브 쿼리(Scala Sub Query)'라고 부른다. 서브 쿼리문을 괄호()로 반드시 감싸야 한다!

   SELECT [DISTINCT | ALL] 컬럼명, 컬럼명... 
   FROM 테이블 
   WHERE 컬럼명 연산자 (Sub Query문)

단일 행 서브쿼리

서브쿼리 문장에서 단 하나의 행과 열을 검색하는 경우, 단일 행 연산자( <=, <, >, <=, >=, !=)를 사용한다.

// 인원수가 가장 많은 학과를 검색하세요
SELECT major 전공, COUNT(sno)재적 
FROM student
GROUP BY major 
HAVING COUNT(sno)=(SELECT MAX(COUNT(sno)) 
                   FROM student 
                   GROUP BY major);

// 관우보다 평점이 우수한 학생의 학번과 이름을 검색하세요
SELECT sno, sname 
FROM student 
WHERE avr > (SELECT avr 
             FROM student 
             WHERE sname='관우');

다중 행 서브쿼리

서브쿼리 문장에서 여러개의 행을 검색하는 경우, 다중 행 연산자(IN, ANY, ALL)를 사용한다.

다중 행 연산자 의미

IN    : 검색된 값 중에 하나만 일치하면 TRUE
ANY : 검색된 조건(특정한 값이 아닌 범위)에 맞는 것이 1개 이상이면 TRUE 
ALL  : 모든 검색된 값과 조건에 맞아야 TRUE 

다중 행 연산자와 그룹함수 값 비교

컬럼 > ALL → 컬럼 > MAX : 가장 큰 값보다 크다
컬럼 < ALL → 컬럼 < MIN : 가장 작은 값보다 작다
컬럼 > ANY → 컬럼 < MIN : 가장 작은 값보다 크다
컬럼 < ANY → 컬럼 < MAX : 가장 큰 값보다 작다

// 화학과 학생과 같은 학년에서 평점이 동일한 학생들을 검색하세요
SELECT sno, sname, major, avr FROM student
WHERE (syear, avr) IN (SELECT syear, avr
                       FROM student
                       WHERE major = '화학') AND major != '화학';

// 01번 부서원들보다 급여가 낮은 사원을 검색하세요
SELECT eno, ename, dno, sal FROM emp
WHERE sal < ALL(SELECT sal
                FROM emp
                WHERE dno='01') AND dno!='01'
ORDER BY sal;