[Oracle] SUBQUERY

조인: 여러개의 테이블에서 필요한 데이터 모아서 수집

        =>단점: 여러 개의 
서브쿼리: 여러개의 SQL문장을 한개로 통합  
             DML 전체에서 사용이 가능  
   

     = 종류  
          1) 단일행 서브쿼리 : 서브쿼리의 결과값이 1개 
          2) 다중행 서브쿼리 : 서브쿼리의 결과가 여러 개
          3) 인라인 뷰 : FROM (SELECT~) 
          4) 스칼라 서브쿼리 : 컬럼 대신 사용  
             SELECT empno,ename,(SELECT ~ ) as dname 

-- SCOTT => scott가 근무하는 부서에 같이 근무하는 사원의 모든 정보 출력
1)SELECT deptno FROM emp
WHERE ename='SCOTT';
 
2)SELECT * FROM emp
WHERE deptno=20;
 
1+2=> SELECT * FROM emp
WHERE deptno=(SELECT deptno FROM emp WHERE ename='SCOTT');
/*
    단일행 서브쿼리의 연산자 
    => 비교연산자 
       = , !=(<>) , <= , >= , < , >
*/
SELECT * FROM emp
WHERE deptno=(SELECT deptno FROM emp); --불가능한 문장: 하나의 값만 반환 가능
 
SELECT DISTINCT deptno FROM emp;
 
-- GROUP BY 
-- 사원의 평균 급여 보다 높은 부서의 부서번호,인원수를 출력
SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno
HAVING AVG(sal)<(SELECT AVG(sal) FROM emp);
 
-- 스칼라 서브쿼리 => 결과값이 한개(컬럼)
-- SELECT empno,(SELECT~),(SELECT~) FROM emp
SELECT * FROM emp;
SELECT * FROM dept;
 
SELECT empno,ename,job,dname,loc 
FROM emp,dept
WHERE emp.deptno=dept.deptno;
 
-- 서브쿼리 
SELECT empno,ename,job,(SELECT dname FROM dept d WHERE d.deptno=e.deptno) as dname,
       (SELECT loc FROM dept d WHERE d.deptno=e.deptno) as loc
FROM emp e;
 
SELECT empno,ename,job,sal,grade
FROM emp,salgrade
WHERE sal BETWEEN losal AND hisal;
 
SELECT empno,ename,job,sal,(SELECT grade FROM salgrade WHERE sal BETWEEN losal AND hisal) as grade
FROM emp;
 
-- 사원 정보 : 사번,이름,직위,입사일,부서명,근무지,급여등급 ==> KING사원과 같은 부서의 사원 출력
SELECT empno,ename,job,hiredate,sal,(SELECT dname FROM dept d WHERE d.deptno=e.deptno) as dname,
                   (SELECT loc FROM dept d WHERE d.deptno=e.deptno) as loc,
                   (SELECT grade FROM salgrade WHERE sal BETWEEN losal AND hisal) as grade
FROM emp e
WHERE deptno=(SELECT deptno FROM emp WHERE ename='KING');
 
-- 다중행 서브쿼리 
SELECT deptno FROM dept;
/*
    데이터가 여러개인 경우 
      = 데이터 전체를 처리  ==> IN
      = 최대값  => ALL , MAX()
      = 최소값  => ANY , MIN() , SOME()
      
      => >ANY() => 수행된 결과중에 최소값  >ANY(SELECT deptno FROM dept)  ANY(10,20,30,40,50) => 10
                   (SELECT MIN(deptno)_FROM dept)
                   (SELECT MAX(deptno)_FROM dept)
         <ANY() => 수행된 결과중에 최대값  >ANY(SELECT deptno FROM dept)  ANY(10,20,30,40,50) => 50
         >ALL() => 수행된 결과중에 최대값  >ALL(SELECT deptno FROM dept)  ALL(10,20,30,40,50) => 50
         <ALL() => 수행된 결과중에 최소값  >ALL(SELECT deptno FROM dept)  ALL(10,20,30,40,50) => 10
*/
-- IN (10,20,30,40)
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno IN(SELECT DISTINCT deptno FROM emp);
-- deptno=10 OR detpno=20 OR deptno=30
SELECT DISTINCT deptno FROM emp;
 
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno>ANY(SELECT DISTINCT deptno FROM emp); --최소값 (10,20,30) => 10
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno<ANY(SELECT DISTINCT deptno FROM emp); --최대값  (10,20,30) => 30
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno<(SELECT NAX(deptno) FROM emp);
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno>(SELECT MIN(deptno) FROM emp);
 
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno<ALL(SELECT DISTINCT deptno FROM emp); --최소값  (10,20,30) => 30
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno>ALL(SELECT DISTINCT deptno FROM emp); --최대값  (10,20,30) => 30
 
-- 인라인뷰 , TOP-N(rownum)=> 중간에 데이터를 자를 수 없다 
-- 인기순위,베스트댓글 
SELECT ename,job,sal,rownum FROM emp;
SELECT ename,job,sal,rownum FROM emp
WHERE rownum<=5;
SELECT ename,job,sal,rownum FROM emp
WHERE rownum BETWEEN 5 AND 10;
-- 5개 => 급여가 많은 사람 
SELECT ename,job,sal,rownum FROM (SELECT ename,job,sal FROM emp ORDER BY sal DESC)
WHERE rownum<=5
ORDER BY sal DESC;
-- 인라인 뷰 => 페이징 기법
SELECT ename,job,sal,num 
FROM (SELECT ename,job,sal,rownum as num
FROM (SELECT ename,job,sal FROM emp ORDER BY sal DESC))
WHERE num BETWEEN 5 AND 10;