서브쿼리

• 앞서 배운 조인은 하나 이상의 테이블에서 원하는 데이터를 조회할 때 사용하는 방법이다.
• 서브쿼리는 하나의 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;