[Oracle Database] 7. 서브 쿼리에 대해 알아보자.
서브 쿼리
서브 쿼리란?
필요한 데이터를 추가로 조회하기 위해 SQL내부에서 사용하는 SELECT문
Q) EMP 테이블에서 월급을 가장 많이 받는 사원의 이름, 업무, 입사일, 급여 정보를 출력하는 쿼리를 작성하세요.
이렇게 쿼리 한 줄로만 결과물이 안나올 땐 서브쿼리를 생각해보자.
select ename, job, hiredate, sal
from emp
where sal = (select max(sal) from emp);
단일행 서브쿼리
- 실행 결과가 단 하나의 행으로 나오는 서브쿼리
- 데이터가 여러 개 존재하는 열의 경우에는 단일행 연산자를 사용 X
단일행 연산자 사용
다중행 서브쿼리
실행 결과가 여러 개의 행으로 나오는 서브쿼리
다중행 연산자 사용
| 다중행 연산자 | 설명 |
|---|---|
| IN | 메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치한 데이터가 있다면 TRUE |
| ANY, SOME | 메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 TRUE |
| ALL | 메인쿼리의 조건식을 서브쿼리의 결과 모두가 만족하면 TRUE |
| EXISTS | 서브쿼리의 결과가 존재하면(즉, 행이 1개 이상일 경우) TRUE |
IN 연산자
Ex) 각 부서별 최고 급여를 받는 사원을 조회하는 경우, 부서별 최고 급여 데이터를 먼저 구하고, 이 데이터와 일치하는 메인쿼리 데이터를 IN 연산자를 통해 선별할 수 있다.
SELECT *
FROM EMP
WHERE SAL IN (
SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO
);
ANY, SOME 연산자
서브쿼리가 반환한 여러 결과 값 중 메인쿼리와 조건식을 사용한 결과가 하나 이상이 TRUE라면 메인쿼리 조건식을 TRUE로 반환
Ex) EMP 테이블에서 10번 부서에 속한 어떤 사원 보다도 더 많은 급여를 받는 사원의 이름, 업무, 입사일, 급여, 부서번호 정보를 출력 (단, 10번 부서에 속한 사원은 제외)
SQL> select ename, job, hiredate, sal, deptno
2 from emp
3 where sal > any (select sal from emp where deptno = 10) and deptno
!= 10;
ENAME JOB HIREDATE SAL DEPTNO
-------------------- ------------------ -------- ---------- ----------
SCOTT ANALYST 82/12/09 3000 20
FORD ANALYST 81/12/03 3000 20
JONES MANAGER 81/04/02 2975 20
BLAKE MANAGER 81/05/01 2850 30
ALLEN SALESMAN 81/02/20 1600 30
TURNER SALESMAN 81/09/08 1500 30
6 rows selected.
ALL 연산자
서브쿼리의 모든 결과가 조건식에 맞아 떨어져야만 메인쿼리의 조건식이 TRUE
Ex) EMP 테이블에서 10번 부서에 속한 어떤 사원 보다도 더 많은 급여를 받는 사원의 이름, 업무, 입사일, 급여, 부서번호 정보를 출력
SQL> select ename, job, hiredate, sal, deptno
2 from emp
3 where sal > all (select sal from emp where deptno = 10)
상관 서브쿼리
일반 서브쿼리는 독립적으로 실행되지만, 상관 서브쿼리는 독립적으로 실행되지 않는다.
외부 쿼리가 동작한 다음에 쿼리가 실행된다.
EXISTS 연산자
서브쿼리에 결과 값이 하나 이상 존재하면 조건식이 모두 TRUE, 존재하지 않으면 모두 FALSE가 되는 연산자이다.
- 서브 쿼리 영역을 제외하고 실행
- 서브 쿼리로부터 반환된 값이 존재하면 EXISTS 실행
- 행의 수 만큼 반복
SQL> select ename, job, sal, hiredate, deptno
2 from emp e
3 where exists (select * from emp where mgr = e.empno);
ENAME JOB SAL HIREDATE DEPTNO
-------------------- ------------------ ---------- -------- ----------
FORD ANALYST 3000 81/12/03 20
BLAKE MANAGER 2850 81/05/01 30
KING PRESIDENT 5000 81/11/17 10
JONES MANAGER 2975 81/04/02 20
SCOTT ANALYST 3000 82/12/09 20
CLARK MANAGER 2450 81/06/09 10
6 rows selected.
다중열 서브쿼리
EMP 테이블에서 scott 사원과 동일한 업무와 급여를 받는 사원의 이름, 업무, 급여. 입사일 정보를 출력하는 쿼리
SQL> select ename, job, sal, hiredate
2 from emp
3 where (job, sal) IN (select job, sal from emp where ename ='SCOTT')
and ename != 'SCOTT';
ENAME JOB SAL HIREDATE
-------------------- ------------------ ---------- --------
FORD ANALYST 3000 81/12/03
[출처] 4. 서브쿼리|작성자 inmeta
FROM절에 사용하는 서브쿼리와 WITH절
FROM절에 사용하는 서브쿼리를 인라인 뷰(INLINE VIEW)라고 부른다.
인라인 뷰는SELECT문을 통해 일부 데이터를 먼저 추출해 온 후 별칭을 주어 테이블처럼 사용
select *
from (select * from emp where deptno = 10) E10,
(select * from dept) D
where E10.deptno = D.deptno;
WITH 절 사용
WITH
E10 as (select * from emp where deptno = 10),
D as (select * from dept)
select *
from E10, D
where E10.deptno = D.deptno;
SELECT절에 사용하는 서브쿼리
SELECT절에서 사용하는 서브쿼리는 단일행, 단일열 서브쿼리(scalar,스칼라) 여야한다.
Ex) 다음과 같은 결과가 나오도록 쿼리를 적어보자
SQL> select (select count(*) from emp) as total,
2 (select count(*) from emp where to_char(hiredate, 'YYYY') = '1980') as "1980",
3 (select count(*) from emp where to_char(hiredate, 'YYYY') = '1981') as "1981",
4 (select count(*) from emp where to_char(hiredate, 'YYYY') = '1982') as "1982",
5 (select count(*) from emp where to_char(hiredate, 'YYYY') = '1983') as "1983"
6 from dual;
TOTAL 1980 1981 1982 1983
---------- ---------- ---------- ---------- ----------
14 1 10 2 1