서브쿼리 (By Oracle)

□ 하나의 SQL 명령문의 결과를 다른 SQL 명령문에 전달하기 위해 두개 이상의 SQL 명령문을 하나의 SQL명령문으로 연결처리하는 방법

 

□ 단일행 서브쿼리

  ○ 서브쿼리에서 단 하나의 행만을 검색하여 메인쿼리에 반환하는 질의문
  ○ 메인쿼리의 WHERE 절에서 서브쿼리의 결과와 비교할 경우에는 반드시 단일행 비교 연산자 중 하나만 사용해야함
   - 단일 행 비교 연산자 : =, >, >=, <, <>, <=

select studno, name, grade from student
where grade = (select grade from student where userid ='jun123');

select name, deptno, weight from student
where weight < (select avg(weight) from student where deptno = 101)
order by deptno;

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, SOM, ALL, EXISTS
    - 다중 행 비교 연산자는 단일 행 비교 연산자와 결합하여 사용가능

  ○ IN 

    - 메인쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인쿼리 조건절이 참이 되는 연산자
    - ‘=‘ 연산자를 OR로 연결한 것과 같은 의미

select name, grade, deptno from student
where deptno in (select deptno from department where college in (100,102));

  ○ ANY

    - 메인쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인쿼리 조건절이 참이 되는 연산자
    - ‘>,<‘ 등과 같은 범위 비교도 가능

select studno, name, height from student
where height > any (select height from student where grade = '4');

  ○ ALL

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

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

select studno, name, height from student
where height > all(select height from student where grade = '4');

  ○ EXISTS     

    - 서브쿼리에서 검색된 결과가 하나라도 존재하면 메인쿼리 조건절이 참이 되는 연산자
    - 서브쿼리에서 검색된 결과가 존재하지 않으면 메인쿼리의 조건절은 거짓
     → ‘선택된 레코드가 없습니다’ 라는 메시지 출력
    - NOT EXISTS
     → EXISTS 와 상반되는 연산자
     → 서브쿼리에서 검색된 결과가 하나도 존재하지 않으면 메인쿼리의 조건절이 참이 되는 연산자

select 1 userid_exist
from dual
where not exists (
    select userid from student where userid = 'goodstudent'
)

 

□ 다중컬럼 서브쿼리 : PAIRWISE, UNPAIRWISE 서브쿼리 비교

  ○ PAIRWISE

    - 메인쿼리와 서브쿼리의 비교 대상 칼럼을 쌍으로 묶어서 행별로 비교하는 방법
    - 메인쿼리와 서브쿼리에서 비교하는 칼럼의 수는 반드시 동일해야 함

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

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

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;

 

□ 서브쿼리 사용시 주의 사항

  ○ 단일행 서브쿼리에서 오류가 발생하는 경우

    - 복수 행 값을 반환하는 서브쿼리와 단일행 비교연산자 함께 사용하는 경우
    - 반환되는 칼럼의 수와 메인쿼리에서 비교되는 칼럼 수가 일치하지 않는 경우
    - 복수행을 출력하는 서브쿼리와 ‘=‘단일행 연산자로 비교하는 경우

  ○ 메인쿼리와 서브쿼리 칼럼의 수가 일치하지 않는 경우

  ○ Order By 절에 사용할 때

  ○ 서브쿼리의 결과가 Null인 경우