국비 6일차 서브쿼리(단일행, 복수행, ALL, ANY, EXISTS, 다중칼럼, PAIRWISE, UNPAIRWISE, 인라인 뷰)

2021.01.12 국비교육 6일차

[TOC]

서브쿼리

• 중첩 SELECT
• 바깥쪽 쿼리 = 메인쿼리 (MAIN QUERY)
• 안쪽 쿼리 = 서브쿼리(SUB QUERY)
• OPERATER는 단일 행 연산자와 복수 행 연사자를 사용 할 수 있다.
• 서브 쿼리에는 ORDER BY 절 불가
• 상관 서브쿼리 : select 와 from사이에 있는서브쿼리

SELECT SAL FROM EMP WHERE ENAME = 'SMITH';
+
SELECT ENAME, SAL FROM EMP WHERE SAL < 800;
=
SELECT ENAME, SAL FROM EMP WHERE SAL < (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');
--SMITH보다 월급이 낮은 사원

SELECT ENAME FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH');
--SMITH의 부서번호와 같은 사람들

SELECT ENAME, HIREDATE FROM EMP 
WHERE HIREDATE > (SELECT HIREDATE FROM EMP WHERE ENAME = 'SMITH');
--SMITH보다 늦게 입사한 사람들의 이름과 입사날짜

SELECT ENAME, JOB FROM EMP 
WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'SMITH');
--SMITH랑 같은 직업

SELECT ENAME, SAL FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP);
--사원들의 평균월급보다 급여가 높은 사람의 이름과 월급

단일행 서브쿼리

• 서브쿼리가 한개의 행을 리턴
• 반드시 단일행 연산자 사용(=, >, <, <= ,>=, !=)

SELECT ENAME, SAL FROM EMP WHERE SAL < (SELECT SAL FROM EMP WHERE ENAME = 'SMITH');
--SMITH보다 월급이 낮은 사원

SELECT ENAME FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP WHERE DEPTNO =10);
--부서 번호가 10번인 부서의 최대월급을 받는 사원정보

SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO
HAVING MAX(SAL) > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 10);
--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);
--EMP 테이블에서 사원번호가 7521의 업무와 같고 급여가 사원번호 7934보다 많은 사원의 사원번호, 이름 담당업무, 입사일자, 급여

SELECT DEPTNO, MIN(SAL) FROM EMP 
GROUP BY DEPTNO
HAVING MIN(SAL) > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20);
--EMP 테이블에서20번 부서의 최소 급여보다 많은 모든 부서 출력

SELECT ENAME, DEPTNO, SAL FROM EMP
WHERE DPETNO IN (SELECT SAL FROM EMP WHERE SAL >=1000);
--월급을 1000이상 받는 사람이 속해 있는 부서와 동일한 부서에 근무하는 사람의 이름, 부서, 월급

복수행 서브쿼리

• 서브쿼리가 여러개의 행을 리턴
• 반드시 복수행 연산자 사용

SELECT EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO FROM EMP
WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY JOB);
--업무별로 최소 급여를 받는 사원의 사원번호, 이름, 업무, 입사일자, 급여, 부서번호 출력

ALL 연산자

• 복수행 서브쿼리 결과가 메인 쿼리의 WHERE절에서 부등호 조건으로 비교될 때 사용
• 서브쿼리에서 반환되는 행들 전체에 대해 조건을 만족
• ALL에 속하는 모든 데이터 중 조건에 맞는 데이터와 비교하게됨

SELECT SAL FROM EMP WHERE JOB = 'MANAGER'; 
+
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL < 2450;
=
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL < ALL(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');

• ALL보다 작다 -> 최솟값보다 작다
• ALL 보다 크다 -> 최댓값보다 크다

ANY 연산자

• 서브쿼리에서 반환되는 행들 중에서 어느 하나만 만족해도 된다.
• ANY 보다 작다 -> 최댓값보다 작다
• ANY보다 크다 -> 최솟값보다 크다

SELECT SAL FROM EMP WHERE JOB = 'MANAGER'; 
+
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL < 2975;
=
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL < ANY(SELECT SAL FROM EMP WHERE JOB = 'MANAGER');

EXISTS 연산자

• 서브쿼리에서 검색된 결과가 하나라도 존재하는지 여부를 확인하는 조건

SELECT * FROM EMP WHERE EXISTS(SELECT EMPNO FROM EMP WHERE COMM IS NOT NULL);
--만일 사원중에서 COMM을 받는 사원이 한명이라도 있으면 모든 사원 출력

다중 칼럼 서브쿼리

• 서브쿼리에서 여러개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리

PAIRWISE

• 컬럼을 쌍으로 묶어서 동시에 비교하는 방식

SELECT ENAME FROM EMP 
WHERE(DEPTNO, SAL) IN(SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);
--부서별로 가장 많은 SAL을 받는 사원 정보 출력

UNPAIRWISE

• 컬럼별로 나누어 비교하고 나중에 AND연산으로 처리하는 방식

SELECT ENAME FROM EMP
WHERE DEPTNO IN(SELECT DEPTNO FROM EMP GROUP BY DEPTNO) AND
SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

인라인 뷰

• FROM 절 뒤에 서브쿼리가 하나의 가상 테이블을 반환하는 형태로 사용 되는 경우

--EMP와 DEPT 테이블에서 부서별 SAL 총합과 평균을 출력 // 굳이 조인 할 필요는 없음
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;

SELECT DEPTNO, TOTAL_SUM, TOTAL_AVG, CNT 
FROM (SELECT DEPTNO, SUM(SAL) TOTAL_SUM, AVG(SAL) TOTAL_AVG, COUNT(*) CNT
      FROM EMP GROUP BY DEPTNO);

SELECT DEPTNO, SUM(SAL), AVG(SAL), COUNT(*)
FROM EMP GROUP BY DEPTNO;