서브쿼리 (subQuery) [스칼라,인라인 뷰,중첩] // all any some 연산자

서브 쿼리는 메인 쿼리 내부에 작성하는 쿼리를 뜻하며, 주로 아래의 3가지 서브 쿼리로 분류한다.

 

스칼라 서브 쿼리

 

SELECT 절에 위치하며 칼럼의 역할을 하므로 해당 서브쿼리의 결과는 반드시 단일 행 or 단일 값으로 리턴되어야 한다.
*다중 행 값이 조회되면 "ORA-01427: single-row subquery returns more than one row" 라는 오류가 발생하므로 주의

만약을 대비해서 서브 쿼리 마지막에 AND ROWNUM = 1을 붙이기도 함 [한개만 반환되게]

예시 테이블

select e.EMPNO, e.ENAME, e.DEPTNO, (select d.DNAME from dept d where d.DEPTNO = e.DEPTNO) as dname
from emp e
where e.sal >= 3000;

 

 

 

인라인 뷰 서브쿼리

from 절 혹은 join절에 쓰인다.

 

인라인 뷰는 뷰(View)와 동일하며 다른 점은 뷰(View)는 오브젝트이기 때문에 재활용이 가능하지만 인라인 뷰는 해당 쿼리문에서만 사용하는 임시 뷰이다. 인라인 뷰는 WITH 절로 생성된 뷰와도 비슷하다.

인라인 뷰는 테이블(또는 뷰)처럼 메인 쿼리의 테이블과 조인을 하여 사용할 수 있다. 인라인 뷰는 테이블과 같이 인덱스가 없기 때문에 데이터가 많으면 쿼리문이 느려질 수 있어서 사용 시 주의해야 한다.

-- 자기 부서의 평균 월급보다 더 많은 월급을 받는 사원의 사번 ,이름 ,부서번호, 부서별 평균월급을 출력
select e.EMPNO, e.ENAME, d.DEPTNO, trunc(d.avg,0)
from emp e
         join (select DEPTNO, avg(sal) as avg
               from emp
               group by DEPTNO) d on e.DEPTNO = d.DEPTNO
where e.sal > d.avg;

 

 

 

 

중첩 서브 쿼리

WHERE 절에서 사용하는 서브쿼리로 메인쿼리 테이블의 특정 컬럼 값과 비교한 값을 반환하는 용도로 사용된다.
단일행 / 다중행 둘 다 리턴이 가능하다.

 

예시 테이블

단일행 서브쿼리 이용 예시)

1. single row subquery : 서브쿼리의 실행결과가 단일컬럼에 단일로우값인 경우를 말한다. (한개의 값)
해당 서브쿼리에 사용되는 연산자 : = , != , > , <

넣을 서브쿼리 결과

select *
from emp
where sal > (select sal from emp where ENAME='JONES');

 

결과

 

 

다중행 서브쿼리 예시)

 

다중행 서브쿼리( Multi row subquery )는  서브쿼리의 실행결과가 단일컬럼에 여러개의 로우인 경우 (다중값)를 말한다.해당 서브쿼리에 사용되는 연산자 : in , not in , any , all

 

---

ALL , ANY

 

오라클의 ANY, SOME, ALL 연산자는 실무에서 자주 사용은 안하지만, 사용법이 궁금해서 찾아보면 사용법이 생각보다 어렵고 어떤 상황의 쿼리문에서 사용해야 할지 머릿속에 그려지지 않는다.

 
SOME은 ANY와 이름만 다를뿐 동일한 기능의 연산자이며 아래의 예제는 ANY로만 작성하였다.

ANY(SOME), ALL은 주로 서브쿼리와 함께 사용하는 다중 행 연산자이며,

 ANY는 조건을 만족하는 값이 하나라도 있으면 결과를 리턴하고, 

ALL은 모든 값이 조건을 만족해야 결과를 리턴한다.

 

ANY : sal > 1000 or sal > 4000 or .... or로 연결

ALL : sal > 1000 and sal > 4000 and .....  and로 연결

 

--6. 30번 부서에 있는 사원들 중에서 가장 많은 월급을 받는 사원보다 많은 월급을 받는 사원들의 이름, 부서번호, 월급을 출력하라.
-- (단, ALL(and) 또는 ANY(or) 연산자를 사용할 것)
select ENAME,DEPTNO,sal from emp
    where sal > ALL(select sal from emp where DEPTNO = 30)
and DEPTNO != 30;

위의 예제는 ALL( 부서번호 30인 사원들의 월급들이  and로 연결되어진 모습일것이다.)

 

결국 sal > 100 and sal > 200 and sal > 300 and 처럼 부서번호30의 사람들 월급보다 다 커야 만족하게 될것이다.

( ALL 연산자를 썼으므로)

 

select ENAME,DEPTNO,sal from emp
    where sal > ANY(select sal from emp where DEPTNO = 30)
and DEPTNO != 30;

만약 ANY였다면 부서번호 30 사람들의 월급들이랑 비교 했을때 하나라도 자신이 더 컸다면 조건을 만족하게 될것이다.

sal > 100 or sal > 200 or  sal > 300 or ... 처럼 진행되니까

 

---

서브쿼리 문제

--1. 'SMITH'보다 월급을 많이 받는 사원들의 이름과 월급을 출력하라.
SELECT ENAME, SAL
FROM EMP
WHERE SAL>(SELECT SAL
           FROM EMP
           WHERE ENAME='SMITH');
 
--2. 10번 부서의 사원들과 같은 월급을 받는 사원들의 이름, 월급,
-- 부서번호를 출력하라.
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL IN(SELECT SAL
             FROM EMP
             WHERE DEPTNO=10);
 
--3. 'BLAKE'와 같은 부서에 있는 사원들의 이름과 고용일을 뽑는데
-- 'BLAKE'는 빼고 출력하라.
SELECT ENAME, HIREDATE
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO
              FROM EMP
              WHERE ENAME='BLAKE')
AND ENAME!='BLAKE';
 
--4. 평균급여보다 많은 급여를 받는 사원들의 사원번호, 이름, 월급을
-- 출력하되, 월급이 높은 사람 순으로 출력하라.
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL>(SELECT  AVG(SAL)  FROM EMP)
ORDER BY SAL DESC;
 
--5. 이름에 'T'를 포함하고 있는 사원들과 같은 부서에서 근무하고
-- 있는 사원의 사원번호와 이름을 출력하라.
SELECT EMPNO, ENAME
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
                FROM EMP
                WHERE ENAME LIKE '%T%');
--where deptno = 20 or deptno= 30


--6. 30번 부서에 있는 사원들 중에서 가장 많은 월급을 받는 사원보다
-- 많은 월급을 받는 사원들의 이름, 부서번호, 월급을 출력하라.
--(단, ALL(and) 또는 ANY(or) 연산자를 사용할 것)
SELECT ENAME, DEPTNO, SAL
FROM EMP
WHERE SAL > (SELECT MAX(SAL)
             FROM EMP
             WHERE DEPTNO=30);
 
SELECT ENAME, DEPTNO, SAL
FROM EMP
WHERE SAL > ALL(SELECT SAL
                FROM EMP
                WHERE DEPTNO=30)
 
--where sal > 1600 and sal > 1250 and sal > 2850 and sal > 1500 and sal > 950
 
 
--7. 'DALLAS'에서 근무하고 있는 사원과 같은 부서에서 일하는 사원의
-- 이름, 부서번호, 직업을 출력하라.
SELECT ENAME, DEPTNO, JOB
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO    -- = 이 맞는데  IN
                FROM DEPT
                WHERE LOC='DALLAS');
 
--8. SALES 부서에서 일하는 사원들의  같은 부서번호, 이름, 직업을 갖는 사원정보를 출력하라.
SELECT DEPTNO, ENAME, JOB
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
                FROM DEPT
                WHERE DNAME='SALES')
 

 
--9. 'KING'에게 보고하는 모든 사원의 이름과 급여를 출력하라
--king 이 사수인 사람 (mgr 데이터가 king 사번)
SELECT ENAME, SAL
FROM EMP
WHERE MGR=(SELECT EMPNO
           FROM EMP
           WHERE ENAME='KING');
 
--10. 자신의 급여가 평균 급여보다 많고, 이름에 'S'가 들어가는
-- 사원과 동일한 부서에서 근무하는 모든 사원의 사원번호, 이름,
-- 급여를 출력하라.
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL)
             FROM EMP)
AND DEPTNO IN(SELECT DEPTNO
              FROM EMP
              WHERE ENAME LIKE '%S%');
 
--select * from emp
--where  deptno in  (
--                      select deptno from emp where sal > (select avg(sal) from emp)
--                      and ename like '%S%'
--                   )
 
--11. 커미션을 받는 사원과 부서번호, 월급이 같은 사원의
-- 이름, 월급, 부서번호를 출력하라.
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO
                FROM EMP
                WHERE COMM IS NOT NULL)
AND SAL IN( SELECT SAL
            FROM EMP
            WHERE COMM IS NOT NULL);
 
--12. 30번 부서 사원들과 월급과 커미션이 같지 않은
-- 사원들의 이름, 월급, 커미션을 출력하라.
SELECT ENAME, SAL, COMM
FROM EMP
WHERE SAL NOT IN(SELECT SAL
                 FROM EMP
                 WHERE DEPTNO=30)
AND COMM NOT IN(SELECT NVL(COMM, 0)
                FROM EMP
                WHERE DEPTNO=30 and comm is not null);
                
                -- 마지막 문제에는 is not null 또는 NVL 둘중하나 골라쓰면 된다.