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;
다중 컬럼 서브쿼리
'ORACLE' 카테고리의 다른 글
오라클이 꺼져 있을때 (0) | 2019.11.25 |
---|---|
조인 (0) | 2019.11.16 |
View(뷰) / Sequence(시퀀스) / SYNONYM(동의어) (0) | 2019.11.15 |
SQL 함수-그룹함수 / GROUP BY 절 / HAVING절 (0) | 2019.11.15 |
SQL함수 / 단일행 함수 (0) | 2019.11.15 |