서브쿼리

서브(SUB) 퀴리:메인 쿼리 내부에 존재하며,서브쿼리->메인쿼리 순으로 실행

 

SELECT DNAME FROM DEPT
WHERE DEPTNO=(SELECT DEPTNO
                        FROM EMP
                        WHERE ENAME='FORD');  ->이름이 'FORD'인 사원을 검색하는 서브쿼리
              
--JONES보다 급여를 많이 받는 사원의 이름,급여 검색
SELECT ENAME,SAL FROM EMP
WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='JONES');

서브 쿼리 위치
1.WHERE 절 : 그냥 서브쿼리 또는 상관관계 서브쿼리
2.FROM 절 : 인라인 뷰(가상의 테이블)
3.SELECT 절: 상관관계 서브쿼리

 

--10번 부서의 근무하는 사원이름, 부서이름 검색 

--서브쿼리  사용 
SELECT E.ENAME, D.DNAME 
FROM EMP E JOIN (SELECT DEPTNO, DNAME 
                          FROM DEPT
                          WHERE DEPTNO=10) D
ON E.DEPTNO=D.DEPTNO;

--서브쿼리  미사용 
SELECT E.ENAME, D.DNAME
FROM EMP E
JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
WHERE D.DEPTNO=10;

서브쿼리에서 그룹함수
--평균 급여보다 많이 받는 사원의 이름과 급여 검색
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP)
ORDER BY SAL DESC;

--직급별로 직급, 최대급여를 출력하되,
--최대급여가 SALESMAN의 최대급여보다 큰 그룹만 출력
SELECT JOB, MAX(SAL)
FROM EMP
GROUP BY JOB
HAVING MAX(SAL)>(SELECT MAX(SAL) FROM EMP WHERE JOB='SALESMAN')
ORDER BY MAX(SAL);

단행 서브쿼리: 결과가 한행
다중행 서브쿼리: 결과가 여러행
SELECT ENAME,SAL,DEPTNO
FROM EMP
WHERE DEPTNO =ANY (SELECT DEPTNO -- =ANY 어느 하나라도 맞으면 참, 하지만 IN을 자주 쓴다
              FROM EMP
              WHERE SAL>=3000);
              
--ANY: 서브쿼리 결과와 하나 이상 만족하면 참이다.(OR) 
--ALL: 서브쿼리 결과와 모두 만족하면 참이다.(AND)
              
SELECT ENAME,SAL
FROM EMP
WHERE SAL>ALL(SELECT SAL --IN은 OR, ALL은 AND
              FROM EMP
              WHERE DEPTNO=30);

--EXISTS:서브쿼리의 결과가 하나라도 존재하면 참
SELECT ENAME,SAL
FROM EMP
WHERE EXISTS(SELECT * FROM EMP
             WHERE SAL>4000);

 

-급여를 많이 받는 사원 순으로 앞에 순위를 붙여 출력하시오(단 ROWNUM함수 사용 할 것)

--그냥 ROWNUM을 쓰면 SELECT문이 먼저 실행되어 행 번호가 붙은 다음 ORDER BY문이 실행되서 뒤죽박죽이 되어 버린다. 그래서 서브쿼리를 사용해 정렬을 한 다음 행번호를 붙인다

 

SELECT ROWNUM,EMPNO,ENAME,SAL
FROM (SELECT EMPNO,ENAME,SAL
      FROM EMP
      ORDER BY SAL DESC);

->서브쿼리 실행하여 정렬한 다음 ROWNUM을 붙인다.
      
--WHERE 절은 자기 자신의 AS를 못 쓴다. ROWNUM AS RANK -> WHERE RANK>6 사용 불가

 

SELECT RANK, EMPNO, ENAME, SAL
FROM (SELECT ROWNUM AS RANK,EMPNO,ENAME,SAL
      FROM (SELECT EMPNO,ENAME,SAL
            FROM EMP
            ORDER BY SAL DESC)
)
WHERE RANK BETWEEN 6 AND 10; 

--RANK가 AS가 아니라서 WHERE 절에서 사용 가능