메인쿼리-서브쿼리(Main Query - Sub Query)

Main Query - Sub Query

? 사원테이블에서 최대월급을 받는 사람과 그 월급을 출력하려면..

select ename, max(sal)
 from emp
 where sal=max(sal);

이러면 되는 줄 알았건만.. ㅡ,.ㅡ;;
'그룹 함수는 허가되지 않습니다' 이건 뭐여~~

서브쿼리를 사용하자!
select ename, sal
 from emp
 where sal=(select max(sal) from emp);

ENAME             SAL
---------- ----------
KING             5000

where 절에 있는 (select max(sal) from emp) 이 서브쿼리다.

? 여기서, JONES의 월급보다 많이 받는 사원들의 이름, 월급을 출력하려면..
select ename, sal
 from emp
 where sal > (select sal from emp where ename='JONES');

ENAME    SAL
--------------
SCOTT    3000
KING       5000
FORD      3000

? SCOTT와 직업이 같은 사원과 직업을 출력하려면...
select ename, job
 from emp
 where job = (select job from emp where ename='SCOTT');

ENAME             JOB     
---------- ---------
SCOTT      ANALYST 
FORD        ANALYST

? SCOTT을 빼려면..
select ename, job
 from emp
 where job = (select job from emp where ename='SCOTT')
              and ename <> 'SCOTT';

? DOLLAS 에서 근무하는 사원들의 이름과 월급을 출력하려면..
 select e.ename, e.sal
  from emp e, dept d
   where e.deptno=d.deptno and d.loc='DALLAS';
ENAME             SAL
---------- ----------
SMITH             800
JONES            2975
SCOTT            3000
ADAMS            1100
FORD              3000

? 직업이 SALESMAN 인 사원들의 월급과 같은 사원의 이름과 월급을 출력하려면...

select ename, sal
    from emp
    where sal = ( select sal from emp where job = 'SALESMAN');

이러면 되는줄 알았어~  '단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.' 이건.. ㅡㅡ;
여기선 in 이라는 걸 쓰면 되더라고..

select ename, sal
    from emp
    where sal in ( select sal from emp where job = 'SALESMAN');

서브쿼리에서 리턴 되는 값이 여러개 일때 이를 비교하려면 in을 쓰면돼..
서브쿼리에서 사용하는 연산자가 몇개 더 있는데..
서브쿼리는 리턴되는 수의 따라서 single row subquery 와 multiple row query 로 나뉘어.
각각의 서브쿼리에 맞게 연산자를 써줘야하는데 위에서 봤듯이 리턴되는 값이 2 이상이면 in 을 쓴 것처럼..

1. single row subquery
연산자 : = , > , >= , < , <= , <>

2. multiple row subquery
연산자 : >all , <all , >any , <any , in , not in

이렇게 각각 쓰인다.

>all 은 서브쿼리의 결과값 모두보다 큰것을 말한다.
예를 들어, 직업이 SALESMAN 인 사원들의 월급보다 큰 월급을 받는 사원들의 이름과 월급을 출력하려면..

select ename, sal
    from emp
    where sal > (select max(sal) from emp where job='SALESMAN');

이렇게 하면 된다. >all 을 사용해도 결과는 동일하다.

select ename, sal
    from emp
    where sal >all (select sal from emp where job='SALESMAN');

SALESMAN 인 사원들의 월급을 서브쿼리에서 가져오는데 이것들 모두 보다 큰것
SALESMAN 의 월급이 1250, 1500, 1600  인데 이것들 모두 보다 큰것은 월급중에서 가장큰 1600 보다 크면 된다.
그래서 max 함수와 동일한 결과를 준것이다.

그럼 반대로, min 함수는..
any 로 쓰면 된다.

최소값보다 큰 결과를 원한다면.. >any 를 쓰면 된다.
결과중에서 all 은 모든것이지만 any 는 어떤 결과보다도 크기만 하면된다. 결과값중 아무거 하나보다
크면 되니까..
select ename, sal
    from emp
    where sal > (select min(sal) from emp where job='SALESMAN');

select ename, sal
    from emp
    where sal >any (select sal from emp where job='SALESMAN');

in은 =any 와 같다.
=any 는 =(7369  or 7499 or ... or null) 처럼 되어있다.

이를 살펴보려면..

? 관리자인 사원들의 이름은..

select ename
      from emp
      where empno in ( select mgr from emp);

ENAME    
----------
JONES    
BLAKE    
CLARK    
SCOTT    
KING     
FORD

그럼 반대로, 관리자가 아닌 사원들의 이름은?

select ename
      from emp
      where empno not in ( select mgr from emp);
사원테이블에는 14명이 있고, 관리자인 사원들이 6명이니까 나머지 8명이 나올 것을 기대했는데..
'no rows selected' 라니... ㅡ,.ㅡ;

not in 은 !=all 과 같다.
!=all 은 !=(7369  and 7499 and .... null) 처럼 되어있기 때문이다.

서브쿼리의 mgr 중에 null 이 존재하기 때문에 !=all 에서처럼 True and null 은 결과값이 null 이 나온다.
따라서 원하는 결과(관리자가 아닌 사원들의 이름)를 출력하려면 다음과 같이 바꿔줘야 한다.
(이것이, not in 을 쓸 때의 주의사항이다!!!)

1.
 select ename
   from emp
   where empno not in ( select nvl(mgr,0) from emp);
2.
 select ename
   from emp
   where empno not in ( select mgr from emp where mgr is not null);

* rownum 사용
다음과 같이 해보자.
select rownum, empno, ename, sal
 from emp;

ROWNUM EMPNO ENAME     SAL
---------------------------------
1             7369      SMITH      800
2             7499      ALLEN    1600
3             7521      WARD     1250
.......
13           7902      FORD      3000
14           7934      MILLER   1300

결과값 앞에 행 수가 출력된다.
이것을 이용하면..

? 3행까지만  출력하고 싶다.

select rownum, empno, ename, sal
 from emp
 where rownum<4;

ROWNUM EMPNO ENAME     SAL
---------------------------------
1             7369      SMITH      800
2             7499      ALLEN    1600
3             7521      WARD     1250

? 월급이 높은 순서로 3개까지만 출력하고 싶다면..

select rownum, empno, ename, sal
  from emp where rownum<4
  order by sal desc;
이렇게 했더니만 결과값은..

ROWNUM EMPNO ENAME   SAL
---------------------------------
2             7499     ALLEN    1600
3             7521     WARD     1250
1             7369     SMITH     800

위의 3개 가져온 것에서 정렬을 하는군.. 음..
우뜩할까..
서브쿼리를 이용하자.. 먼저 월급으로 정렬된 테이블을 만들고 거기서 3개만 가져오면 되니까..

select rownum, empno, ename, sal
  from (select * from emp order by sal desc)
  where rownum<4;

ROWNUM EMPNO ENAME   SAL
--------------------------------
1            7839      KING       5000
2            7788      SCOTT    3000
3            7902      FORD      3000
원하는 결과값을 얻을 수 있다.
그래서, 서브쿼리를 쓸수 있는 절은..

*** subquery 를 쓸수 있는 절 ***
1. select
2. where
3. from
4. having
5. order by

이렇다.. 휴~