SUBQUERY 서브쿼리

SUBQUERY 서브쿼리

---

• SELECT문 하나 만으로 원하는 데이터를 조회할 수 없을 때 사용하는 방법
• 여러개의 SELECT 문장을 하나로 합쳐서 하나의 실행 가능한 SQL문장으로 만들어 원하는 데이터를 조회하는 것
• Sub-Query 또는 Outer-Query
  Main-Query 또는 Inner-Query 두 쌍이 같은 의미이다. 
• 서브쿼리로 찾아낸 DATA도 하나의 TABLE이다.
• 서브쿼리의 위치
  서브쿼리는 메인 쿼리의 다음 부분에 위치할 수 있다.
  (1) SELECT / DELETE / UPDATE문의 FROM절과 WHERE절
  (2) SELECT문의 HAVING절
  (3) INSERT문의 INTO절 
  (4) UPDATE문의 SET절 

//문법
SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 연산자 (SELECT 컬럼명
		       FROM 테이블명);

• 서브쿼리가 실행되어 반환된 행의 개수에 따라서 단일행 서브쿼리와 복수행 서브쿼리로 나뉜다.

종류	설명	사용가능 연산자
단일행 서브쿼리	서브쿼리 실행 결과가 한개의 행을 반환한다	비교연산자 =, >, >=, <, <=, !=
복수행 서브쿼리	서브쿼리 실행 결과가 한개 이상의 행을 반환한다	IN, ANY, ALL, EXIST 연산자

 

 

단일행 서브쿼리

---

• 서브쿼리의 실행결과가 반드시 한개의 행을 반환한다.
• 주로 PRIMARY KEY나 MAX,MIX,SUM과 같은 그룹함수를 사용하여 검색하는 경우 사용된다.
• 사용가능한 연산자 : 비교 연산자

//예시1
SELECT ename,sal
FROM emp
WHERE sal >= (SELECT AVG(sal)
		 FROM emp);
         
//예시2
// 'ALLEN' 과 같은 부서에서 근무하는 사원의 이름과 부서의 번호를 출력해 보세요.
SELECT ename, deptno
FROM emp
WHERE deptno=(SELECT deptno
			FROM emp
			WHERE ename='ALLEN');

 

연습문제

//문제1. 'SMITH' 가 근무하는 부서명을 서브쿼리를 이용해서 출력해 보세요.
//답1.
SELECT dname
FROM dept
WHERE deptno = (SELECT deptno FROM emp WHERE ename='SMITH');

DNAME
----------------------------
RESEARCH

//문제2. 'ALLEN' 과 같은 부서에서 근무하는 사원의 이름과 부서의 번호를 출력해 보세요.
//답2.
SELECT ename,deptno
FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename='ALLEN')

ENAME                    DEPTNO
-------------------- ----------
ALLEN                        30
WARD                         30
MARTIN                       30
BLAKE                        30
TURNER                       30
JAMES                        30

//문제3. 'ALLEN' 과 동일한 직책(job) 을 가진 사원의 사번과 이름, 직책을 출력해 보세요.
//답3.
SELECT empno, ename, job
FROM emp
WHERE job = (SELECT job FROM emp WHERE ename='ALLEN');

     EMPNO ENAME                JOB
---------- -------------------- ------------------
      7499 ALLEN                SALESMAN
      7521 WARD                 SALESMAN
      7654 MARTIN               SALESMAN
      7844 TURNER               SALESMAN
      
//문제4. 'ALLEN' 의 급여와 동일하거나 더 많이 받는 사원의 이름과 급여를 출력해 보세요.
//답4. 
SELECT ename, sal
FROM emp
WHERE sal >= (SELECT sal FROM emp WHERE ename='ALLEN');
ENAME                       SAL
-------------------- ----------
ALLEN                      1600
JONES                      2975
BLAKE                      2850
CLARK                      2450
KING                       5000
FORD                       3000

//문제5. 'DALLAS' 에서 근무하는 사원의 이름, 부서번호를 출력해보세요.
//답5
SELECT ename, deptno
FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE loc='DALLAS');

ENAME                    DEPTNO
-------------------- ----------
SMITH                        20
JONES                        20
FORD                         20


//문제6.'SALES' 부서에서 근무하는 모든 사원의 이름과 급여를 출력해보세요.
//답6
SELECT ename, sal
FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE dname='SALES');

ENAME                       SAL
-------------------- ----------
ALLEN                      1600
WARD                       1250
MARTIN                     1250
BLAKE                      2850
TURNER                     1500
JAMES                       950

//문제7. 자신의 직속 상관이 'KING' 인 사원의 이름과 급여를 출력해 보세요. 
//답7
SELECT ename,sal
FROM emp
WHERE mgr = (SELECT empno FROM emp WHERE ename='KING');

ENAME                       SAL
-------------------- ----------
JONES                      2975
BLAKE                      2850
CLARK                      2450

 

 

복수행 서브쿼리

---

• 서브쿼리의 실행결과가 하나 이상의 행을 반환할때 사용한다.
• 반드시 복수형 연산자와 함께 사용해야됨.

 

IN 연산자

• 하나의 값이 아닌 목록에 지정된 여러 개의 값을 한꺼번에 비교할 때 사용하는 연산자
• 내부적으로 OR연산자로 실행된다.

WHERE 컬럼명 IN(값1,값2,...);

//OR 연산자 사용
SELECT deptno, ename, empno FROM emp WHERE deptno=10 OR deptno=20;

//IN 연산자 사용
SELECT deptno, ename, empno FROM emp WHERE deptno IN(10,20);

 

ANY 연산자

• 조건을 비교할때 어느 하나라도 맞으면 true

WHERE 컬럼명 ANY(조건);

//OR 연산자를 이용한 데이터 검색
SELECT EMPNO, SAL FROM EMP WHERE SAL>1000 OR SAL>2000 OR SAL>3000;

//ANY 연산자를 이용한 데이터 검색
SELECT EMPNO, SAL FROM EMP WHERE SAL>ANY(1000,2000,3000);

 

ALL 연산자

• 조건을 비교할때 조건이 모두 맞으면 true

WHERE 컬럼명 비교연산자 ALL(조건);

//AND 연산자로 작성
SELECT empno,sal FROM emp WHERE sal>1000 AND sal>2000 AND sal>3000;

//ALL 연산자로 작성
SELECT empno,sal FROM emp WHERE sal>ALL(1000,2000,3000);

 

EXISTS

• 검색한 데이터가 존재하면 TRUE값이 전달되고 데이터가 출력된다.
• 반환값이 없으면 메인 쿼리를 실행하지 않는다.

// 사원이름이 'FORD' 인 사원이 존재하면 사원의 이름과  커미션을 출력하기
SELECT ename,comm
FROM emp
WHERE EXISTS(SELECT ename 
		FROM emp 
             WHERE ename='FORD');

 

연습문제

//문제1. 급여를 3000 이상받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원들의 이름과 급여, 부서번호를 출력해 보세요.
//답1.
SELECT ename,sal,deptno
FROM emp
WHERE deptno IN(SELECT deptno FROM emp WHERE sal>=3000);

ENAME                       SAL     DEPTNO
-------------------- ---------- ----------
MILLER                     1300         10
KING                       5000         10
CLARK                      2450         10
FORD                       3000         20
JONES                      2975         20
SMITH                       800         20

//문제2. IN 연산자를 이용하여 부서별로 가장 급여를 많이 받는 사원의 사원번호, 급  여, 부서번호를 출력해보세요. 
//답2.
SELECT empno, sal, deptno
FROM emp
WHERE sal IN(SELECT MAX(sal) FROM emp GROUP BY deptno);

     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7698       2850         30
      7839       5000         10
      7902       3000         20
      
//문제3. 직책이 MANAGER 인 사원이 속한 부서의 부서번호와 부서명과 부서의 위치를 출력해보세요.
//답3. 
SELECT deptno, dname, loc
FROM dept
WHERE deptno IN(SELECT deptno FROM emp WHERE job='MANAGER');

DNAME                        LOC
---------------------------- --------------------------
ACCOUNTING                   NEW YORK
RESEARCH                     DALLAS
SALES                        CHICAGO

//문제4. 30번 부서의 사원중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 이름과 급여를 출력해보세요.
//답4.
//다중행 ver.
SELECT ename,sal
FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE deptno=30);

ENAME                       SAL
-------------------- ----------
JONES                      2975
KING                       5000
FORD                       3000

//단일행 ver.
SELECT ename,sal
FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp GROUP BY deptno HAVING deptno=30);


//문제5.  직책이 'SALESMAN' 보다 급여를 많이 받는 사원들의 이름과 급여를 출력하라. (ANY 연산자 이용)
//답5.
SELECT ename,sal
FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE job='SALESMAN');

//문제6. 부서번호가 30번인 사원들의 급여중 최저 급여보다 높은 급여를 받는 사원의 이름, 급여를 출력해보세요.
//답6.
//- 단일행 서브 쿼리 -
>SELECT ename,sal
FROM emp
WHERE sal > (SELECT MIN(sal) FROM emp GROUP BY deptno HAVING deptno=30);
//- 다중행 서브 쿼리 - 
(ANY 연산자 사용) 
>SELECT ename,sal
FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE deptno=30);ㅍ

//문제7.  직책이 'SALESMAN' 인 사원의 최소 급여보다 많이 받는 사원들의 이름과 급여, 직책을 출력하되 'SALESMAN' 은 출력하지 않습니다.(ANY 연산자를 사용하세요)
//답7.
SELECT ename, sal, job
FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE job='SALESMAN') 
AND job != 'SALESMAN' ;

//문제8. SMITH 와 동일한 직책을 가진 사원의 이름과 직책을 출력하세요
//답8
SELECT ename, job
FROM emp
WHERE job = (SELECT job FROM emp WHERE ename='SMITH');

//문제9. 직책이 'SALESMAN' 인 사원이 받는 급여들의 최대 급여보다 많이 받는 사원들 의 이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다. (ALL 연산자 이용)
//답9
SELECT ename,sal
FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE job='SALESMAN')
AND deptno != 20;

//문제10. 직책이 'SALESMAN' 인 사원이 받는 급여들의 최소 급여보다 많이 받는 사원 들의 이름과 급여를 출력하되 부서번호가 20번인 사원은 제외한다. (ANY 연산자 이용)
//답 10
SELECT ename,sal
FROM emp
WHERE sal > ANY (SELECT sal FROM emp WHERE job='SALESMAN')
AND deptno != 20;

 

다중 컬럼 서브쿼리

---