9일차(2)/DB_Oracle(6) : 다중행 서브쿼리

9일차(2)/DB_Oracle(5) : 다중행 서브쿼리

 

 

2) 다중행 서브쿼리

- 서브쿼리의 결과값으로 여러개의 행이 출력되는 서브쿼리

- 다중행 서브쿼리의 결과값을 조건절에서 사용할때는 반드시 다중행 연산자와 함께 사용해야 한다.(IN, ALL, ANY, EXIST)

- 다중행 서브쿼리의 경우 리턴되는 값의 수가 여러개 나오므로 = 으로는 비교할 수 없다.

 

---

 

예제1) 급여를 3000 이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원들의 이름과 급여, 부서번호를 출력

SELECT ename, sal, deptno
FROM emp
WHERE deptno IN(SELECT deptno FROM emp WHERE sal>=3000);

- 먼저 급여를 3000이상 받는 사원이 소속된 부서의 부서번호를 도출하고,

 그 SELECT문을 IN() 안에 넣어 서브쿼리로 활용하기

 

- 서브쿼리를 통한 결과값이 10,20이므로 WHERE deptno IN(10,20) 과 같은 의미이다.

 

---

예제2) IN 연산자를 이용하여 부서별로 가장 급여를 많이 받는 사원의 사원번호, 급여, 부서번호를 출력

SELECT empno, sal, deptno
FROM emp
WHERE sal IN(SELECT MAX(sal) FROM emp GROUP BY deptno);

- MAX(sal)을 얻어내고, 부서별로 그룹화한 뒤 emp의 sal 값과 연산하여 도출한다.

 

예제3) 직책이 MANAGER인 사원이 속한 부서의 부서번호, 부서명, 부서 위치를 출력

SELECT deptno, dname, loc
FROM dept
WHERE deptno IN(SELECT deptno FROM emp WHERE job='MANAGER');

- 값이 여러개 나오므로 IN() 사용. 이 안에 있는 값!


예제4) 30번 부서의 사원 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 이름과 급여를 출력

SELECT ename, sal
FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp GROUP BY deptno HAVING deptno=30);

- 단일행1: 그룹으로 묶은 다음에 그룹에서 having절에 해당하는 그룹을 추려낸 것!

 

SELECT ename, sal
FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno=30);

- 단일행2: max(sal)을 얻어내고, 이것보다 큰지 확인하는 것.

SELECT ename, sal
FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE deptno=30);

- 다중행: 부서번호가30번인 사원의 sal을 다중행으로만들기. ALL연산자 사용
- ANY : 어느 하나의 값보다 큰 것. 최소값보다 큰 것도 가능.
  ALL : 모든 값이 비교하는 sal보다 커야 함.


예제5) 직책이 'SALESMAN' 보다 급여를 많이 받는 사원들의 이름과 급여를 출력 (ANY 연산자 이용)

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

- 그냥 최소값보다라도 많기만 하면 된다면 ANY 사용!

 

예제6) 부서번호가 30번인 사원들의 급여중 최저 급여보다 높은 급여를 받는 사원의 이름, 급여를 출력

SELECT ename, sal
FROM emp
WHERE sal > (SELECT MIN(sal) FROM emp GROUP BY deptno HAVING deptno=30)

- 단일행 서브쿼리 : 값이 하나 나와서 단일행으로도 가능

SELECT ename, sal
FROM emp
WHERE sal > ANY (SELECT sal FROM emp WHERE deptno=30);

- 다중행 서브쿼리 : 어느 하나(최저급여)보다 높기만 하면 된다! → ANY 사용


예제7) 직책이 'SALESMAN' 인 사원의 최소 급여보다 많이 받는 사원들의 이름과 급여, 직책을 출력하되 'SALESMAN' 은 출력하지 않는다. (ANY 연산자 사용)

SELECT ename, sal, job
FROM emp
WHERE sal>ANY (SELECT sal FROM emp WHERE job='SALESMAN')
AND job!='SALESMAN';

- 조건이 하나 더 붙어서 AND로 추가한것!


예제8) SMITH 와 동일한 직책을 가진 사원의 이름과 직책을 출력

SELECT ename, job
FROM emp
WHERE job=(select job FROM emp WHERE ename='SMITH');

- 단일행 서브쿼리

 

- ALL과 ANY일떄의 값 차이 (예제 9,10)

예제9) 직책이 'SALESMAN' 인 사원이 받는 급여들의 최대 급여보다 많이 받는 사원들의 이름과 급여를 출력하되, 부서번호가 20번인 사원은 제외 (ALL 연산자 이용)

SELECT ename, sal
FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE job='SALESMAN')
AND deptno!=20;

- 추가조건은 AND로!

예제10) 직책이 'SALESMAN' 인 사원이 받는 급여들의 최소 급여보다 많이 받는 사원 들의 이름과 급여를 출력하되, 부서번호가 20번인 사원은 제외 (ANY 연산자 이용)

SELECT ename, sal
FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE job='SALESMAN')
AND deptno!=20;