17. 서브쿼리(1)

1. 서브쿼리의 정의와 종류

1). 정의 : 쿼리(메인쿼리) 안에 있는 쿼리(서브쿼리)
2). 종류
(1) 위치에 따라(실무적 의미)
   select col1, (select ... from ...)  -- 스칼라 서브쿼리(하나의 컬럼처럼 출력을 원할 때 사용)
     from tab1, (select ... from ...)  -- 인라인뷰(하나의 테이블처럼 사용하고 싶을 때 사용)
    where col1 = (select ... from ...) -- (일반) 서브쿼리(상수를 대체하는 표현을 하고 싶을 때 사용)
     
(2) 문법에 따라(개념적 의미)
- 단일행 서브쿼리
- 다중행 서브쿼리
- 다중컬럼 서브쿼리
- (상호)연관 서브쿼리

 

 

 

 

2. 단일행 서브쿼리

- 서브쿼리 결과가 단 하나의 행을 리턴하는 경우(하나의 컬럼만 출력)
- =, >, < 등의 비교 연산 시 반드시 서브쿼리는 단일행 서브쿼리 형태여야 함

예제) emp에서 전 직원의 평균급여보다 높은 급여를 받는 직원의 이름, 급여 출력
step1) 평균급여
select avg(sal)   -- 2073.21 
  from emp;

step2) 대상 출력
select *
  from emp
 where sal > 2073.21;
 

서브쿼리를 활용하여 변경) 상수의 변수화 
select *
  from emp
 where sal > (select avg(sal)
                from emp);

예제) emp에서 가장 늦게 입사한 사람의 이름, 입사일, 급여 출력

select max(hiredate)
  from emp;
  

select *
  from emp
 where hiredate = (select max(hiredate)
                     from emp);
 

 

3. 다중행 서브쿼리
- 서브쿼리 결과가 둘 이상의 행을 리턴하는 경우(하나의 컬럼만 출력)
- =, >, < 등의 비교 연산 불가 -> 대체 연산자로 변경하거나 단일행 서브쿼리로 변경

예제) emp에서 이름이 J로 시작하는 직원과 같은 부서에 있는 직원 모두 출력(J로 시작하는 직원 포함)
select deptno 
  from emp
 where ename like 'J%';

select * 
  from emp
 where deptno = (select deptno 
                   from emp
                  where ename like 'J%');  -- error
                                           -- 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.
                                           
다중행 서브쿼리 연산자로 변경) = -> in 변경                                           
select * 
  from emp
 where deptno in (select deptno 
                    from emp
                   where ename like 'J%');


예제) emp에서 이름이 J로 시작하는 직원의 급여보다 높은 급여를 받는 직원 정보 출력
select sal
  from emp
 where ename like 'J%';

 

select *
  from emp
 where sal > (select sal
                from emp
               where ename like 'J%');        --error

해결1) 단일행 서브쿼리 형태로 변경
select *
  from emp
 where sal > (select max(sal)
                from emp
               where ename like 'J%');
 

              
해결2) 대소비교 다중행 서브쿼리 연산자(any, all)로 변경
select *
  from emp
 where sal > all(select sal
                   from emp
                  where ename like 'J%');    

 

** any, all 연산사
 대소비교와 여러 상수를 동시 비교하도록 하는 연산자

  > all(100,200)  =>   > 200 으로 해석(집합중 최대 리턴)
  < all(100,200)  =>   < 100 으로 해석(집합중 최소 리턴)

  > any(100,200)  =>   > 100 으로 해석(집합중 최소 리턴)
  < any(100,200)  =>   < 200 으로 해석(집합중 최대 리턴)

 

 

[ 연습 문제 ]
1. student 테이블에서 여학생 중 키가 가장 큰 학생의 이름, 학년, 키 출력

sol)
select * 
  from student
 where jumin like '______2%'
   and height = (select max(height)
                   from student
                  where jumin like '______2%');
             

 

     
2. emp2 테이블에서 1005번 부서원들의 취미와 같은 취미를 갖는 직원의 이름, 취미, 부서번호 출력

sol)
select hobby
  from emp2
 where deptno = 1005;

select name, hobby, deptno
  from emp2
 where hobby in (select hobby
                   from emp2
                  where deptno = 1005);

 

4. 다중 컬럼 서브쿼리 
- 비교할 컬럼이 두 개 이상
- 서브쿼리 출력 결과가 두 개 이상의 컬럼을 갖는 경우
- 대소비교 불가

예제) emp에서 전체 직원 중 최대 급여를 받는 직원의 이름, 부서번호, 급여 출력(단일행 서브쿼리)

sol)
select * 
  from emp
 where sal = (select max(sal)
                from emp);

예제) emp에서 각 부서별로 최대 급여를 받는 직원의 이름, 부서번호, 급여 출력(다중컬럼 서브쿼리)

select * from emp order by deptno, sal desc;

 

update emp 
   set sal = 2850
 where empno = 7782; 

 

입력 후 다시 비교해보자.
 

-바른 쿼리) 부서와 급여를 동시에 비교 
select ename, deptno, sal
  from emp
 where (deptno, sal) in (select deptno, max(sal)
                           from emp
                          group by deptno);

 

잘못된 쿼리) 부서 상관없이 급여만 비교(각 부서별 최대급여자가 아닌 직원도 출력됨)
select ename, deptno, sal
  from emp
 where sal in (select max(sal)
                 from emp
                group by deptno);

예제) student 테이블을 사용하여 학년별로 가장 큰 키를 갖는 학생의 이름, 학년, 키 출력

sol)

select grade, max(height)
  from student
 group by grade;

 

select *
  from student
 where (grade,height) in (select grade, max(height)
                            from student
                           group by grade);

 

예제) student, exam_01 테이블을 사용하여 각 학년별로 최고성적자의 이름, 학년, 성적 출력
select s.grade, max(e.total)
  from student s, exam_01 e
 where s.studno = e.studno
 group by s.grade;

select s.name, s.grade, e.total, d.dname
  from student s, exam_01 e, department d
 where s.studno = e.studno
   and s.deptno1 = d.deptno
   and (s.grade, e.TOTAL) in (select s.grade, max(e.total)
                                from student s, exam_01 e
                               where s.studno = e.studno
                               group by s.grade);

 

 

5.  상호연관
- 다중컬럼 서브쿼리에서 대소비교가 안되는 점 해결 가능
- 동시에 두 컬럼의 값을 비교하지 X
- 먼저 확인하거나 고정되어야 할 정보(메인쿼리와 서브쿼리간의 연결 조건)를 서브쿼리에 전달

예제) 부서별 평균급여보다 높은 급여를 받는 직원의 이름, 부서번호, 급여 출력
select deptno, avg(sal)
  from emp
 group by deptno;
 
select *
  from emp
 where (deptno, sal) > (select deptno, avg(sal)
                          from emp
                         group by deptno);       -- 불가
 

 

                     
select *
  from emp e1
 where sal > (select avg(sal)
                from emp e2
               where e1.deptno = e2.deptno);

[ 연습문제 ]
1. emp2에서 각 EMP_TYPE별 생년월일이 가장 빠른 직원의 이름, EMP_TYPE, 생년월일 출력

sol)
1) 다중컬럼
select EMP_TYPE, min(BIRTHDAY)
  from emp2
 group by EMP_TYPE;
 
select *
  from emp2
 where (emp_type, birthday) in (select emp_type, min(birthday)
                                  from emp2
                                 group by emp_type) ;

 

2) 상호연관
select *
  from emp2 e1
 where birthday = (select min(birthday) as min_birth
                      from emp2 e2
                     where e1.EMP_TYPE = e2.EMP_TYPE) ;  
  

1)번과 결과는 동일하다.
               
       

2. professor 테이블을 사용하여 각 POSITION로 평균급여보다 낮은 급여를 받는 교수의
  이름, 직급, 급여, 소속부서명 출력

sol)
select position, avg(pay)
  from professor
 group by position;
 
select p1.name, p1.position, p1.pay, d.dname
  from professor p1, department d
 where p1.pay < (select avg(pay)
                   from professor p2
                  where p2.position = p1.position)
   and p1.deptno = d.deptno;