쿼리 안에 쿼리가 들어가 있는 형태를 서브 쿼리라 한다.

지금 까지 배운 쿼리로는 해결할 수 없기 때문에 복합적인 쿼리형태를 사용한다.(메인쿼리+서브쿼리)

서브쿼리는 괄호를 사용한다.

단일행 서브 쿼리

• 서브쿼리 SELECT 문에서 얻은 한 개 행의 결괏값을 메인 쿼리로 전달한다.

  select * from emp;
    
  --JONES 보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하시오
  
  select * from emp;
  select ename,sal
  from emp
  where sal > (select sal
                  from emp
                  where ename='JONES');
  

  --scott과 같은 월급을 받는 사원들의 이름과 월급을 출력하시오.
  select ename,sal
  from emp
  where sal = (select sal
                  from emp
                  where ename='SCOTT')
  AND ename !='SCOTT';
  
  

  #HR
  SELECT *
  FROM employees A
  WHERE A.salary = (
                    SELECT salary
                    FROM employees
                    WHERE last_name = 'De Haan'
                    );
  

다중행 서브 쿼리

서브 쿼리로 부터 얻은 결괏값이 여러개 일때 사용한다.

IN

리스트 값과 동일하다는 의미 이다.

다중행 서브 쿼리

SELECT ename, sal
  FROM emp
  WHERE sal in (SELECT sal
                       FROM emp
                       WHERE job='SALESMAN');

다중열 서브 쿼리

SELECT *
FROM employees A 
 WHERE  (A.job_id ,A.salary )IN (SELECT job_id, MIN(salary) 최저급여
                     FROM employees
                     GROUP BY job_id
                     )
 order by A.salary DESC;     

NOT IN

리스트 값과 동일 하지 않다는 의미이다.

--NOT IN
  SELECT ename, sal, job,mgr
  FROM emp
  WHERE empno not in (SELECT mgr
                                  FROM emp
                                  WHERE mgr is not null);

  select * from emp;

EXISTS 와 NOT EXISTS

특정 테이블의 데이터가 다른 테이블에도 존재하는지 여부를 확인하는 방법이다

EXISTS

select * 
    from dept d
    where EXISTS (SELECT * 
                    FROM emp e
                    WHERE e.deptno=d.deptno);

NOT EXISTS

SELECT *
  FROM dept d
  WHERE NOT EXISTS (SELECT *
                      FROM emp e
                      WHERE e.deptno = d.deptno);

4)기타

>all: 리스트의 값보다 크다.
>any:리스트의 가장 작은 값보다 크다.
<all:리스트의 가장 작은 값보다 작다
<any: 리스트의 가장 큰 값보다 작다.

서브 쿼리 위치에 따른 서브쿼리 종류

사용 위치에 따라 서브 쿼리를 분류한다.

1)select 절에 있는 서브쿼리 - 스칼라 서브쿼리

• 서브쿼리가 SELECT 절로 확장 되었다고 해서 스칼라 서브 쿼리로 불린다.
• scalar는 한번에 한가지만 처리한다는 의미 이고,스칼라 서브 쿼리에서 나온 결과는 하나의 행이어야 한다.
• 스칼라 서브 쿼리는 출력되는 행수 만큼 반복되어 실행된다.

2)from 절에 있는 서브쿼리 - 인라인뷰

3)where 절에 있는 서브쿼리 - 서브쿼리

• where 절에 있는 서브쿼리는 당연히 메인쿼리가 실행되기전에 수행되지만 ,스칼라 서브쿼리는 다르게 동작한다.

4)Where절의 결과값에 따른 분류

• 단일행 서브 쿼리 : 서브 쿼리에서 메인 쿼리로 하나의 값이 반환됨
• 다중행 서브 쿼리 : 서브 쿼리에서 메인 쿼리로 여러개의 값이 반환된다.
• 다중 컬럼 서브 쿼리 : 서브 쿼리에서 메인 쿼리로 여러 개의 컬럼 값이 반환된다.

from 절에 있는 서브쿼리 - 인라인뷰

--이름과 월급을 출력하는데 순위가 1위 인 사람만 출력하시오.
 SELECT v.ename, v.sal, v.순위
 FROM ( SELECT ename, sal, rank() over (order by sal desc) 순위
               FROM emp) v
  WHERE v.순위 =1;

#HR
select * 
        from employees A,
        --     ( 서브 쿼리:인라인 뷰)B
        (select department_id
          from departments
          where department_name ='IT') B
  WHERE A.department_id = B.department_id ;

HAVING절의 서브 쿼리

그룹 함수는 안된다.

--직업과 직업별 토탈 월급을 출력하는데 , 직업이 SALESMAN인 사람들의 토탈 월급보다 
   --더 큰 값들만 출력하시오.
   
   select job,sum(sal)
        from emp
        where sum(sal) > (select sum(sal) from emp 
                           where job ='SALSMAN') --직업이 SALESMAN인 사람들  더 큰 값
        group by job;  --ORA-00934: group function is not allowed here

ORA-00934: group function is not allowed here

SELECT job, sum(sal)
  FROM emp
  GROUP BY job
  HAVING sum(sal) > (SELECT sum(sal)
                       FROM emp
                       WHERE job='SALESMAN');

8.select 절에 있는 서브쿼리 - 스칼라 서브쿼리

서브 쿼리 캐싱을 이용하여 수행된다.

--select 절에 있는 서브쿼리 - 스칼라 서브쿼리
 --직업이 SALESMAN인 사원들의 이름과 월급을 출력하는데,SALESMAN인 사원들의 
 --최대 월급과 최소 월급도 같이 출력하시오.

select  ename,sal,max(sal) 최대월급,
                  min(sal) 최소월급
 from emp
 where job='SALESMAN';
 --ORA-00937: not a single-group group function

 select  ename,sal,(select  max(sal) from emp where job='SALESMAN') 최대월급,
                   (select  min(sal) from emp where job='SALESMAN') 최소월급
 from emp
 where job='SALESMAN';

select max(sal) from emp where job='SALESMAN' 절을 레코드 마다 계산 하면 데이터베이스 효율이 떨어진다. 첫번째 행은 계산하지만 두번째 행부터는 계산 하지 않고 캐싱을 한다.(서브쿼리 캐싱)