[오라클(Oracle) ] 서브쿼리(SUBQUERY) / 다중행 서브쿼리 / 다중칼럼 서브쿼리 / 상호연관 서브쿼리

[오라클(Oracle) ] 서브쿼리(SUBQUERY) 

 

1. 서브쿼리(SUBQUERY) 

2. 다중행 서브쿼리(IN, ANY/SOME, ALL, EXISTS)

3. 다중칼럼 서브쿼리(PAIRWISE, UNPAIRWISE)

4. 상호연관 서브쿼리

 

 

 

 

 

 

 

1. 서브쿼리(SUBQUERY)

 

 

서브쿼리(SUBQUERY)는 메인쿼리 안에 또 하나의 쿼리를 작성해 처리하는 방법이다. 서브쿼리 SQL문의 결과를 메인쿼리에 전달하여 최종 결과를 출력한다. 메인쿼리 안의 서브쿼리는 괄호로 감싸서 사용해야 한다.

 

 

EX) student 테이블에서 '이민혁' 학생과 동일한 학년의 학생 출력

select name, grade
from student
where grade = (select grade from student
                  where name = '이민혁');

 

 

EX) professor 테이블에서 '강일재' 교수와 동일한 직급의 교수 출력

select name, position
from professor
where position = (select position from professor
                  where name = '강일재');

 

 

EX) student 테이블에서 평균 키보다 작은 학생들 출력

select name, grade, height
from student
where height < (select avg(height)
                from student);

 

 

 

 

 

2. 다중행 서브쿼리(IN, ANY/SOME, ALL, EXISTS)

 

다중행 서브쿼리는 서브쿼리 결과 두 개 이상의 행이 반환될 때 사용되며 in, any/some, all, exists 연산자를 사용한다.

다중행 서브쿼리 연산자 종류

 

 

(1) IN 비교 연산자

 

- 메인쿼리 조건이 서브쿼리 결과 중 하나라도 일치하면 참( "=" 비교만 가능)

 

 

 

EX) 정보미디어학부(부서 번호 100)에 소속된 학생들 출력

select studno, name, deptno
from student
where deptno in (select deptno 
                 from department 
                 where college = 100);

                 
                

 

(2) ANY/SOME 비교 연산자

 

- 메인쿼리 조건이 서브쿼리 결과 중 하나라도 일치하면 참(등호, 부등호 연산 가능)

 

 

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

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

 

 즉, 서브쿼리 결과인 '1학년 학생의 키 값들'의 최솟값보다 큰 학생들이 출력된다.

 

 

(3) ALL 비교 연산자

- 메인쿼리 조건이 서브쿼리 결과와 모두 일치해야 참

 

 

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

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

즉, 서브쿼리 결과인 '1학년 학생의 키 값들'의 최댓값보다 큰 학생들이 출력된다.

 

EX) 3학년 학생 중 몸무게가 가장 가벼운 학생보다 체중이 적은 학생의 이름, 몸무게 출력

select name, weight
from student
where weight < all(select weight 
                   from student 
                   where grade = '3');

즉, 서브쿼리 결과인 '3학년 학생의 체중 값들'의 최솟값보다 가벼운 학생들이 출력된다.

 

 

(4) EXISTS 비교 연산자

- 서브쿼리 검색 결과 행이 하나라도 출력되면 참

 

 

EX) student 테이블에 학년이 '2'학인 학생이 존재하면 모든 학생의 이름, 학년 출력

select name, grade
from student
where exists (select studno 
              from student 
              where grade= '2');

서브쿼리에서 꼭 studno 칼럼으로 조회할 필요는 없지만 null값이 허용되지 않는 칼럼으로 조회하는 것이 좋다.

exists 연산 결과

student 테이블에 2학년 학생이 존재하여 모든 학생이 출력된다.

 

 

 

EX) student 테이블에 학년이 '5'학인 학생이 존재하면 모든 학생의 이름, 학년 출력

select name, grade
from student
where exists (select studno 
              from student 
              where grade= '5');

 

 

3. 다중 칼럼 서브쿼리(PAIRWISE, UNPAIRWISE)

 

다중칼럼 서브쿼리 서브쿼리에서 두 개 이상의 칼럼을 조회하는 것이 특징이다. 이 때 서브쿼리에서 검색하는 칼럼 수와 메인쿼리 조건절의 칼럼 수를 동일하게 해야 한다.

 

 

(1) PAIRWISE

- 비교할 칼럼들을 묶어 동시에 비교

- (칼럼1, 칼럼2.. ) IN (select 칼럼1, 칼럼2 from...) 형식

 

 

EX) student 테이블에서 학년별로 성적이 최대인 학생의 이름, 학년, 성적 출력(PAIRWISE 비교 사용)

select name, grade, score
from student
where (grade,score) in (select grade, max(score) 
                        from student 
                        group by grade); 

 

 

 

EX) professor 테이블에서 부서별로 연봉이 최대인 사원의 이름, 부서번호, 연봉 출력(단, 202 부서는 제외)

select name, deptno, sal
from professor
where (deptno, sal) in (select deptno, max(sal) 
                        from professor
                        where deptno <> 202
                        group by deptno);

 

 

(1) UNPAIRWISE

- 비교할 칼럼들을 분리하여 비교한 후 하나라도 만족하는 행들을 출력

- 

 

 

EX) student 테이블에서 학년별로 성적이 최대인 학생의 이름, 학년, 성적 출력(UNPAIRWISE 비교 사용)

 

select name, grade, score
from student
where grade  in  (select grade
                  from student
                  group by grade)
 
 
AND   score in  (select max(score)
                 from student
                 group by grade);

즉, 학년이 1, 2, 3, 4 중 하나이고 성적이 각 학년의 최댓값에 해당하는 93, 70, 89, 96 중 하나와 같으면 참이 되어 출력되는 것이다. (PAIRWISE 비교에서는 출력되지 않았던 임혁재, 김수미 학생도 출력되었다.)

 

 

 

 

 

 

 

4. 상호연관 서브쿼리

 

상호연관 서브쿼리는 메인쿼리와 서브쿼리가 검색 결과를 교환하는 방식이다. 즉, 메인쿼리의 값을 서브쿼리가 사용하고, 서브쿼리의 값을 메인쿼리가 계산하여 최종 결과를 출력한다.

 

 

 

EX) professor 테이블에서 각 부서의 평균 급여보다 적은 연봉 사원의 사원번호, 이름, 급여 출력

select p1.empno, p1.name, p1.sal
from professor p1
where p1.sal > (select avg(sal) from professor p2
                    where p2.deptno = p1.deptno);

 

 

 

 

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

select s1.name, s1.deptno, s1.height
from student s1
where s1.height > (select avg(height) from student s2
                    where s2.deptno = s1.deptno);