Oracle SQL(6)

YangJiWon·2021년 1월 13일

DB

목록 보기

6/12

서브쿼리

앞서 배운 조인은 하나 이상의 테이블에서 원하는 데이터를 조회할 때 사용하는 방법이다.
서브쿼리는 하나의 SELECT만으로 원하는 데이터를 조회할 수 없을 때 사용하는 방법으로 여러 개의 SELECT문장을 하나로 합쳐서 하나의 실행 가능한 SQL문장으로 원하는 데이터를 조회할 수 있다.

-기본 문법

SELECT 컬럼
FROM 테이블
WHERE 컬럼명 연산자 (
SELECT 컬럼
FROM 테이블
[WHERE 조건]);

SELECT sal
FROM emp
WHERE ename = 'SMITH'; -- 800
-- +
SELECT ename, sal
FROM emp
WHERE sal < 800;
-- 위 두 개의 쿼리를 합친 것이 아래의 하나의 쿼리문이다.
SELECT ename, sal
FROM emp
WHERE sal < (
SELECT sal
FROM emp
WHERE ename = 'SMITH');

바깥 쪽 쿼리를 Main query, 안쪽 쿼리를 Subquery라고 한다.
서브퀴리에는 ORDER BY 절을 사용불가하다.
서브쿼리는 SELECT, FROM, WHERE, HAVING, ORDER BY, UPDATE, INSERT INTO절에도 사용될 수 있다.

서브쿼리의 종류

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

단일행 서브쿼리

단일행 서브쿼리는 서브쿼리가 실행되어 반드시 한 개의 행을 반환하는 서브쿼리를 의미한다.
기본 키를 이용하거나 MAX, MIN, SUM과 같은 그룹함수를 사용하여 검색하는 경우로서 반드시 단일행 연산자를 사용하여 메인 쿼리와 연산되어야 된다.

SELECT ename
FROM emp
WHERE ename != 'SMITH'
AND deptno = (
SELECT deptno
FROM emp
WHERE ename = 'SMITH');

-- 사원들의 평균 월급보다 급여가 높은 사람의 이름과 월급 출력
SELECT ename, sal
FROM emp
WHERE sal > (
SELECT AVG(sal)
FROM emp);

-- 부서번호가 10번인 부서의 최대월급을 받는 사원이름과 월급 출력
SELECT ename, sal 
FROM emp
WHERE sal = (
SELECT MAX(sal)
FROM emp
WHERE deptno = 10);

-- 여러 개의 서브 쿼리 사용 가능
SELECT empno, ename, job, hiredate, sal
FROM emp
WHERE job = (SELECT job
FROM emp
WHERE empno = 7521)
AND
sal > (
SELECT sal
FROM emp
WHERE empno = 7934);


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

복수행 서브쿼리

복수행 서브쿼리는 서브쿼리가 실행되어 반환되는 결과가 하나 이상의 행일 때 사용하는 서브쿼리이다.

종류	설명
IN	메인 쿼리와 서브 쿼리가 IN 연산자로 비교한다. 서브쿼리 결과값이 복수개인 경우에 사용된다.
ANY	ANY 연산자는 복수행 서브쿼리에서 > 또는 < 같은 비교 연산자를 사용하고자 할 때 사용되며 검색 조건이 하나라도 일치하면 참이다.
ALL	ALL 연산자는 복수행 서브쿼리에서 > 또는 < 같은 비교 연산자를 사용하고 할 때 사용되며 검색 조건의 모든 값이 일치하면 참이다.
EXIST	서브 쿼리의 반환 값이 존재하면 메인 쿼리를 실행하고 반환 값이 없으면 메인 쿼리를 실행하지 않는다.

IN 연산자

IN 연산자는 서브쿼리 반환 값이 복수이고 메인 쿼리와 동등 연산자 방식으로 비교할 때 사용하는 연산자이다.

SELECT ename, sal
FROM emp
WHERE sal IN (
SELECT sal
FROM emp
WHERE ename IN ('SMITH', 'ALLEN'));

SELECT ename, sal
FROM emp
WHERE sal IN
(SELECT sal
FROM emp
WHERE ename IN ('WARD', 'SMITH'));

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

SELECT empno, ename, job, hiredate, sal, deptno
FROM emp
WHERE sal IN (
SELECT MIN(sal)
FROM emp
GROUP BY job);

ALL 연산자

복수행 서브쿼리에서 > 또는 < 같은 비교 연산자를 사용하고자 할 때 사용된다.
ALL연산자는 서브쿼리에서 반환되는 행들 전체에 대한 조건이 모두 만족해야 된다는 것을 의미한다.

종류	설명
> ALL(서브쿼리)	서브쿼리에서 반환된 모든 데이터보다 큰 데이터를 메인 쿼리에서 조회한다. 결국 서브퀴리에서 반환된 최대값보다 큰 데이터 를 조회할 때 사용하는 서브쿼리이다.
< ALL(서브쿼리)	서브쿼리에서 모든 데이터보다 작은 데이터를 메인 쿼리에서 조회한다. 결국 서브쿼리에서 반환된 최소값보다 작은 데이터를 조회할 때 사용하는 서브쿼리이다.

SELECT empno, ename, sal
FROM emp
WHERE sal < ALL (
SELECT sal
FROM emp
WHERE job = 'MANAGER');

ANY 연산자

반환되는 행들 전체에 대해 조건이 하나 이상만 만족하면 된다는 것을 의미한다.

종류	설명
> ANY(서브쿼리)	서브쿼리에서 반환된 하나 이상만 조건이 일치하면 되는 큰 데이터를 메인 쿼리에서 조회한다. 결국 서브퀴리에서 반환된 최소값보다 큰 데이터 를 조회할 때 사용하는 서브쿼리이다.
< ANY(서브쿼리)	서브쿼리에서 반환된 하나 이상만 조건이 일치하면 되는 작은 데이터를 메인 쿼리에서 조회한다. 결국 서브쿼리에서 반환된 최대값보다 작은 데이터를 조회할 때 사용하는 서브쿼리이다.

SELECT empno, ename, sal
FROM emp
WHERE sal < ANY (
SELECT sal
FROM emp
WHERE job = 'MANAGER');

EXISTS 연산자

서브쿼리에서 실행된 결과가 하나라도 존재하는지 여부를 확인할 때 사용하는 연산자이다.
서브쿼리의 결과가 하나도 없으면 FALSE를 반환한다.
하나라도 있으면 TRUE를 반환한다.

SELECT *
FROM emp
WHERE EXISTS ( SELECT empno
FROM emp
WHERE comm IS NOT NULL);

다중 컬럼 서브쿼리

서브쿼리에서 여러 개의 컬럼 값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리이다.
컬럼을 쌍으로 묶어서 동시에 비교하는 pairwise 방식이 있고 컬럼별로 나누어 비교하고 나중에 AND 연산으로 처리하는 unpairwise방식이 있다.

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

인라인 뷰

FROM 절에도 서브쿼리를 사용할 수 있으며 이렇게 FROM 절에서 사용된 서브쿼리를 인라인 뷰라고 부른다.
서브쿼리가 하나의 가상테이블을 반환하는 형태로 사용되는 경우를 의미한다.
FROM 절에서 사용된 서브쿼리가 뷰와 비슷하게 동작하기 때문이 붙여진 이름이다.

SELECT 컬럼
FROM (서브쿼리) alias
WHERE 조건식;

SELECT e.deptno, total_sum, total_avg, cnt
FROM (SELECT deptno, sum(sal) total_sum, avg(sal) total_avg, count(*) cnt
FROM emp
GROUP BY deptno) e, dept d
WHERE e.deptno = d.deptno;